ApplyFilter Macro Action

You can use the ApplyFilter action to apply a filter, a query, or an SQL WHERE clause to a table, form, or report to restrict or sort the records in the table, or the records from the underlying table or query of the form or report. For reports, you can use this action only in a macro specified by the report's OnOpen event property.

** Note **  You can use this action to apply an SQL WHERE clause only when applying a server filter. A server filter cannot be applied to a stored procedure's record source.

Setting

The ApplyFilter action has the following arguments.

** Note **  You can use the Filter Name argument if you have already defined a filter that provides the appropriate data. You can use the Where Condition argument to enter the restriction criteria directly. If you use both arguments, Microsoft Office Access 2007 applies the WHERE clause to the results of the filter. You must use one or both arguments.

Remarks

You can apply a filter or query to a form in Form view or Datasheet view.

The filter and WHERE condition you apply become the setting of the form's or report's Filter or ServerFilter property.

For tables and forms, this action is similar to clicking Apply Filter/Sort or Apply Server Filter on the Records menu. The menu command applies the most recently created filter to the table or form, whereas the ApplyFilter action applies a specified filter or query.

In an Access database, if you point to Filter on the Records menu and then click Advanced Filter/Sort after running the ApplyFilter action, the Advanced Filter/Sort window shows the filter criteria you have selected with this action.

To remove a filter and display all of the records for a table or form in an Office Access 2007 database, you can use the ShowAllRecords action or the Remove Filter/Sort command on the Records menu. To remove a filter in an Access project (.adp), you can return to the Server Filter By Form window and remove all filter criteria and then click Apply Server Filter on the Records menu on the toolbar, or set the ServerFilterByForm property to False (0).

When you save a table or form, Access saves any filter currently defined in that object, but won't apply the filter automatically the next time the object is opened (although it will automatically apply any sort you applied to the object before it was saved). If you want to apply a filter automatically when a form is first opened, specify a macro containing the ApplyFilter action or an event procedure containing the ApplyFilter method of the DoCmd object as the OnOpen event property setting of the form. You can also apply a filter by using the OpenForm or OpenReport action, or their corresponding methods. To apply a filter automatically when a table is first opened, you can open the table by using a macro containing the OpenTable action, followed immediately by the ApplyFilter action.

Example

Apply a filter by using a macro

The following macro contains a set of actions, each of which filters the records for a Customer Phone List form. It shows the use of the ApplyFilter, ShowAllRecords, and GoToControl actions. It also shows the use of conditions to determine which toggle button in an option group has been selected on the form. Each action row is associated with a toggle button that selects the set of records starting with A, B, C, and so on, or all records. This macro should be attached to the AfterUpdate event of the CompanyNameFilter option group.