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.

7 comments
james.hare
james.hare

Actually, there's an even easier way to do this that doesn't involve macros or command buttons at all. I call it the "filter by example" process.. lol First, create a small procedure in a module with the following code: Public Sub SetFilter() ' set filter on a control value ' place call to this proc in the click event of a text box - will filter on the form's current active control name On Error Resume Next Set Frm = Screen.ActiveForm With Frm .Filter = Screen.ActiveControl.Name + " = '" & Screen.ActiveControl.Value & "'" .FilterOn = True .Requery End With Set Frm = Nothing End Sub Then, as the procedure suggests, simple set a call to this routine in a field's ON CLICK event. In a continuous form, it's a very simple way to quickly filter the myriad values down to one specific value. You do need to alter this to handle numeric and date fields, because of the embedded quoting and all, but any competant developer should be able to see how. I use routines like this in virtually all of my continuous forms. I also include a "remove all filters" button so the users can see all the data again, when they need to: Public Sub RemoveFilter() Set Frm = Screen.ActiveForm With Frm .FilterOn = False .Requery .Refresh End With Set Frm = Nothing End Sub

brandon.kem
brandon.kem

I'm a little confused with this solution. In my On Click drop down, I only see the Macrogroup name and no sub-marcos. I typed in in manually and received a syntax error that I am using the macrogroup.macroname expression incorrectly. Can you please clarify the process of setting up a macrogroup? Thank you, bkem

mdsrds
mdsrds

Exactly what I was looking for. Thanks, my boss will think I am brilliant. :)

wittmav
wittmav

Why not just build the form based on a parameter query? Or one that pulls records from a custom form where visitors select from a combo box of options?

wolfhound_z
wolfhound_z

I am having difficulty with this code - it works great until I want to do another search/filter... it doesn't clear the previous value and simply applies the same filter. How do I get it to forget the last filter term? Any help is greatly appreciated

chuckoliver.hrc
chuckoliver.hrc

Ultimately, in creating a user-friendly form, you'll need to consider some of the truly stupid things a user can (and probably will)do in your system. I've always found it safer to totally control a user's behavior. For that reason, I'd strongly recommend that instead of killing yourself trying to figure out all the possible data sorts a user might want, try using generic cascading combo boxes [i.e. Screen.ActiveControl] to provide the choices. There are several articles available from the Access community sites that explain- in great detail- everything you'll need to do. Why create an object 10 times when you can make it once and reuse it whenever you need that functionality!

Tink!
Tink!

In my Access applications I have the user select from a drop down list to sort by that category (or other parameter) upon opening the form. Seems much simpler than creating a button for each well-used parameter.

Editor's Picks