Access 2003 VBA Programmer's Reference

14.3. Using SQL to Enhance Forms

Using SQL, you can enhance your forms in many ways, including allowing quick and easy record sorting, narrowing a list of records by applying selections, and using combo box values to limit the drop-down lists for other combo boxes. These are all powerful tools that help your user get more value from your application. Let's study these techniques one at a time.

14.3.1. Sorting on Columns

Users often expect the ability to sort on columns, similar to other Windows applications like Outlook. For example, if you have an index form of businesses, your user may want to sort on either the Business Name or Contact Name column, as shown in Figure 14-1.

Figure 14.1. Figure 14-1

In this example, the user can click either the Business Name column heading or the Contact Name column heading. The two toggle buttons are in an option group called optSort. This control has an After Update event that contains the following code:

Private Sub optSort_AfterUpdate() On Error GoTo Error_Handler Dim strOrderBy As Variant strOrderBy = Null Select Case Me!optSort Case 1 'Business Name strOrderBy = " tblBusiness.BusinessName, " & _ "tblBusiness.LastName, tblBusiness.FirstName" Case 2 'Contact information strOrderBy = "tblBusiness.LastName, " & _ "tblBusiness.FirstName, tblBusiness.BusinessName" Case Else End Select strOrderBy = " ORDER BY " + strOrderBy Me.RecordSource ...