Programming Apply Filter Command Buttons That Use Queries

[eHaak]

A couple years ago, I built a database in MS Access 2003. I built the form using macros in some of the command buttons, and now I’m trying to eliminate the macros and just use visual basic code. I’ve been successful in doing this for most of the buttons, but I’m having trouble reprogramming some Apply Filter buttons on one form and could use some help.

So I have a Contacts List form that lists all of the division’s staff members alphabetically by last name. There are three different departments in our division, each with its own filter button: AMMP, DMP, and HAV. Clicking the AMMP button shows an alphabetical list of that department’s staff, clicking DMP shows a different alphabetical list, etc. There’s also a Show All button that removes any filters and restores the original alphabetical list when clicked.

What I did originally was create a series of queries where I filtered the particular department I was interested in and sorted that department’s staff members alphabetically. So there was qryStaffAMMP for showing all of AMMP’s staff, qryStaffDMP for DMP staff, qryStaffHAV for HAV Staff, and qryStaff for showing the entire staff list alphabetically regardless of what department they came from. I linked these queries to macros and linked the Macros to the filter buttons on the Contacts List form. Now I want to take the macros out of the equation and have Visual Basic use these queries to filter the Contacts List form. Could anyone tell me how to do that?

[servantofone]

This answer may or may not work based on your table structure.

I would set your listbox source as only one query. Set up a combobox with a list of your department names (AMMP, DMP, and HAV). You can then specify your combobox as the criteria for the query. So instead of having LIKE "AMMP", you would have LIKE [FORMS]![FRMNAME]![COMBONAME]. In the combo properties, choose the AfterUpdate event and go to the VBA code. In the code, requery the listbox (ListboxName.re query). As you update the criteria in the combobox, the query criteria changes. When the criteria changes, your listbox will automatically update because of the combobox calling the AfterUpdate event which requeries the listbox.

That's how I would do it anyway.

-Kyle