I need city and state to populate after I enter the Zip Code in Access

[imnotsmart]

I have an Access DB that I'm looking to have the City, State, and some other info auto populate after I type the Zip. I am VERY VERY new to Access and am teaching myself. I don't know VB and would need to be walked through step by step. Any help would be possible.

Thanks

[pbmods]

Heya, imnotsmart. Welcome to TSDN!

I'm going to go ahead and move this thread to the Access forum, where our resident Experts will be better able to help you out.

[Rabbit]

Is it safe to assume you have a table listing all cities, states, and zip code?

[imnotsmart]

Is it safe to assume you have a table listing all cities, states, and zip code?

Yes. I have the table with all the Towns, Cities, Zips and other info I need.

[Rabbit]

In the after update event of the zip code control you'd have something along the lines of:

Private Sub txtZip_AfterUpdate()
   txtCity = DLookup("City", "tbl_ZipCityState", "Zip = " & Me.txtZip)
   txtState = DLookup("State", "tbl_ZipCityState", "Zip = " & Me.txtZip)
End Sub
 

This assumes zip is a numeric data type.

[imnotsmart]

The Zip is a text data type (Zip codes in NJ start with 0. If its numeric the 0 gets dropped). The table is called City. The fields are City, State, Zip. So based on what you posted before, I tried this:

Private Sub Zip_AfterUpdate()
txtCity = DLookup("City", "tbl_City", "Zip = " & Me.txtZip)
txtState = DLookup("State", "tbl_City", "Zip = " & Me.txtZip)
End Sub

When I go back to the form, I get the following error.

Compile error:
Method or data member not found

.txtZip on line 2 is highlighted in blue and the first line is highlighted in yellow.

[Rabbit]

If Zip's text then you'll need to do:

"Zip = '" & Me.txtZip & "'"
 

txtZip, txtCity, and txtState are in reference to the controls on your form, I doubt that's what you named them so you'll have to change those to match.

[imnotsmart]

Thanks!! I played with it a little and got it to work.

Now, another question. I have yes/no buttons associated with each town and a number. Is the code the same or does it change.

[Rabbit]

Thanks!! I played with it a little and got it to work.

Now, another question. I have yes/no buttons associated with each town and a number. Is the code the same or does it change.

I'm not following, can you go into more detail?

[imnotsmart]

Nevermind, I figured it out.

But I have another question. I need the info that populates in the City, State and Zip field to fill in City, State, and Zip fields in another table. Don't know if this is possible.

[Rabbit]

Nevermind, I figured it out.

But I have another question. I need the info that populates in the City, State and Zip field to fill in City, State, and Zip fields in another table. Don't know if this is possible.

You mean into an additional table? You'll need to use DoCmd.RunSQL "INSERT INTO ..."

[imnotsmart]

Yes, into an additional table called People.

[Rabbit]

Ok, then try out what I suggested in post #11.

[imnotsmart]

Not sure how to do it. Can you give a little more. Here is some more info.

After the City, State, and Zip are entered for a Person in my form, I need the City, State, and Zip to also show up under that Person in a different table.

[Rabbit]

Not sure how to do it. Can you give a little more. Here is some more info.

After the City, State, and Zip are entered for a Person in my form, I need the City, State, and Zip to also show up under that Person in a different table.

What you'll want to do is, in the after update event of each of the fields, check that all 3 fields are populated, i.e. not null. Then you want to check to see if a record already exists in the other table. If it does, you use DoCmd.RunSQL "UPDATE SQL String". If not, you use a DoCmd.RunSQL "INSERT INTO SQL Append String"

[terryechols1]

I found this interesting and was wondering if it could be expended a bit.

My table of zip codes has Primary City Name [PrimaryCityName] and Acceptable City Names [AcceptableCityNames]. I'd like for the code to change the [City] control on the form to an option drop down showing the alternative Acceptable City Names that the user could then select from. The acceptable city names are stored comma separated so sometimes they will have multiple acceptable city names to select from. Both are Short Text data types.

Here is my code pulling just the three things I want to use but I don't know how to code the options part of it.

Private Sub ZIP_Code_AfterUpdate()
 
   City = DLookup("PrimaryCityName", "tblZipCodes", "ZipCode = '" & Me.[ZIP Code] & "'")
 
   State = DLookup("StateCode", "tblZipCodes", "ZipCode = '" & Me.[ZIP Code] & "'")
 
   Country = DLookup("Country", "tblZipCodes", "ZipCode = '" & Me.[ZIP Code] & "'")
 
End Sub

Thanks,
Terry