ACC: Cannot Apply a Filter to a Subform

• Microsoft Access versions 2.0, 7.0, 97

SUMMARY

Moderate: Requires basic macro, coding, and interoperability skills.

You cannot use the ApplyFilter action to apply a filter to a subform. If you try to do so, the filter is applied to the main form instead. In Microsoft Access version 1.x, you can achieve some of this functionality by basing the subform on a parameter query. In the other versions of Microsoft Access, you can get the same results by changing the subform's RecordSource property.

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.

REFERENCES