Making a form control the criteria for the sql statement of another control (Access 2003)

[SteMc]

I have two comboboxes on a form. Combobox1 uses field1 in another table
as its source of data. What I want to do is to enable combobox2 to use
field2 in that table, but only to show the rows of data that contain
the item selected in combobox1.

At the moment I can easily write a SQL statement with a WHERE clause,
but I only know how to specifiy static criteria. What I want is a
variable criteria, where the criteria is the data contained in combobox
1.

Is this possible?

thanks for any help :)

Ste

[Darryl Kerkeslager]

"SteMc" <go****@jasmonio.supanet.comwrote

>I have two comboboxes on a form. Combobox1 uses field1 in another table
as its source of data. What I want to do is to enable combobox2 to use
field2 in that table, but only to show the rows of data that contain
the item selected in combobox1.

At the moment I can easily write a SQL statement with a WHERE clause,
but I only know how to specifiy static criteria. What I want is a
variable criteria, where the criteria is the data contained in combobox
1.

In the AfterUpdate event of ComboBox1, specify the Row Source of ComboBox2
as a SELECT query. So if the first combo selects the state, then the second
combo of cities is filtered by that state:

Private Sub cboState_AfterUpdate()
Me!cboCity.RowSource = "SELECT city_id, cty_name FROM city WHERE
city_state = ' " & Me!cboState & " ' "
Me!cboCity.Requery
End Sub

--
Darryl Kerkeslager