Example of a MS access database that can look up values and automatically enter them?

Question

I want to have Access enter the values of city name and state abbreviation when I enter the zip code in a field. I have the database for the zip codes, but I need an example database of whatever linking or codes I have to perform to have these actions automated for me. The zip is entered and then the city and state fields are automatically looked up and the fields populated with the correct information. I can't find anything that is easy or clear online (probably because folks make their money off of their databases :-)). Anyone have their access database automated already? Or does anyone have a link to an existing database example that I could look at for a lead?

I did find this code, but I am so new to access it means nothing to me :-) Would this work, provided I understood what exactly was happening?

************* Code Start **************
' This code was originally written by Erika Yoxall.
' It is not to be altered or distributed,
' except as part of an application.
' You are free to use it in any application,
' provided the copyright notice is left unchanged.
'
' Code Courtesy of
' Erika Yoxall
'
Sub Zip_OnExit(Cancel As Integer)
Dim varState, varCity As Variant
varState = DLookup("State", "tblZipCode", "ZipCode =[Zip] ")
varCity = DLookup("City", "tblZipCode", "ZipCode =[Zip] ")
If (Not IsNull(varState)) Then Me![State] = varState
If (Not IsNull(varCity)) Then Me![City] = varCity
End Sub
'************* Code End **************

Answer 1

I like the kludge method. Here's how I do it:

the ZIP codes are in a table called tblZIPCodes

I am trying to enter a person into a form called frmContacts (recordsource is the table tblContacts), with controls txtZIP, txtCity and txtState.

After entering my ZIP in txtZIP, I trigger a LostFocus event. The LostFocus event opens another form, called frmZIPKludge, that has its recordsource set to the following SQL:

SELECT tblZIPCodes.City, tblZIPCodes.State, tblZIPCodes.ZIP
FROM tblZIPCodes
WHERE (((tblZIPCodes.ZIP)=[Forms]![frmContacts]![txtZIP]));

frmZIPKludge has the controls txtCity, txtState, and txtZIP set to the corresponding table fields.

The code in the LostFocus event looks something like this:

Private Sub txtZIP_LostFocus
Dim HadFocus
HadFocus = Screen.ActiveControl
DoCmd.OpenForm "frmZIPKludge",,,,acHidden
Me.txtCity = Forms!frmZIPKludge!txtCity
Me.txtState = Forms!frmZIPKludge!txtState
DoCmd.Close acForm, "frmZIPKludge"
Me.HadFocus.SetFocus
End Sub

I think that's about right. I was checking to make sure and Access locked up on me.

----------------------------

Oooh, I like your code there... I've never used DLookUp before, but looking at Access VBA help... Yeah, that would work! Sweet, you've rendered my Kludge obsolete! I ♥ U!!

Basically, what you're doing there is setting up variables as containers to hold information pulled up by the DLookup function, which pulls up the specified field in the specified table with the specified criteria. Then, if the ZIP Code existed in your database (signified by the variables not holding the null state, aka not being empty) the information held in those two variables would be put into the City and State fields on your form.

The table holding the ZIPs in this code is tblZipCode, containing field City, State and ZipCode.
the controls on the form are called City, State and Zip
if that's set up right, then you should have no problem with the code.

Answer 2

Here's the general idea (assuming that you are using a form to enter the data):

1) Create an update query which writes the city and state to their respective fields, based on the ZIP code (match the entered ZIP to the one in your ZIP code data) AND where the Record ID (or other unique field) is the one that you're currently on.

2) Create a macro or procedure that runs the update query.

3) Assign the macro (or procedure) to the "After Update" Event property of the ZIP code field on the form.

It will probably take some experimentation to get everything working properly, but it should work.

Answer 3

Look in the validation event for the zip code text box. When you hit enter after typing in the zip code it can check if it's a valid zip code and draw the matching city state.

I don't have a specific code example with me but I hope that gets you a start.

Answer 4

Check the help file for date formats - it would explain how.

Answer 5

Use the wizards if you don't know how to manually do it.

Answer 6

visit http://msaccess.batcave.net