I do this with a single Table, not with separate city and state Tables. Each Record in this Table needs to have both state and city Fields. After you have that straight you can use this step-by-step:
- Place a Combobox on your Form
- When the Combobox Wizard comes up hit Cancel
- With the Combobox selected, go to Properties - Other
- In the Name Property enter cboStates
- Click on the Data Tab
In the Row Source Property enter
Code:
SELECT DISTINCT tblStateCity.StateFieldName FROM tblStateCity ORDER BY [StateFieldName];
Now go into the AfterUpdate event for your cboStates Combobox and use this code:
Code:
Private Sub cboStates_AfterUpdate()
cboCities.RowSource = _
"Select Distinct tblStateCity.CityFieldName " & _
"FROM tblStateCity " & _
"WHERE tblStateCity.StateFieldName = '" & cboStates.Value & "'" & _
"ORDER BY tblStateCity.CityFieldName;"
cboCities = ""
End Sub
Now repeat Steps 1-4, above, but in Step 4, instead of entering cboStates, enter the name cboCities.
Replace tblStateCity with the actual name of your Table that contains both the Cities and States, and CityFieldName and StateFieldName with the actual names for these Fields from your Table.
Linq ;0)>
The problem with making anything foolproof...is that fools are so darn ingenious!
All posts/responses based on Access 2003/2007