Filter using multiple listboxes (optimizing question)

[hkaing79]

I have several three tables:

tblStaff: StaffID, LastName, FirstName, School, Track, Position, Active

tblProgram: StaffID, Program

tblGrade: StaffID, Grade

I had to separate Program and Grade because each Staff can participate in multiple Program and Grade.

I have 6 filters: School, Grade, Active, Track, Program, Position

I have a listbox that will list all the LastName and FirstName of the Staff who meets those criteria.

To query them, I created three sql strings:
1. strSchool = Select StaffID from tblStaff Where School, Active, Track, and Position filters
2. strProgram = Select StaffID from tblProgram Where Program
3. strGrade = Select StaffID from tblGrade Where Grade

Then I will Select LastName, FirstName from tblStaff WHERE StaffID in (strStaff AND strProgram AND strGrade)

All this is done on Form_Open. Oh, there's two forms. frmStaff and frmFilter. I did this because other forms will be filtered by frmFilter as well.

Now, the problem is when the user makes a change on frmFilter. I planned on using After_Update to requery, but that would mean I would have to rerun all those filters again.

All fields are unbound. All the fields in frmFilter are listboxes (extended multiselect), except for Active (combobox).

Is there a better solution?