14.2. Using SQL When Opening Forms and Reports

Whenever you use the Access Wizard to build a command button to open a form or report with a filter to limit the records that are displayed, you are actually using SQL in VBA. This wizard will build VBA code to open the form with a WhereCondition, like this:

Private Sub cmdCityBusinesses_Click()
On Error GoTo Err_cmdCityBusinesses_Click

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "frmBusiness"

    stLinkCriteria = "[City]=" & "'" & Me![txtCity] & "'"
    DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cmdCityBusinesses_Click:
    Exit Sub

Err_cmdCityBusinesses_Click:
    MsgBox Err.Description
    Resume Exit_cmdCityBusinesses_Click
End Sub

The WhereCondition on the OpenForm command is used to filter the form being opened to a set of records that meet some criteria. Usually, it is used to drill down to a specific single record, so the criteria is merely the primary key value of the record. However, as in this example, it can be used to open a form to a set of multiple records that meet the specified criteria (in this case, the City).

NOTE

When you use the WhereCondition, you don't need to include the word Where at the beginning of the string. It's assumed.

This is a very simple example of using a fragment of SQL in your code; after all, the wizard will build it for you. The wizard to open a report works much the same way. However, there are many other more powerful ways to use SQL in your VBA code.

Get Access 2003 VBA Programmer's Reference now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.