Microsoft KB Archive/112796

= ACC: Cannot Apply Filter to Subform Using ApplyFilter Action =

Article ID: 112796

Article Last Modified on 1/18/2007

-

APPLIES TO


 * Microsoft Access 2.0 Standard Edition
 * Microsoft Access 95 Standard Edition
 * Microsoft Access 97 Standard Edition

-



This article was previously published under Q112796



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.

 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. Set the UnitPrice field's Sort row to Ascending.

NOTE: In Microsoft Access 2.0 the UnitPrice field is called Unit Price. Save the query as UnitPriceSort. Open the Categories form in Design view, and then add a command button to the form.  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                    View the Categories form in Form view. 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.</li></ol>

<div class="references_section">