3 tier combo - based on a sample database

[rhino213]

Hello!

I'm still working on this supplier database for my boss and the supplier filter section is still causing me problems.

Let me set the scene for you....

I have a form with 3 combo boxes on it. These are cboProg, cboPPS & cboSupplier.

I need to get the combo's working so that when you select a programme from cboProg it filters the list in cboPPS (Primary Product or Service) to show only products and/or services that are used on that programme. In turn I need to make it so that when you select one of these Primary Product or Service's from cboPPS it filters the list of suppliers in cboSupplier to show only suppliers that provide that product or service.

Essentially, I have a HUGE list of suppliers that I want to filter down to suppliers that work on a programme providing a particular product or service using the combo boxes in the order shown below.

Programme (cboProg) --(filters)--> Primary Product or Service (cboPPS) --(filters)--> Supplier (cboSupplier)

When I have filtered the list of suppliers down to a list that worked on programme X, providing product or service Y, I'd like to make it so that you can just double click on that suppliers name to open their details.

I've based my previous efforts on a sample database called dbcombos that I found on this forum (I've attached it to this post). The only problem with this database is that it’s 2 sets of 2 tiered combo boxes on the same form and not a 4 tiered set of combo boxes as I originally thought.

Any ideas? All help greatly appreciated!

 

[Pat Hartman]

It doesn't matter how many tiers there are once you understand the concept. The query for each combo except the first (highest level) references the previous combo (next highest in the hierarchy) for its criteria. The AfterUpdate event of each combo except the last(lowest level), requeries the next combo(next lowest in the hierarchy).