Ms Office Tune Up

One of form filtering’s limitations is that Access remembers only your most recent set of filters. If you’ve perfected a complex filter expression that you want to reuse later, this quality’s a problem. As soon as you apply a different filter, you’ll lose all your hard work.

Fortunately, you have several solutions to this dilemma. One option’s to create a whole new query that performs the filtering, and use that query in a whole new form. This choice is a good one if you want to use your filter criteria to perform a specific task, and you also want to customize the way the form works or the way it displays its data.

On the other hand, if you don’t plan to use your filtering settings very often, but you just want to have them on hand for the next time you need them (or if you need to store dozens of different filter settings, and you don’t want to be stuck with dozens of nearly identical forms), there’s a better option. You can save your filter settings as a query in your database. Then, when you want them back, you can load them up and apply them to your form.

Here’s how to pull this trick off:

1. Apply your filters.

   Use any of the techniques described in Section 9.2.

2. Choose Home Sort & Filter Advanced Advanced Filter/Sort.

   This action opens a query window. This query uses the same data source (table or query) as your form, and it applies your filtering using the Criteria box under the appropriate field (Section 6.2.1). You don’t need to make any changes in the query window because Access automatically fills in the Criteria box (or boxes) based on the current filter settings.

3. Choose Home Sort & Filter Advanced Save as Query. Supply a name for this query, and then click OK.

   Although you can use this query like a normal query, you probably won’t. So to prevent confusion, use a different type of name, like CustomerBrowser_Filter, that clearly indicates this query’s designed for form filtering.

The next time you want to retrieve your filter settings and reapply them, open your form and follow these steps:

1. Choose Home Sort & Filter Advanced Advanced Filter/Sort.

   This action shows the query window.

2. Choose Home Sort & Filter Advanced Load From Query.

   Access shows all the queries that use the same table and don’t involve joins (Section 6.3).

3. Pick the filter query you created earlier, and then click OK.

   The filter settings for that query appear in the query window.

4. Right-click anywhere on the blank space in the query window, and then choose Apply Filter/Sort to put your filter settings into effect.

Popularity: 2% [?]