macro that will fill in state and zip when a certain city is typed

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am working on a form for a high school reunion. I'm trying to make it
easier for the person that will be updating and entering records of alumni on
the form. I want it to automatically fill in state and zip code when San
Benito city is filled, and i want it to do the same thing if other cities are
typed and the pertaining data filled in.

thanks

rosa
 
There is no need for a macro.

Either take a look at the Northwinds sample database that ships with Access
and see how it handles similar situations in the "Orders" form. Or, do a
search and read the previous posts on this topic.

There are several ways to do it. Most would use a three-column lookup for
the City field and then pull the second and third column to the ZIP and
State field.

Here is the problem. You can't pull Zip based on City. You could pull City
based on ZIP. For example, Houston, Tx has at least 25 zip codes. If I
enter "Houston" it will not know which ZIP to pull. If, however, I enter
the ZIP, it would know which city and state to pull.

One more issue. Do you have the Zip code table built? Did you get a full
list of Zip codes/Cities/States? If so, do you have that in a table that
can be used for this lookup?
 
All i have is a table that has a person,lastname, first
name,address,city,state,zip code. I don't have a separate table for all zip
codes.
 
Then how will you look up ZIP codes and find the corresponding city and
sate, unless you already have an existing customer with that data?

You can still do it, just use the existing table in your lookup and pull
only unique values.
 
I do have a table that has all those columns, name address,city,state and zip
code for each person.
 
Then create a query that selects unique for those three items with the
Zip as the primary. When the user selects the zip the form would load
the city and state and save the zip.

However allow them to enter a zip that is not in the dropdown so that
they can create a new combination.

Word of warning, however. If you do not have an official table and not
allow additions to it, you are going to get multiple cities for the
same zip.

I used to live in Highland Park in the Los Angeles area. The official
name for the zip code was Los Angeles, BUT many people used Highland
Park in their addresses.

Assuming a halfway accurate table so far, you could have the zip be a
dropdown and once the user selected that the form could load the city
and state, but then you could have the city also be a dropdown that is
limited to occurances that have that zip code so the user could select.

But have the state locked out except when the zip was NOT already
present in the table. The Same Zip WILL NOT be for multiple states.

Ron
 
I still don't know how to do this. I am new to Access so I need step by step,
buth this is exactly what I want to do...PJF
 
Back
Top