Access™ 2007 VBA Programmer's Reference

15.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. The wizard builds 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 (it's the fourth parameter, using a variable named stLinkCriteria) is used to filter the form being opened to a set of records that meet some criteria. It's usually used to drill down to a specific single record, so the criterion is merely the primary key value of the record. As in this example, however, it can be used to open a form to a set of multiple records that meet the specified criterion (in this case, the City).

When you use the WhereCondition, you don't include the word Where at the beginning of the string. It's assumed, so you'll see an error if you specify it.

This is a 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 ...