ApplyFilter based on multiple criteria selected from combo boxes...
Posted on 2004-03-27
I am trying to create a task manager app using Access 2000. I have everything working the way I want it to so far except for 1 thing.
The database stores tasks that include the following fields:
Status (Open, Closed, Waiting, Canceled)
Category (Personal, Common, Test)
Priority (High, Medium, Low)
My main form obviously displays all of the fields. The form (frmTasks) is built off of a query (qryTasks) of the tblTasks. The form is not filtered. What I have on my form is a section that is used for filtering the Tasks.
This section contains 3 combo boxes:
What I want to happen with these is, I want to be able to filter the database by selecting any number of combination of values from these combo boxes. At first I got each combo box to work independently. Then what I did was add a checkbox for each combo box and made each combo box disabled by default.
I set up VBA code to have an integer for each checkbox (set the int to 0 on form open and set it to 1 if the chk is True). Then for each combo box
AfterUpdate, I set up a case statement that says if this box's int value is 1 then determine which combo box is enabled (also using the int value). From there it does a
DoCmd.ApplyFilter using the values from the combo boxes.
NOW. That was all working fine for a little while....
IF I selected a Status, let's say "Open", it would show me all of the open tasks. If I then checked Category and chose "Personal", it would show me all of the open personal tasks. Then, selecting a "High" priority would show me all of the open personal tasks that had a high priority.
I could do this in all sorts of combinations. Then I decided to compact and repair. I reopened the database and now all of those functions either work sometimes, or not at all, or in weird combinations. So, I went back and modified the code a bit....
It seemed to me that the reason it was working intermittently was that none of the selected values would be "cleared" when boxes were unchecked. So, I added code that set the values to "". That worked for a bit while I still had the db open, then I closed the db and went back in an hour later and the code stopped working again.
ok... now I am done with my long confusing story. I am hoping that someone can help me with my code. I am convinced that there is a simple solution that I am not seeing. I am relatively new to VBA, but I do know my way around. I just don't know all of the correct syntax yet.
Let me know if you need me to post my current code...
...or maybe send the whole darn database :-)
Any help would be much appreciated... thanks EDH