O'Reilly logo

Access™ 2007 VBA Programmer's Reference by Armen Stein, Geoffrey Griffith, Rob Cooper, Teresa Hennig

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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 ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required