Autopopulate city & state from zipcode table

[HenryTD]

I'm stuck on something that should be simple, but maybe it isn't:

Problem 1:

To speed data entry in a contact table, I want an entry in the Zipcode (postal code) field to automatically populate the City and State fields from the data in a Zipcode table (Zipcode-PK, City, ST (2 letter code)). The Zipcode table has only one city /st for each zipcode, so it can be the primary key (always a five digit number, sometimes with leading zero(s)).

Sometimes there are several names for a City associated with a particular Zipcode, so I want the City field to be able to be overwritten with a different city name if the data entry person needs to do that. Alternatively, I could establish a separate primary key for the zipcode table, and allow several choices for the rare zipcode that has more than one city name in use.

If I did that I wouldn't have to store the city and st in the contact table (which is better database design, I realize), but I really do want to be able to store the city and state in the contact table because there will be times when the right zipcode isn't known, and the city and state are known, and forcing the input of a possibly incorrect zipcode would be undesirable.

Problem 2: I've got a table with 4,000 records where I have the zipcode, but the city and state information were lost, and I want to be able to re-populate those fields by writing, I guess, an update query that taps the info in the zipcode table.

Problem 3: When entering addresses, sometimes there won't be info about a certain zipcode, and I need some automatically generated dialog that allows adding city and state info for a new zipcode that won't slow down the entry of contact records.

Any help would be greatly appreciated. Thanks in advance. Send an email if you prefer.

HenryTD

 

[FoFa]

Hows about: Say the Lost Focus event of your ZIP code, you run a SUB that first checks to verify it is a valid ZIP (5 or 9 in length, if you are using only USA zip codes, or if no zip then just exit to aloow manual entry). Then counts how many are available from the table. If it is zero, let them know the zip does not exist. If one, just populate the data in the fields in the form. If more than one, popup a form with a list box and allow them to select the zip (say double click as an example) on the one they want to use. Once populated, they could over ride it since they would be text fields.
Just my thoughts.

 

[Pat Hartman]

This example db shows three ways to populate look up fields. You need a combination. You want to use the join technique for the state field because you don't want to be able to override that. You want to use the combo columns technique for the city name because you do want to store (duplicate) the city name so that it can be overridden.

http://www.access-programmers.co.uk/forums/showthread.php?p=293920#post293920

 

[HenryTD]

Thank you Pat and Fofa. I'll let you know how I make out.

HenryTD