List style combo box issue

[whitelexi]

Hello everyone. I'm new to Access and need some help with a simple task which is giving me serious trouble. I have built a table called customer details and it contains a field called city, and another field for state. I want a user to be able to select a state from the stat combo box that i've created and have all the cities in the selected state appear in the city combo box.
At the moment i created 2 tables (one for city and one for state) and i have every city and state packed into those tables, so when i click the city combo box, all the cities in the country are listed, but i want only the cities in the selected state to appear instead.
Please can anyone help me with a workable solution?



Many thanks in advance.

[orange]

Do not use Lookups in Tables.
see http://access.mvps.org/access/lookupfields.htm

Use lookups in forms see comboboxes

For combo boxes where select State, then only Cities for that State appear in the City combo ==> Cascading combos
watch these free video tutorials
http://www.datapigtechnologies.com/f...combobox1.html
http://www.datapigtechnologies.com/f...combobox2.html

Good luck

[Missinglinq]

To begin with, you need a single Table for this, not 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:

1. Place a Combobox on your Form
2. When the Combobox Wizard comes up hit Cancel
3. With the Combobox selected, go to Properties - Other
4. In the Name Property enter cboStates
5. Click on the Data Tab

In the Row Source Property enter

SELECT DISTINCT tblStateCity.StateFieldName FROM tblStateCity ORDER BY [StateFieldName];

Now go into the AfterUpdate event for your cboStates Combobox and use this 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)>

[whitelexi]

Thanks everyone, with your help and suggestions, i've been able to resolve the issue... It works perfectly well now. Thanks again.

[Missinglinq]

Glad we could help!

Good luck with your project!

Linq ;0)>