Help with Forms: How to have form lookup City and State after Zip is supplied

I think this is probably simple, but I'm having a problem figuring it
out.

I have a form that is used to enter data. I want users to be able to
enter a zip code in the form, and have the city and state come up
automatically.

I have two tables:

Table 1 (14 fields): Customer Name, Address, City, State, Zip,
Preferences, etc
Table 2 (3 fields) : City, State, Zip

The form right now is completely based on Table 1. The data entered
in the form goes into Table 1. How can I get the form to look up City
and State after the Zip is entered, and enter the new data into Table
1?

Thanks for your help.

AM

re: Help with Forms: How to have form lookup City and State after Zip is supplied

In the AfterUpdate event of the ZipCode textbox you could use DLookup to
find the City and State then set the value of the City and State textboxes
to these results. The problem you may run into is that the Zip Code
frequently will return the city of the Post Office, not the city the person
lives in. The mail will get there with this, but the user may want to still
be able to edit the city.

Example:
Me.txtCity = DLookup("City", "Table2", "ZipCode = '" & Me.txtZipCode & "'")

You may need to adjust this slightly if the user is entering 9 digit zip
codes and your lookup table has 5 digit zip codes.

--
Wayne Morgan
MS Access MVP


"CaliSchmuck" <mann.amar@bls.gov> wrote in message
news:32d97000.0405030936.370c88cb@posting.google.c om...[color=blue]
> I think this is probably simple, but I'm having a problem figuring it
> out.
>
> I have a form that is used to enter data. I want users to be able to
> enter a zip code in the form, and have the city and state come up
> automatically.
>
> I have two tables:
>
> Table 1 (14 fields): Customer Name, Address, City, State, Zip,
> Preferences, etc
> Table 2 (3 fields) : City, State, Zip
>
> The form right now is completely based on Table 1. The data entered
> in the form goes into Table 1. How can I get the form to look up City
> and State after the Zip is entered, and enter the new data into Table
> 1?
>
> Thanks for your help.
>
> AM
>[/color]

re: Help with Forms: How to have form lookup City and State after Zip is supplied

"Wayne Morgan" <comprev_gothroughthenewsgroup@hotmail.com> wrote:
[color=blue]
>The problem you may run into is that the Zip Code
>frequently will return the city of the Post Office, not the city the person
>lives in.[/color]

Which may also in a few isolated situations be in a different state.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

re: Help with Forms: How to have form lookup City and State after Zip is supplied

I hope I understand the reply to the question.

May I comment that, in order for this to work, ALL possible ZIP Codes, with
their associated city and state information, have to be loaded into "Table
2". If you deal with customers/clients/donors/whatever on a nationwide
basis, that is a lot of work, and you may enter a lot of data that you'll
never need.

Perhaps it would be better, or less work, to query the existing Table 1 for
matching ZIP codes, then fill the city and state text boxes based on the
results of the query. If you add a new contact whose ZIP doesn't have any
matching data in the table, you can provide the user with an error message
that the city/state data isn't in the table, then provide a prompt to add
the new city and state info into the form they are already using. Once
that's done, that city/state info will be available the next time the same
ZIP is used. This may have an additional advantage in that you can ask the
user to verify the ZIP if you come up with no city/state data - this way you
may catch a few typos.

I maintain and upgrade a small database for an organization with 481 records
in the member table. Even though we serve a local audience, there are still
170 unique ZIP codes in the member table. I just queried it to find out. So,
it seems to me that using the information you already have might be easier,
rather than constructing, then maintaining, another table.

Just my $0.02, as they say.


Steve E.



"Wayne Morgan" <comprev_gothroughthenewsgroup@hotmail.com> wrote in message
news:ewvlc.4514$PM5.3076@newssvr24.news.prodigy.co m...[color=blue]
> In the AfterUpdate event of the ZipCode textbox you could use DLookup to
> find the City and State then set the value of the City and State textboxes
> to these results. The problem you may run into is that the Zip Code
> frequently will return the city of the Post Office, not the city the[/color]
person[color=blue]
> lives in. The mail will get there with this, but the user may want to[/color]
still[color=blue]
> be able to edit the city.
>
> Example:
> Me.txtCity = DLookup("City", "Table2", "ZipCode = '" & Me.txtZipCode &[/color]
"'")[color=blue]
>
> You may need to adjust this slightly if the user is entering 9 digit zip
> codes and your lookup table has 5 digit zip codes.
>
> --
> Wayne Morgan
> MS Access MVP
>
>
> "CaliSchmuck" <mann.amar@bls.gov> wrote in message
> news:32d97000.0405030936.370c88cb@posting.google.c om...[color=green]
> > I think this is probably simple, but I'm having a problem figuring it
> > out.
> >
> > I have a form that is used to enter data. I want users to be able to
> > enter a zip code in the form, and have the city and state come up
> > automatically.
> >
> > I have two tables:
> >
> > Table 1 (14 fields): Customer Name, Address, City, State, Zip,
> > Preferences, etc
> > Table 2 (3 fields) : City, State, Zip
> >
> > The form right now is completely based on Table 1. The data entered
> > in the form goes into Table 1. How can I get the form to look up City
> > and State after the Zip is entered, and enter the new data into Table
> > 1?
> >
> > Thanks for your help.
> >
> > AM
> >[/color]
>
>[/color]

re: Help with Forms: How to have form lookup City and State after Zip is supplied

Thanks Wayne for the response. I tried typing the command into the
after update event but kept getting errors. Am I missing something
here?

I typed the following into the afterupdate event of the Zip Code
Textbox:

State = DLookup("State", "Table2", "ZipCode = '"&ZipCode"'")

Am I doing something wrong with the spaces? The name of the ZipCode
field in both tables is "ZipCode" and the name of the State field in
both tables is "State".

I hope I can get this!


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

re: Help with Forms: How to have form lookup City and State after Zip is supplied

Steve E. has a very good suggestion that you may want to consider. The
procedure would be the same, just the name of the table would be different.
[color=blue]
> State = DLookup("State", "Table2", "ZipCode = '"&ZipCode"'")[/color]
For starters, you are typing this into the event procedure in the VBA
window, not directly into the Properties sheet, correct? Next, the syntax at
the end is off a little. You need to concatenate the zip code and the quotes
together so that the zip code gets passes as a text value (zip codes are
normally text, if you have them as number, you would need to change this).

State = DLookup("State", "Table2", "ZipCode = '" & ZipCode & "'")

Also, it appears that your textboxes have the same name as the fields they
are bound to. This may not cause you a problem here, but is can cause a
problem in some cases. I would recommend renaming them. Every object in your
database should have a unique name. The easiest way to rename them yet keep
them meaningful is to put a prefix on the name, such as txt for a textbox.
That would change the name of the State textbox to txtState. For a listing
of commonly used prefixes, check this link.

http://www.mvps.org/access/general/gen0012.htm

--
Wayne Morgan
Microsoft Access MVP


"AS Mann" <tansenva@aol.com> wrote in message
news:40969f80$0$201$75868355@news.frii.net...[color=blue]
>
> Thanks Wayne for the response. I tried typing the command into the
> after update event but kept getting errors. Am I missing something
> here?
>
> I typed the following into the afterupdate event of the Zip Code
> Textbox:
>
> State = DLookup("State", "Table2", "ZipCode = '"&ZipCode"'")
>
> Am I doing something wrong with the spaces? The name of the ZipCode
> field in both tables is "ZipCode" and the name of the State field in
> both tables is "State".
>
> I hope I can get this!
>
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it![/color]