SQL question re:combo boxes 1

[stillwillyboy]

I have the table Tbl_States. In this table I have the State, city and zip code as follows:

KS KC 66444
KS Topeka 66999
MO KC 64111
MO Spfld 67123
OH Cinncy 12345
OH Cleve 11111

I have a form with three combo boxes: cbo_State, cbo_city, and cbo_zipcode.

I have SQL for the cbo_state: SELECT DISTINCT tbl_states.state FROM tbl_states WHERE tbl_states.state ORDER BY tbl_states.state; This works as it should giving me a list of states.

I have SQL for cbo_city: SELECT DISTINCT tbl_states.state FROM tbl_states WHERE tbl_states.state ORDER BY tbl_states.state; This works as it should by giving me only the cities for the selected state chosen from cbo_state. i.e. Selecting MO will give only KC and Spfld. So far, so good.

I want the zipcode for the selected city and state to appear in the cbo_zipcode. I have SQL for cbo_zipcode: SELECT DISTINCT tbl_states.zipcode, tbl_states.city, tbl_states.state FROM tbl_States WHERE (((tbl_States.city)=forms!frm_state.cbo_city)) ORDER BY tbl_states.zipcode;

The problems are, (as an example) when I select KS, then KC, both zipcodes appear in cbo_zipcode. One for MO and one for KS. The only one appearing should be 66444 for KC, KS.

The other problem is that the querying only selects zipcodes when I come straight out of design view into form view and I had chosen a state before I went into design. When I select a new state and city in form view and then click the down arrow on the cbo_zipcode, it drops down but is blank. I have the following code in the state Change event:

Private Sub cbo_State_Change()
Me.cbo_city.Value = ""
Me.cbo_city.Requery
Me.cbo_zipcode.Value = ""
Me.cbo_zipcode.Requery

End Sub

Thanks, Bill

 

[flyover789]

how about the following solution:

in the cbo_State after update event:

me.cbo_city = ""
me.cbo_zipcode = ""

me.cbo_city.rowsource = "SELECT city " & _
"FROM tbl_States " & _
"WHERE State = '" & me.cbo_State & "';"

me.cbo_zipcode.rowsource = "SELECT zipcode " & _
"FROM tbl_States " & _
"WHERE State = '" & me.cbo_State & "';"

in the cbo_city after update event:

me.cbo_zipcode = ""

me.cbo_zipcode.rowsource = "SELECT zipcode " & _
"FROM tbl_States " & _
"WHERE City = '" & me.cbo_City & "' AND State = '" & _
me.cbo_State & "';"

HTH,
fly

[blue]Typos, that don't affect the functionality of code, will not be corrected.[/blue]

Martin Serra Jr.

http://www.mserrasystems.com

[blue]Database_Systems and _Applications shared across all Business_Areas[/blue]

 

[stillwillyboy]

Excellent! Thanks.