Filtering a Lookup value based on other Lookup values.

[mbenton]

Hello,

Any suggestions would be appreciated. I have three large source tables imported into my database. I have created queries to retrieve relevant values from fields in each source table which feeds into my form. Each field on my form that is connected to the relevant query is a lookup field. For example, one field called "Supplier_Name" another called "Supplier_Code" and a third called "Route_Number". Needless to say each of my lookup fields are very long. I am trying to filter my search based upon the selection from the previous Lookup field. Does anyone know how I can filter a lookup field's value based upon the previous lookup field selection? Each Supplier has a code and assign route(s) and I have already established these relationships. Thanks.

[ranman256]

To do the multi-combo box
each combo box has a query for its recordsource.
Here the user picks a state, then in the next combo picks a City (in that state)

The 1st combo,(say cboStates)... User picks a state, then picks a company from the cboCity box.
The cboStates AfterUpdate event will trigger when the user picks it, and this will update the next combo.

sub cboStates_AfterEvent()
 cboCo.requery
end sub

The cboCity query (say qsCityViaState) will reference the cboStates in the query sql
Select [city] from tZipCodes where [ST] ='" & forms!frmMain!cboStates & "'"

The City combo must be refreshed (cboCity.requery action) after user picks the state so it can deliver the resulting dataset.

If there is another combo after this say cboEmps to pick employees, then the cboEmp must be refreshed after user picks cboCity.

sub cboCity_AfterEvent()
 cboEmps.requery
end sub

[June7]

Sounds like cascading comboboxes. Review http://datapigtechnologies.com/flash...combobox2.html

[mbenton]

Very helpful, thank you.

[mbenton]

Got it, thanks