Filter a Form on a Field in a Subform

December 27, 2008 at 3:19 pm Leave a comment

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:

  

Name

cboShowCat 

  

 

ControlSource 

  

‘ Leave blank. 

 

RowSource 

tblProductCategory 

‘Your look up table. 

 

AfterUpdate 

[Event Procedure] 

 

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

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:

  

Name

cboShowSup 

  

 

ControlSource 

  

‘Leave blank. 

 

RowSource 

tblSupplier 

 
 

AfterUpdate 

[Event Procedure] 

 

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.

Entry filed under: Uncategorized. Tags: .

Access Carry Over Data Using the NotInList event (combo)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Trackback this post  |  Subscribe to the comments via RSS Feed


Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 14 other followers

Blog Stats

  • 94,725 hits

Twitter Updates

Iklan


%d bloggers like this: