Results 1 to 5 of 5
  1. #1
    BigD
    Guest

    Populate City & State fields using entered Zip?

    I have built the contact database using the Access wizard and made some modifications. I have added a CityStateZip table which is fully populated. On my main contact form I want to be able to enter the zip code and automatically populate the City and State fields. Should the zip code not reside in the table, I want to use the entries to populate the CityStateZip table.
    Thanks in advance for the help[img]/w3timages/icons/grin.gif[/img]
    BigD

  2. #2
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    USA
    Posts
    386
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Populate City & State fields using entered Zip?

    To be correct, the key of the CityStateZip table should be stored on the MainContact table. This is point to the correct CityStateZip record when it is needed for display on forms or reports.

    To have the City and State field populate on the form when the Zip is entered can be done using a code in the event handlers for the Zip field. I have used the follwoing in the past:

    Private Sub txtZip_LostFocus()
    If IsNull(Me.txtZip) Then
    Me.txtCity = DLookup("City", "CityStateZip", "Zip = '" & Me.txtZip & "' ")
    Me.txtState = DLookup("State", "CityStateZip", "Zip = '" & Me.txtZip & "' ")

    End If

    End Sub

    This will lookup the City and State based on the Zip and then populate the form fields.

    Hope this helps

    Richard Aheron
    raheron@hotmail.com
    [img]/w3timages/icons/grin.gif[/img]
    Richard

  3. #3
    BigD
    Guest

    Re: Populate City & State fields using entered Zip?

    I appreciate the help but need a little more.
    Let me outline my DB.
    I have two tables of interest - Contacts and CityStateZip.
    I have set up a relationship between Contacts and CityStateZip with a field called Zipcode. Zipcode is the Primary Key for CityStateZip.

    In the form, Contacts, I have three, unbound text boxes with the names City, State and Country. The Zipcode field is from the Contacts table.

    When I inserted your code, nothing happened. Thinking the names were wrong, I made several different substitutions with no success.

    Any additional help would be appreciated.

    Thanks,
    Dan

  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    USA
    Posts
    386
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Populate City & State fields using entered Zip?

    I am attaching a db that has a form that works the way I think you are looking for.

    When the form opens, the City, State and Country populate.
    When you change records, the City, State and Country populate.
    When a new record is added and the zip exists in the tblCityStateZip, the City, State and Country populate.


    Hope it helps.


    Richard Aheron
    raheron@hotmail.com
    [img]/w3timages/icons/laugh.gif[/img]
    Attached Files Attached Files
    Richard

  5. #5
    BigD
    Guest

    Re: Populate City & State fields using entered Zip?

    Richard,
    Many thanks and a hat's off to you! You are the professor [img]/w3timages/icons/clever.gif[/img].

    I had to make some slight modification such as changing the period ('.') between Me to an exclamation point ('!') and removing prefix labels such as txt and tbl. It must be a case of version. I have both Access 97 and 2000. I am using 97 for this application.

    Thanks again,
    Dan

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •