Results 1 to 3 of 3
  1. #1
    laqa is offline Novice
    Windows XP Access 2003
    Join Date
    Mar 2009
    Location
    Rio de Janeiro
    Posts
    15

    Form with lookup wizard

    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.

  2. #2
    Missinglinq's Avatar
    Missinglinq is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Location
    Richmond (Virginia, not North Yorkshire!)
    Posts
    3,016
    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

    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

  3. #3
    laqa is offline Novice
    Windows XP Access 2003
    Join Date
    Mar 2009
    Location
    Rio de Janeiro
    Posts
    15
    Quote Originally Posted by Missinglinq View Post
    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

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

    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.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. LOOKUP WIZARD, is it possible?
    By finsmith in forum Programming
    Replies: 5
    Last Post: 01-24-2013, 09:45 AM
  2. lookup wizard
    By dafdek in forum Access
    Replies: 1
    Last Post: 01-10-2013, 04:57 PM
  3. lookup wizard
    By bigmac in forum Access
    Replies: 1
    Last Post: 03-10-2012, 02:09 PM
  4. Lookup Wizard Problem
    By Boltman in forum Access
    Replies: 2
    Last Post: 07-31-2011, 10:06 PM
  5. Lookup wizard
    By VLI in forum Forms
    Replies: 7
    Last Post: 01-28-2011, 09:25 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums