Microsoft Access Tips for Serious Users

Provided by Allen Browne, allenbrowne.com

Filter a Form on a Field in a Subform

The Filter property of forms (introduced in Access 95) makes it easy to filter a form based on a control in the form. However, the simple filter cannot be used if the field you wish to filter on is not in the form.

You can achieve the same result by changing the RecordSource of the main form to an SQL statement with an INNER JOIN to the table containing the field you wish to filter on. If that sounds a mouthful, it is quite simple to do.

Before trying to filter on a field in the subform, review how filters are normally used within a form.

Simple Filter Example

Take a Products form with a ProductCategory field. With an unbound combo in the form's header, you could provide a simple interface to filter products from one category. The combo would have these properties:

Now when the user selects any category in this combo, its AfterUpdate event procedure filters the form like this:

Private Sub cboShowCat_AfterUpdate()
    If IsNull(Me.cboShowCat) Then
        Me.FilterOn = False
    Else
        Me.Filter = "ProductCatID = """ & Me.cboShowCat & """"
        Me.FilterOn = True
    End If
End Sub

Filtering on a field in the Subform

You cannot use this simple approach if the field you wish to filter on is in the subform. For example, some products might have several suppliers. You need a subform for the various suppliers of the product in the main form. The database structure for this example involves three tables:

• tblProduct, with ProductID as primary key.

• tblSupplier, with SupplierID as primary key.
• tblProductSupplier, a link table with ProductID and SupplierID as foreign keys.

The main form draws its records from tblProduct, and the subform from tblProductSupplier.

When a supplier sends a price update list, how do you filter your main form to only products from this supplier to facilitate changing all those prices? Remember, SupplierID exists only in the subform.

One solution is to change the RecordSource of your main form, using an INNER JOIN to get the equivalent of a filter. It is straightforward to create, and the user interface can be identical to the example above.

Here are the 2 simple steps to filter the main form to a selected supplier:

1. Add a combo to the header of the main form with these properties:

2. Click the build button (...) beside the AfterUpdate property. Paste this code between the Sub and End Sub lines:

Dim strSQL As String
If IsNull(Me.cboShowSup) Then
    ' If the combo is Null, use the whole table as the RecordSource.
    Me.RecordSource = "tblProduct"
Else
    strSQL = "SELECT DISTINCTROW tblProduct.* FROM tblProduct " & _
        "INNER JOIN tblProductSupplier ON " & _
        "tblProduct.ProductID = tblProductSupplier.ProductID " & _
        "WHERE tblProductSupplier.SupplierID = " & Me.cboShowSup & ";"
    Me.RecordSource = strSQL
End If

That's it! Although the SELECT statement does not return any fields from tblProductSupplier, the INNER JOIN limits the recordset to products that have an entry for the particular supplier, effectively filtering the products.

Even if you know nothing of SQL, you can create these strings easily in Access. Use Query Design view to create the query you want, and then copy the statement from SQL View and paste into your code.

Combining Both Types

When you change the RecordSource, Access turns the form's FilterOn property off. This means that if you use both the Filter and the change of RecordSource together, your code must save the filter state before changing the RecordSource and restore it.

Assume you have provided both the combos described above (cboShowCat and cboShowSup) on your main form. A user can now filter only products of a certain category and from a particular supplier. The AfterUpdate event procedure for cboShowSup must save and restore the filter state. Here is the complete code, with error handling.

Private Sub cboShowSup_AfterUpdate()
On Error GoTo Err_cboShowSup_AfterUpdate
    ' Purpose: Change the form's RecordSource to only products from this supplier.
    Dim sSQL As String
    Dim bWasFilterOn As Boolean

    ' Save the FilterOn state. (It's lost during RecordSource change.)
    bWasFilterOn = Me.FilterOn

    ' Change the RecordSource.
    If IsNull(Me.cboShowSup) Then
        If Me.RecordSource <> "tblProduct" Then
            Me.RecordSource = "tblProduct"
        End If
    Else
        sSQL = "SELECT DISTINCTROW tblProduct.* FROM tblProduct " & _
            "INNER JOIN tblProductSupplier ON " & _
            "tblProduct.ProductID = tblProductSupplier.ProductID " & _
            "WHERE tblProductSupplier.SupplierID = """ & Me.cboShowSup & """;"
        Me.RecordSource = sSQL
    End If

    ' Apply the filter again, if it was on.
    If bWasFilterOn And Not Me.FilterOn Then
        Me.FilterOn = True
    End If

Exit_cboShowSup_AfterUpdate:
    Exit Sub

Err_cboShowSup_AfterUpdate:
    MsgBox Err.Number & ": " & Err.Description, vbInformation, & _
        Me.Module.Name & ".cboShowSup_AfterUpdate"
    Resume Exit_cboShowSup_AfterUpdate
End Sub

Note: For comparison, this example shows a text-based SupplierID, where the previous example assumed an AutoNumber type.

For an example of how to handle many optional criteria, see the Search Criteria database.