Mulitple comb box "tree" x9 but only one query? 1

[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

 

[sozzer]

In the After Update property of the each combo box, do this (example is cbo1 setting rowsource of cbo2)

Dim strSQL as string
strSQL = "SELECT tblExample.* FROM tblExample WHERE tblExample.fldExample = '" & me.cbo1.value & "'"
Me.cbo2.RowSource = strSQL

Of course, this uses multiple queries but I gather you just didnt want loads of queries showing in the db window?

 

[GPM4663]

Sozzer,
Thanks for that, it's perfect. I knew there was a straight forward way to do it but couldn't see the wood for the trees.

thanks

GPM