Form with lookup wizard

[laqa]

How can I use the MS Acess lookup wizard in a form to search data from a table and then search another table depending on the result of the first search? It will be used for, once I choose a customer's state I can choose the city that the customer lives between the cities from that state (they have the required foreign key to their state).

Thanks, Luis.

[Missinglinq]

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:

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)>

[laqa]

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:

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)>

Thanks a lot for the reply. Unfortunately I cannot use your solution because I have to use 3 tables, Customer, State and City. This is because the database is not for real use, the solution will be used as an exercise for my students. I am a database Professor, and, as you are aware, using only one table for State and City violates the 3rd normal form, and this is very much emphasised in the course. Besides, my students do not have programming skills, and VB code is outside the course's program.

Thanks again,

Luis.