Combo box values based on another field 4

[rib742]

Hello!

I have 2 fields; State and City. What I'm trying to accomplish is 1) if the state is selected from a combo box, then only the cities for that state are able to be selected (based on a table that contains state and city). and vice versa, 2) if the city is selected, then the state is automatically valued.

I've been banging my head for hours.

Thanks!

 

[humbleprogrammer]

See faq702-681

 

[GingerR]

without knowing your object names, i have:

form: Main
State combo box: cboState
City combo box: cboCity

cboState rowsource is just a table with unique StateCodes and StateName:

NY New York
WA Washington
with statecode hidden, and bound field = 1

=========================================
cboCity rowsource is your city/state table in a query where StateCode = cboState from the form

tableName: Cities

StateCode City
WA Seattle
WA Tacoma
NY Rochester
NY Buffalo

cboCity rowsource:

SELECT DISTINCTROW Cities.StateCode, Cities.City
FROM Cities
WHERE (((Cities.StateCode)=[forms]![main]![cboState]));

with statecode hidden, and bound field = 2

=========================================

in OnChange property of cboCity, put

    Me.cboState = Me.cboCity.Column(0)

this will make the cboState be set to the state after you select a city.

then you have to be able to reset the cboCity so it shows all cities, not just the ones belonging to the state you've chosen. so i put on a button next to the cboCity, called btnResetCity with the word RESET

    Me.cboCity.RowSource = "SELECT DISTINCTROW [Cities].[StateCode], [Cities].[City] FROM [Cities];"
    Me.cboCity.Requery

this is simply making the rowsource of cboCity show all cities from your city table.

hope this helps.

g

 

[rib742]

Thanks for the help!!! I got it to work.

FAQ702-681 was great.