Auto Enter County, State & Zip after entering City

Though townships may combine and use only one zipcode, there will always be only one USPS acceptable city for each Zipcode.  There are serveral sources for free zipcode databases.  The following is one example:

http://federalgovernmentzipcodes.us/

So when you build your tables you will want a table for Countries; States; Cities; Zipcodes and perhaps Counties.

Your Zipcodes table will reference the Cities table

In turn your Cities table will reference your States table

and again, in turn your States table will reference your Countries table.

So when you add a Zipcode to your addressee record, all the other related data will be available for display by using a query.

And though I agree with the gentlemen here about normalization, it may be a considerable necessity to store the individual components of your address in the addressee record: Country, State, City and Zip for the consideration of other countries where the address structure is quite different than that of the U.S..

These guys might have some other suggestions about foreign countries and how to manage them.  Best of luck.

Arvin,

I am intrigued by this hidden key...

I am also confused by the "When multiple cities have the same name," ---our service area is very small. (I have a table with the 27 cities, including the state, and zip and a second table for the county)

do share more :) I'm learning more every day!

Thanks!

When multiple cities have the same name, you'll need more information, and a unique key. Instead of a single column for city, you'll need to display a state column, and have either a key that shows or is hidden to actually do the lookup.

Arvin Meyer, MCP, MVP

http://www.datastrat.com

http://www.accessmvp.com

http://www.mvps.org/access

Co-author: "Access Solutions", published by Wiley

<REACH.Hawley> wrote in message news:*** Email address is removed for privacy ***...

I managed to figure out that a Macro will get the State, Zip & County to Auto-Fill after the City is entered. I'm still working on getting it to differentiate between different Cities....

...back to the books...

Arvin Meyer MCP, MVP MS-Access http://www.datastrat.com http://www.accessmvp.com http://www.mvps.org/access Co-author: "Access Solutions", published by Wiley

I managed to figure out that a Macro will get the State, Zip & County to Auto-Fill after the City is entered. I'm still working on getting it to differentiate between different Cities....

...back to the books...

sadly, i did, and i kept getting errors on the subforms...something about data type not matching.

it's about time to get someone standing over my shoulder :(

Ok, when you compare or translate data, DataTypes have to match. When you create a field in a table you have to choose the data type. You also have to be aware that some datatypes have subtypes. For example a Number datatype may be Long Integer, Integer, Double, Single etc. Also some datatypes have underlying types. For example a Yes/No field is really an Integer and a Autonumber is really a Long Integer.

So when comparing types you may have to delimit them. If you are comparing a text datatype you have to wrap the text in single quotes:

Ex: "[Address] = '" & Me.txtAddress & "'"

Dates need to be wrapped in the # sign

Ex: "[DOB] = #" & Me.txtDOB & "#"

And numbers need no wrapper

Ex: "[Quantity] = " & Me.txtQty

Hope this helps, Scott<> P.S. Please post a response to let us know whether our answer helped or not. Microsoft Access MVP 2010 Blog: http://scottgem.wordpress.com Author: Microsoft Office Access 2007 VBA Technical Editor for: Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports and Queries