Fill in City & State from Zip Code (wedding database help)

[Guest]

I have a table of guest info (name, street address) I have the table I
downloaded with the zip code, city state. I understand I have to make a query
to combine these 2 tables. Everytime I try, something isn't working & no
information shows up. I need a form where I can enter a name, a street
address & a zip code. I want the city and state to pre-fill from entering the
zip code.

 

[Wayne Morgan]

In the AfterUpdate event of the zip code textbox, lookup the city and state
values and assign them to the city and state textboxes. One thing to
remember is that the city is probably going to be the city of the main post
office that handles that zip code, it may not be the actually city that the
address is in. The mail will get there that way, but if you look at a map it
may not be where you are expecting it to be.

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

For the state, replace City with State. Zip Codes are frequently stored as
text, even if they are numbers. Also, I don't know if the table you have has
5 or 9 digit zip codes, so you may need to do some modification. The first 5
digits should be all you need to get the correct city and state.

You could hard code this in and just have the textboxes automatically
display the city and state from the table, but this option will allow you to
change the city if it isn't correct (see comment in first paragraph).

 

[Guest]

I will try this, thanks- I should have noted that I'm pretty novice with
Access.

 

[Guest]

Do I add this to the query? Here is my querey:

SELECT [Guests].[Prefix], [Guests].[FirstName], [Guests].[LastName],
[Guests].[SpouseName], [Guests].[ChildrenNames], [Guests].[Address],
[Guests].[HomePhone], [zip].[City], [Guests].[Zip], [zip].[ST],
[Guests].[WorkPhone], [Guests].[MobilePhone], [Guests].[AlternativePhone],
[Guests].[FaxNumber], [Guests].[EmailAddress], [Guests].[DateUpdated],
[Guests].[DateLastTalkedTo], [Guests].[NumberAttending],
[Guests].[Confirmed], [Guests].[NeedDaycare], [Guests].[HealthIssues],
[Guests].[PlaceStaying], [Guests].[RoomNumber], [Guests].[CheckinDateTime],
[Guests].[CheckoutDateTime], [Guests].[CheckedIn], [Guests].[GiftGiven],
[Guests].[ThankYouNoteSent], [Guests].[Notes]
FROM Guests LEFT JOIN zip ON [Guests].[Zip]=[zip].[ZIP];


Right now, it will not let me edit anything info in the query/form.
I thought this would be so nice & easy to track guest info for a wedding.
Thought it would be easy to type in the zip code & auto fill the city &
state. I was oh so very wrong.

 

[Wayne Morgan]

No, this wouldn't be done in the query. Also, adding the second table (Zip)
using a Left Join will probably make the query not updateable and thereby
make the form not editable. You would be using the Zip table simply as a
"lookup" table to find the city and state. You would actually still have a
City and State field in the Guest table to store these values in. In the
AfterUpdate event of the Zip control on the form, you would look up the
associated city and state in the Zip table and assign those values to the
City and State controls on the form, to be stored in the Guests table. This
will allow you to edit the City and State if the lookup table wasn't
correct.

--
Wayne Morgan
MS Access MVP

Do I add this to the query? Here is my querey:

SELECT [Guests].[Prefix], [Guests].[FirstName], [Guests].[LastName],
[Guests].[SpouseName], [Guests].[ChildrenNames], [Guests].[Address],
[Guests].[HomePhone], [zip].[City], [Guests].[Zip], [zip].[ST],
[Guests].[WorkPhone], [Guests].[MobilePhone], [Guests].[AlternativePhone],
[Guests].[FaxNumber], [Guests].[EmailAddress], [Guests].[DateUpdated],
[Guests].[DateLastTalkedTo], [Guests].[NumberAttending],
[Guests].[Confirmed], [Guests].[NeedDaycare], [Guests].[HealthIssues],
[Guests].[PlaceStaying], [Guests].[RoomNumber],
[Guests].[CheckinDateTime],
[Guests].[CheckoutDateTime], [Guests].[CheckedIn], [Guests].[GiftGiven],
[Guests].[ThankYouNoteSent], [Guests].[Notes]
FROM Guests LEFT JOIN zip ON [Guests].[Zip]=[zip].[ZIP];


Right now, it will not let me edit anything info in the query/form.
I thought this would be so nice & easy to track guest info for a wedding.
Thought it would be easy to type in the zip code & auto fill the city &
state. I was oh so very wrong.

I will try this, thanks- I should have noted that I'm pretty novice with
Access.