Data Management

Filter Access forms with one button

Looking for a needle in an Access haystack? Mary Ann Richardson shows how to add a button that will easily filter the records you use the most.

If a user only works with certain records in an Access database, it's time-consuming for them to navigate through records that do not pertain to the job at hand. For example, suppose each of your customer service representatives are assigned to take care of customers within several cities. You can add a button to the form that will allow the rep to filter out only those records that pertain to their designated city. To do so, follow these steps:

  1. Open your database containing the form and click Macros, then click New.
  2. Click the Macro Names button.
  3. Under the Macro Names column, enter Show Marlton.
  4. Under Actions, select ApplyFilter.
  5. Under Action Arguments, in the Where Condition box, enter:
    [City]="Marlton"
  6. In the next blank row in the Macro Name column, enter Show Medford.
  7. Under Actions, select ApplyFilter.
  8. Under Action Arguments in the Where Condition box, enter:
    [City]="Medford"
  9. In the next blank row in the Macro Name column, enter Show All.
  10. Under Actions, select ShowAllRecords.
  11. Close and save the macro as CityFilter.
  12. Open the form in design view.
  13. Click the Command Button in the toolbox, and then click and drag in the form where you want to put your button.
  14. Select Form Operations under Categories.
  15. Select Apply FormFilter under Actions. Click Next.
  16. Click Apply Filter in the text box and enter Show Marlton. Click Next.
  17. Enter ShowMarltonRecords. Click Finish.
  18. Right-click the Show Marlton button and select Properties.
  19. Under the Event tab, click in the On click property text box and select CityFilter.Show Marlton from the drop-down list. Close the Properties box.
  20. Click the Command Button in the toolbox, and then click and drag in the form where you want to put your button.
  21. Select Form Operations under Categories.
  22. Select Apply FormFilter under Actions. Click Next.
  23. Click Apply Filter in the text box and enter Show Medford. Click Next.
  24. Enter ShowMedfordRecords. Click Finish.
  25. Right-click the Show Medford button and select Properties.
  26. Under the Event tab, click in the On click property text box and select CityFilter.Show Medford from the drop-down list. Close the Properties box.
  27. Click the Command Button in the toolbox, and then click and drag in the form where you want to put your button.
  28. Select Form Operations under Categories.
  29. Select Apply FormFilter under Actions. Click Next.
  30. Click Apply Filter in the text box and enter Show All. Click Next.
  31. Enter ShowAllRecords. Click Finish.
  32. Right-click the Show All button and select Properties.
  33. Under the Event tab, click in the On click property text box and select CityFilter.Show All from the drop-down list. Close the Properties box.
  34. Click the Close button and then click Yes to save your form.

Now the representative that services Marlton can click the Marlton button to filter out all non-pertinent records; while the representative that services Medford can click the Medford button.

Miss a tip?

Check out the Microsoft Access archive, and catch up on our most recent Access tips.

Help users increase productivity by automatically signing up for TechRepublic's free Microsoft Office Suite newsletter, featuring Word, Excel, and Access tips, delivered each Wednesday.

Editor's Picks

Free Newsletters, In your Inbox