Form, Query, Report, Table.
You can use the Filter property to specify a subset of records to be displayed when a filter is applied to a form, query, or table.
The Filter property is a string expression consisting of a WHERE clause without the WHERE keyword. For example, the following Visual Basic code defines applies a filter to show only customers from the USA:
Me.Filter = "Country = 'USA'".FilterOn = True
You can set this property in the following ways:
You can also set this property in Form view or Datasheet view by pointing to Filter on the Records menu and clicking one of the commands on the submenu.
Note Setting the Filter property has no affect on the data access Filter property.
You can use the Filter property to save a filter and apply it at a later time. Filters are saved with the objects in which they are created. They are automatically loaded when the object is opened, but they aren’t automatically applied.
When a new object is created, it inherits the RecordSource, Filter, OrderBy, and OrderByOn properties of the table or query it was created from. For forms and reports, inherited filters aren’t automatically applied when an object is opened.
To apply a saved filter to a form, query, or table, you can click the Apply Filter button on the toolbar, click Apply Filter/Sort on the Records menu, or use a macro or Visual Basic to set the FilterOn property to True (-1). For reports, you can apply a filter by setting the FilterOn property to Yes in the report’s property sheet.
The Apply Filter button indicates the state of the Filter and FilterOn properties. The button remains disabled until there is a filter to apply. If an existing filter is currently applied, the Apply Filter button appears pressed in.
To apply a filter automatically when a form is opened, specify in the OnOpen event property setting of the form either a macro that uses the ApplyFilter action or an event procedure that uses the ApplyFilter method of the DoCmd object.
You can remove a filter by clicking the pressed-in Apply Filter button, clicking Remove Filter/Sort on the Records menu, or using Visual Basic to set the FilterOn property to False (0).
Note You can save a filter as a query by clicking Save As Query on the File menu while in the Filter By Form window or the Advanced Filter/Sort window.
When the Filter property is set in form Design view, Microsoft Access does not attempt to validate the SQL expression. If the SQL expression is invalid, an error occurs when the filter is applied.
AllowFilters Property, ApplyFilter Action, ApplyFilter Event, ApplyFilter Method, BuildCriteria Method, Filter Event, Filter Property (Microsoft Office 95 Data Access Reference), FilterOn Property, OrderBy Property, OrderByOn Property.