What am I missing - Combo box lookup

  • Thread starter Thread starter Someone
  • Start date Start date
S

Someone

This should be easy, but for some reason I'm stuck.

Access 2000. Two tables:

1: State name and state abbreviation
2: List of cities and state abbreviation

The two tables have a one-to-many relationship from
table 1 to table 2 on the state abbreviation field.

So now I want a form where the user selects the state in a combo box
with the list of corresponding cities ap below.

Have I lost my mind?
 
Off the top of my head (not tested:)

combo box one: cboStateList
rowsource: "SELECT StateName, StateAbbreviation FROM tblState ORDER BY
StateName"
column count = 2
bound column = 2

combo box two: cboCityList
rowsource = "SELECT CityName FROM tblCity WHERE StateAbbreviation IS NULL"
column count = 1
bound column = 1

In the after update event of cboStateList:
Dim strSQL As String
' note the embedded single quotations in the following:
strSQL = "SELECT CityName FROM tblCity WHERE StateAbbreviation = ' " &
me.cboStateList.value & "' ORDER BY CityName"
Me.cboCityList.RowSource = strSQL
Me.cboCityList.Value = Me.cboCityList.ItemData(0)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Back
Top