By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
458,183 Members | 1,386 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 458,183 IT Pros & Developers. It's quick & easy.

Programming Apply Filter Command Buttons That Use Queries

P: 1
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?
Oct 29 '08 #1
Share this Question
Share on Google+
1 Reply

P: 33
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.requery). 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.

Oct 29 '08 #2

Post your reply

Sign in to post your reply or Sign up for a free account.