I have made a program for my office that process incoming/outgoing mail in ms access 2000. This is my first project, and I am very new at this.

Now the data entry person have to enter city, state, zip code on each record. Is there any way person just enters zip code and city,state comes up automatically. Of course all the city,state, zip have to be in a separate table but how can I link it to the main table and when the person enters zip code the city, state comes up automatically. Any help will be greatly appricated.

Thank you

rajput

You could put this in the before update of your Zip Code text box: (Code is between the bold text)

Private Sub Zip_BeforeUpdate(Cancel As Integer)

City = DLookup("[City]", "tblCounty", "[Zip]= '" & Me!Zip & "'")
State = DLookup("[State]", "tblCounty", "[Zip]= '" & Me!Zip & "'")

End Sub

This will fill the City and State fields as soon as the Zip field is tabbed or entered out of. All you have to do is rename the controls to match those on your form.

Hope that helps! Let me know if you need more help with it.

For more information check out the thread I cut and pasted this from:

Topic: automatically update fields (http://www.access-programmers.co.uk/ubb/Forum1/HTML/002361.html)

[This message has been edited by Talismanic (edited 01-11-2001).]

Is the zip code unique for each city or districts within city?

Not in the US. Zip codes can cross city/state lines. I would use a combo box that not only displays the zip but also displays the city/state so the user can see when this anomoly occurs (it is rare but should be accounted for if you expect zip codes from anywhere). Then the user can scroll the list if necessary to get to the next entry for a zip code. Then in the AfterUpdate event of the combobox, store the city, state, and zip in bound fields so they will be saved to the table. The reason you need to do this is because you need the ability to override the zipcode combobox at any time just in case the zip data file gets outdated. You would not want to store erroneous data just because your zip table was outdated.

I personally ran into this situation in November when ordering some Christmas presents from catalogs. Two of the companies I called did not recognize my new zip code even though it was officially changed last June and we were notified of the impending change almost a year before that.

Thank you Talismanic, Richie, and Pat Hartman for your great help. Hartman you have a valid point however we only service 14 states in west coast. So Talismanic's idea will do for this project. Thanks again for your time.

Rajput