combo box tree x8 - one query

[GPM4663]

Hi everyone,
I'm trying to develop a form that reacts like a selection tree. I have 9 combo boxes in it and I'd like each box to filter depending its previous boxes value. The good news is that the previous box value is the only value required for the criteria (no long AND statements needed) because the data is like a tree format i.e.

cbo1 could be PM001
cbo2 could be PM001-002
cbo3 could be PM001-002-005 etc

However I only want to use one query! How do i pass the cboX-1 value to the query criteria expression so that it filters correctly?

many thanks in advance

GPM

 

[Flippertje]

If i'm correct you're trying to use only one query for all 9 comboboxes; why?

What you need to do:

1. open the SQL window of youre query and copy the contents.
2. goto you're first combobox - rowsource - choose SQL - paste contents.
3. Go back to your query and open it in the normal view
4. Blow the field the combobox1 should filter on you enter the Criteria "Forms![you're formname].cbo1"
5. open the SQL view and copy the contents
6. follow step 1 and 2 but then use combobox2 etc etc etc for the following boxes.

7. At the 'Lost focus event' on the first combobox you place: me.requery

Hope you're getting the point. If not; reply!

grtz Flippertje

 

[MajP]

Any interest in actually using a TreeView control? Takes a marginal amount of coding, but I am a big fan for displaying data in that way if it is a logical tree structure. And like Flippertje said, what is the big hang up on using a single query? It can be done, but the above post is kind of the simple standard approach.

 

[GPM4663]

Thanks Guys,
I've got that sorted with your help. I'd love to use a treeview control if you can explain how. Is there any issue with users needing to install library references etc for each computer it's used on.

thanks again,

GPM

 

[MajP]

The treeview control comes with Access since A2K (or maybe earlier). It is under "More Controls", "Microsoft Treeview Control, Version X.X" It is part of the common control library. If interested there are a lot of examples in this site and on the web.