Drop Down that returns value in same table

[AngelM]

I am still a beginner with Access so I'm sorry if it takes me a while to
communicate my needs and answer the clarifying questions.

I have a form that requires an address, city, state, zip code and county. I
have the zip code set as a drop down. My goal is for the user to input the
zip code, then the city, state and county autofill in the same table. I'd
like to eliminate as much data entry as possible. I have a table of all the
possible zip codes with the corresponding cities and counties.

 

[KARL DEWEY]

My goal is for the user to input the zip code, then the city, state and
county autofill in the same table.
If you already have the information in a table then it is not necessary to
enter it again in another table. Just join the tables on the Zip when you
build queries.

 

[Maarkr]

are you aware that there are many zip codes with multiple towns? if your
area does not include any, you should be OK... your zip db will also be large
if you have every zip in the US.

 

[Armen Stein]

If you already have the information in a table then it is not necessary to
enter it again in another table. Just join the tables on the Zip when you
build queries.

Except that one Zip can result in more than one City. So maybe the OP
is asking how to *default* the information.

You can add other columns for City, St, etc. to the combobox for the
Zip field, assuming that you have the most likely values in the Zip
table. If you don't want them to display in the dropdown list, set
their widths to zero.

In the After Update event of the Zip field, you can use a bit of VBA
code to set the other fields to the columns of the Zip combobox,
something like this:

Me.City = Me.cboZip.Column.x

(where x is the number of the column that contains City - remembers,
the Column count starts with 0)

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

 

[John Spencer]

Also some zip codes overlap counties (I know of one in Maryland that is valid
for 4 counties).

What I generally do is use the ZIP CODE to fill in default values for city,
state, and county. See Armen Stein's posting.

With county I might use a combobox that changes the available choices as the
zip code is entered/present. I might do the same with city.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[Owner]

county autofill in the same table.
If you already have the information in a table then it is not necessary to
enter it again in another table. Just join the tables on the Zip when you
build queries.