ACC: Cannot Apply Filter to Subform Using ApplyFilter Action

MORE INFORMATION

The ApplyFilter action dynamically restricts or sorts the records in a table or the records from a form or report's underlying table or query. Although you cannot use this action with a subform, you can change the subform's RecordSource property to achieve the same results.

The following example demonstrates how to change the sort order of a subform by changing the subform's RecordSource property. It uses the Categories form and Product List subform in the sample database Northwind.mdb (or the Categories form and Categories Subform subform in the sample database NWIND.MDB in 2.0).

CAUTION: Following the steps in this example will modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file or perform these steps on a copy of the Northwind database.

1. Open the sample database Northwind.mdb (NWIND.MDB in version 2.0) and create a new query based on the Products table. Include all the fields from the table in the query.
2. Set the UnitPrice field's Sort row to Ascending.
   
   NOTE: In Microsoft Access 2.0 the UnitPrice field is called Unit Price.
3. Save the query as UnitPriceSort.
4. Open the Categories form in Design view, and then add a command button to the form.
5. Set the command button's OnClick property to the following event procedure.
   
   In Microsoft Access 7.0 and 97:

   If Me![Product List].Form.RecordSource = "Products" Then
      Me![Product List].Form.RecordSource = "UnitPriceSort"
   Else
      Me![Product List].Form.RecordSource = "Products"
   End If
   						

   In Microsoft Access 2.0:

   If Me![Categories Subform].Form.RecordSource = "Products" Then
      Me![Categories Subform].Form.RecordSource = "UnitPriceSort"
   Else
      Me![Categories Subform].Form.RecordSource = "Products"
   End If
   					

6. View the Categories form in Form view.
7. Click the command button you added in step 4. Note that as you continue to click the command button, the form's sort order is toggled between the Unit Price and the Product ID fields.