Microsoft Access: ZipCode Lookup and field fill

• scottwhetsell_a_s_wvsp / 204 / Fri, 05 Jun 2009 11:39:00 GMT / Comments (8)
• I would like to be able to type a zip code into a form, and have it
  automatically return the City and State into their respectiver fields, or
  enter the city and state and have it return the zip code.
  
  I have one table which contains Zip, City, and State fields which is the
  master listing for the Zip Codes.
  
  The other table is where I would like the data to be stored after it is
  entered and looked up. This table is basically a contacts table with name,
  address, phone, etc.
  
  I have been trying this for about two weeks and can't get it to work. Any
  help is appreciated, thanks in advance.
  
• Keywords:

  zipcode, lookup, field, fill, microsoft, access

• http://database.itags.org/ms-access-database/207240/«« Last Thread - Next Thread »»

• 1. You don't have to store the city and state in the other table as you can
     always find them through the ZIP. Base all your lookup forms and reports
     (mailing labels, etc.) on a query based on both tables with the link between
     ZIPs.
     
     Scott Whetsell, A.S. - WVSP wrote:
     >I would like to be able to type a zip code into a form, and have it
     >automatically return the City and State into their respectiver fields, or
     >enter the city and state and have it return the zip code.
     >I have one table which contains Zip, City, and State fields which is the
     >master listing for the Zip Codes.
     >The other table is where I would like the data to be stored after it is
     >entered and looked up. This table is basically a contacts table with name,
     >address, phone, etc.
     >I have been trying this for about two weeks and can't get it to work. Any
     >help is appreciated, thanks in advance.
     Message posted via droptable.com
     http://www.droptable.com/Uwe/Forums.aspx/access-tablesdbdesign/200706/1
     
     

     jahoobobviaaccessmonster_com | Sun, 01 Jun 2008 13:57:00 GMT |

  2. This approach doesn't always work.
     
     We have a pair of adjacent municipalities in my area that share a single zip
     code. Storing the zip code only would NOT resolve which city.
     
     Regards
     
     Jeff Boyce
     Microsoft Office/Access MVP
     
     "jahoobob via droptable.com" <u12179...uwe> wrote in message
     news:7414d3fc4ec4b...uwe...
     > You don't have to store the city and state in the other table as you can
     > always find them through the ZIP. Base all your lookup forms and reports
     > (mailing labels, etc.) on a query based on both tables with the link
     > between
     > ZIPs.
     > Scott Whetsell, A.S. - WVSP wrote:
     > --
     > Message posted via droptable.com
     > http://www.droptable.com/Uwe/Forums.aspx/access-tablesdbdesign/200706/1
     >
     
     

     jeffboyce | Sun, 01 Jun 2008 13:58:00 GMT |

  3. Yes, a better way would be to index all the cities ZIPs and store the index
     in the main table but from what Scott stated, that he will type a ZIP and a
     city and state will be stored, indicates he has only one-to-one city to ZIP.
     I used to live in Pace, FL 32571, however, if you put 32571 in any on line
     registration it will show up as Milton, FL. It really doesn't matter what
     the city, the PO delivers by ZIP.
     I haven't tried it but I believe you could put Timbuktu as the city and 20500
     as the ZIP and your mail will get to the White House.
     
     Jeff Boyce wrote:[vbcol=seagreen]
     >This approach doesn't always work.
     >We have a pair of adjacent municipalities in my area that share a single zip
     >code. Storing the zip code only would NOT resolve which city.
     >Regards
     >Jeff Boyce
     >Microsoft Office/Access MVP
     >[quoted text clipped - 16 lines]
     Message posted via droptable.com
     http://www.droptable.com/Uwe/Forums.aspx/access-tablesdbdesign/200706/1
     
     

     jahoobobviaaccessmonster_com | Sun, 01 Jun 2008 13:59:00 GMT |

  4. In reference to the comment that several smaller towns may fall under one
     zipcode reaffirms my need to be able to store the city/state/zip in the main
     table so I can change the city in such cases.
     
     Thanks
     
     "jahoobob via droptable.com" wrote:
     
     > Yes, a better way would be to index all the cities ZIPs and store the index
     > in the main table but from what Scott stated, that he will type a ZIP and a
     > city and state will be stored, indicates he has only one-to-one city to ZIP.
     > I used to live in Pace, FL 32571, however, if you put 32571 in any on line
     > registration it will show up as Milton, FL. It really doesn't matter what
     > the city, the PO delivers by ZIP.
     > I haven't tried it but I believe you could put Timbuktu as the city and 20500
     > as the ZIP and your mail will get to the White House.
     >
     > Jeff Boyce wrote:
     > --
     > Message posted via droptable.com
     > http://www.droptable.com/Uwe/Forums.aspx/access-tablesdbdesign/200706/1
     >
     

     scottwhetsell_a_s_wvsp | Sun, 01 Jun 2008 14:00:00 GMT |

  5. On Fri, 22 Jun 2007 11:22:00 -0700, Scott Whetsell, A.S. - WVSP
     <ScottWhetsellASWVSP...discussions.microsoft.com> wrote:
     
     >In reference to the comment that several smaller towns may fall under one
     >zipcode reaffirms my need to be able to store the city/state/zip in the main
     >table so I can change the city in such cases.
     
     That's certainly how I do it. Zip to City is in reality a many to many
     relationship: one zip can cover several cities and many larger cities have
     multiple zips.
     
     If you go to the USPS Zipcode directory online
     
     http://zip4.usps.com/zip4/citytown.jsp
     
     you will see that multicity zips have a "preferred city" but if the zip is
     correct it will be delivered whichever city is named; and depending on the
     nature of the mail and the feelings of the recipient, it might be important to
     use the actual city where the person lives rather than the name of the city
     where their post office stands.
     
     John W. Vinson [MVP]
     

     johnw_vinson | Sun, 01 Jun 2008 14:01:00 GMT |

  6. If you type in a ZIP (as you say) and you have multiple cities for any one
     ZIP the first city will always be the one you get.
     
     Scott Whetsell, A.S. - WVSP wrote:[vbcol=seagreen]
     >In reference to the comment that several smaller towns may fall under one
     >zipcode reaffirms my need to be able to store the city/state/zip in the main
     >table so I can change the city in such cases.
     >Thanks
     >[quoted text clipped - 20 lines]
     Message posted via droptable.com
     http://www.droptable.com/Uwe/Forums.aspx/access-tablesdbdesign/200706/1
     
     

     jahoobobviaaccessmonster_com | Sun, 01 Jun 2008 14:02:00 GMT |

  7. I understand that it will pick the first match, which in most cases will be
     the one I need. However, the problem at hand is how to get it to populate
     the fields with the information in the first place.
     
     "jahoobob via droptable.com" wrote:
     
     > If you type in a ZIP (as you say) and you have multiple cities for any one
     > ZIP the first city will always be the one you get.
     > Scott Whetsell, A.S. - WVSP wrote:
     > --
     > Message posted via droptable.com
     > http://www.droptable.com/Uwe/Forums.aspx/access-tablesdbdesign/200706/1
     >
     

     scottwhetsell_a_s_wvsp | Sun, 01 Jun 2008 14:03:00 GMT |

  8. Scott
     
     In the AfterUpdate event of the combobox, add something like:
     
     Me!txtCity = Me!cboZip.Column(1)
     Me!txtState = Me!cboZip.Column(2)
     ...
     where the SECOND field in your combobox's quey is the City and the THIRD is
     your State (.Column() is zero-based).
     
     Regards
     
     Jeff Boyce
     Microsoft Office/Access MVP
     
     "Scott Whetsell, A.S. - WVSP"
     <ScottWhetsellASWVSP...discussions.microsoft.com> wrote in message
     news:7279BFF5-B5AE-404F-BEBF-A34487177EC6...microsoft.com...[vbcol=seagreen]
     >I understand that it will pick the first match, which in most cases will be
     > the one I need. However, the problem at hand is how to get it to populate
     > the fields with the information in the first place.
     > "jahoobob via droptable.com" wrote:
     
     

     jeffboyce | Sun, 01 Jun 2008 14:04:00 GMT |