cascading combo boxes

[Guest]

This is my first time asking a question and I am not an expert.

The Setup

I have multiple tables with reapeating variables. The first problem was
that they did not always have the same variables or the same number of them.
I fixed this by using a union querry. I have made simple unbound combo boxes
to search this union querry. Now I must do something much more complicated.

The Question/Problem

In reality this is a much larger database I am simply making it smaller for
ease of the question.

I have 5 columns in the union querry.
I want to be able to select one of the column names with a combo box. I
then want the next 2 combo boxes to show only the values in that column. I
then want them to pick a low value for one combo box and a high value for the
other box. When I would hit the OK command button it would only give the
rows that fall inbetween the high and low.

Here are names for everything.
qryUnited
qryDefinedbyForm
frmSearch
cboVariable
cboHigh
cboLow

 

[Michel Walsh]

Have the list of fields as row source for your first combo box. In its after
update event, write:




Dim str As String
if(0=len(vbNullString & me.ComboBox1.Value ) ) exit sub

str="SELECT DISTINCT " & Me.ComboBox1.Value & " FROM
tableNameHere"

If(Me.ComboBox2.RowSource <> str) Me.ComboBox2.RowSource=str
If(Me.ComboBox3.RowSource <> str) Me.ComboBox3.RowSource=str




where I assumed ComboBox1, ComboBox2 and ComboBox3 are the combo box name.




In the OK button click, define a filter, like:



if(0=len(vbNullString & me.ComboBox1.Value ) ) exit sub
Me.FilterOn=false
Me.Filter = Me.ComboBox1.Value & " BETWEEN
FORMS!formNameHere!ComboBox2 AND FORMS!formNameHere!ComboBox3 "
Me.FilterOn = true





Hoping it may help,
Vanderghast, Access MVP