Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2004
    Location
    Vermont
    Posts
    53

    Unanswered: looking for advice

    I am looking to have a form populate a zip code based on the state and city a person types. Is there an easy way to do this? I am looking for suggestions on how to make this happen.

    Thanks,
    CAT :-D

  2. #2
    Join Date
    Apr 2009
    Location
    RI
    Posts
    31
    This can be done. Is it easy? Based on what you want to do, no... it's extremely difficult because your approach has 1 overwhelming flaw: cities commonly have more than 1 zip code. How are you going to know which zip code is correct for a given city? You'd need to base your zip code on the street address, plus city and state. This means a massive database with each street in a city, each city in a zip code, and each zip in a state.

    The more common approach is to work backwards from zip code. The user enters the zip, and the form populates the city and state. This is much easier because a zip code can only exist in 1 city.

  3. #3
    Join Date
    Feb 2004
    Location
    Vermont
    Posts
    53
    ok so how does it work as such? I assume it pulls from a table but does the text box require and if statement?
    CAT :-D

  4. #4
    Join Date
    Apr 2009
    Location
    RI
    Posts
    31
    if you're going to use the common method you'll need a zipcode table and probably a state table

    tblZipCode
    txtZipCode (primary key)
    txtCity
    lngStateID (foreign key)

    tblState
    lngStateID (primary key)
    txtState

    you could further normalize by having a City table as well (i.e. the city of Bristol could exist in multiple states) and link it to the zipcode table

    on the AfterUpdate property of the ZipCode field on your form, you'll need code to do a DLookup and populate the corresponding city and state fields for that zip code.

  5. #5
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    The more common approach is to work backwards from zip code. The user enters the zip, and the form populates the city and state. This is much easier because a zip code can only exist in 1 city.
    That doesn't apply in Australia! One postcode (aka zipcode) can and often contains several suburbs
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  6. #6
    Join Date
    Aug 2003
    Location
    Bulgaria, Plovdiv
    Posts
    36
    'txtZipCode (primary key)'

    In my hamble opinion I would never recommend text field as primary key.

Posting Permissions

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