Access™ 2007 VBA Programmer's Reference

15.3. Using SQL to Enhance Forms

Using SQL, you can enhance your forms in many ways. You can allow quick and easy record sorting, the capability to narrow a list of records by applying selections, and the use of 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.

15.3.1. Sorting on Columns

Users often expect the capability to sort on columns, similar to other Windows applications such as Outlook and Excel. 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 15-1.

Figure 15.1. Figure 15-1

The two toggle buttons (Business Name and Contact Name) are in an option group control called optSort, which 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" End Select strOrderBy = " ORDER BY " + strOrderBy Me.RecordSource = ReplaceOrderByClause(Me.RecordSource, strOrderBy) 'Me.Requery 'may be needed for earlier versions of Access ...