Provided by Allen Browne.
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.
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:
|ControlSource||' Leave blank.|
|RowSource||tblProductCategory||'Your look up table.|
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
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.
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.
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.
|Home||Index of tips||Top|