filtered cascading combo boxes

[tonycl69]

Hi, I know there has been many threads written around cascading combo boxes, but I can't seem to figure out if any of them fit my problem.
I have a continuous form. in the header I have 3 combo boxes based on 3 tables of single firlds, each one filters the form i.e location, category and status. But when I filter a category I can't then filter in that category a location, the location combo just filters the location and ignores the category etc. What I would like is to still be able to filter from each combo box but also narrow each combo like a cascading combo box set if you get my drift.

 

[Pat Hartman]

OK, I'll summarize again.
The simplest way to control cascading combos is to use queries for the RowSources that reference the "parent" combo for criteria. So the RowSource for combo3 references combo2 and the RowSource for combo2 references combo1. It doesn't matter if you have 15 nested combos. Each one references the one to its "left" for criteria. Then the only code you need goes into the AfterUpdate event of each combo and it needs to requery all combos to the "right"
So, in a three combo set, combo1 must requery 2 and 3.
Me.combo2.Requery
Me.combo3.Requery
And combo2 only needs to requery combo 3
Me.combo3.Requery

 

[tonycl69]

Thanks for your reply, this is fine if I were using a single form scenario, but I am using a continuous form. I have 3 combo boxes based on 3 tables which are just lists that feed a main table, I have a qry behind the continuous form, each combo box filters 3 different columns succesfully but I need to drill down instead of just filter 3 columns. Can you help with this please.

 

[Pat Hartman]

I posted an example that might help you.

http://www.access-programmers.co.uk/forums/showthread.php?t=240976

 

[tonycl69]

Thank you for your help, I don't seem to have explained myself very well, my fault. I have a continuous form that shows all records in a vehicle maintenance db. I have 3 combo boxes that filter 3 fields, location, category and status. They have there source from 3 tables with just a list of location, category and status. These are in the form header. But the problem is when I select any combo box to filter the main form it works fine but as soon as I select one of the other combo boxes to filter I lose the filter of the previous filter, thus I need to have cascading lists to drill down if I need to but still filter on any of the 3, so if I choose category I need the other 2 to only have location and status relative to category and any combination of the 3. Is this at all possible or is there perhaps an alternative method. All help appreciated.

 

[Pat Hartman]

Combos either cascade or stand alone. You can't have both effects. Take a look at the example I posted. It is intended to solve the visibility problem that occurs with cascading combos in continuous forms. If your combos are not bound but are located in the header and used for searching, the "cascade" still works the same way. You still need to requery the lower level combos to the "right" when one to the "left" changes and you still need the RowSources of combos to reference the FK field from the combo to the "left"