Query Using Multiple Combo Boxes 1

[Hinfer]

Hi,

How do I build a form that queries using multiple combo boxes? I am trying to create a form that functions like MS Excel Advanced Filter function. That is, I have a table with the fields: Country, State/Province, City, Location. I want the user to pick the Country in the first combo box and the 2nd combo box will only contain State/Provices specified by the first combo box (country). The 3rd combo box will display of cities within the specified state/province and country. The last combo box will define the exact record (Location). I have other fields in the form that I want to autopopulate when the user picks the last combo box(Location). Again for my purpose, Location will define a unique record.

Can anyone give me any direction? If this has been posted here before, can someone point me to the right direction? I have found threads that dealth with one combo box but not multiple ones.

Thanks

 

[PaulF]

create separate queries for each combobox

for the one with State/Provience ComboBox use
Forms!YourFormName.YourCountryComboBoxName
as the Criteria for the State/Provience Field

for the one used for the Cities ComboBox use
Forms!YourFormName.YourCountryComboBoxName
Forms!YourFormName.YourStateComboBoxName
as Criteria Rows For Country, State/Provience Fields respectively

For the one for Location (and other data)
Forms!YourFormName.YourCountryComboBoxName
Forms!YourFormName.YourStateComboBoxName
Forms!YourFormName.YourCityComboBoxName
as Criteria Rows For Country, State/Provience and City Fields respectively

Remember to Requery the 2nd, 3rd and 4th ComboBoxes in the AfterUpdate Event for the 1st ComboBox

PaulF

 

[Hinfer]

This solution looks great but if you can give me a little more direction, much would be appreciated. I am novice user.

Thanks

 

[PaulF]

I'll try to break it down into more detail

First you have a Form (i.e. frmSelect) with 4 ComboBoxes on it, each one using a query for its Row Source

cboCountry
cboState
cboCity
cboLocation

for the query associated with cboCountry, it will contain only one field
Country: sorted in Ascending sequence
set the Unique Values property for the query to True (to display each Country only once)and

for the query associated with cboState, it will contain 2 fields
State/Provience: sorted in Ascending Order
Country: (set its Show Property to False) and use as the Criteria Forms!frmSelect.cboCountry
set the Unique Values property for the query to True (to display each State/Provience only once)

for the query associated with cboCity, it will contain 3 fields
City: sorted in Ascending Order
Country: (set its Show Property to False) and use as the Criteria Forms!frmSelect.cboCountry
State/Provience: (set its Show Property to False) and use as the Criteria Forms!frmSelect.cboState
set the Unique Values property for the query to True (to display each State/Provience only once)

for the query associated with cboLocation, it will contain the Country, State/Provience and City Fields Plus all the other fields You Want to display in cboLocation

Country: (set its Show Property to False) and use as the Criteria Forms!frmSelect.cboCountry
State/Provience: (set its Show Property to False) and use as the Criteria Forms!frmSelect.cboState
City: (set its Show Property to False) and use as the Criteria Forms!frmSelect.cboCity

Then in the AfterUpdate Event for each of the first 3 ComboBoxes, add Code to Requery the next ComboBox

for cboCountry add code to Requery the State ComboBox

Private Sub cboCountry_AfterUpdate()
cboState.Requery
End Sub

then Requery cboCity after updating cboState

Private Sub cboState_AfterUpdate()
cboCity.Requery
End Sub

then Requery cboLocation after updating cboCity

Private Sub cboCity_AfterUpdate()
cboLocation.Requery
End Sub

then add data to your Form's TextBoxes from the ComboBoxes and the respective column(s) in cboLocation

Private Sub cboLocation_AfterUpdate()
txtCountry = cboCountry
txtState = cboState
txtCity = cboCity
txtLocationData1 = cboLocation.Column(0)
txtLocationData2 = cboLocation.Column(1)
etc.....
End Sub

PaulF

 

[Guest_imported]

Hinfer --

Remember to add code that resets the combo boxes should a user stop at combo box 3, and start over again at combo box 1...i.e., clear the combo boxes (I usually disenable combo boxes util User input is required)...

 

[Hinfer]

PaulF - Thanks for all your help it works beautifully!

Louise Lemir - Thanks for the tip but I am not sure where or how to add code?