Zip Code Lookup

[Bill]

I'm trying to do a zip code look up where the user enters a zip code then the
city and state is populated base on a table. My table is called ZIP_CODES and
the table which will be holding all data is "Address". When the user enters
the zip code into the form I want the city and state to autofill. Here's
what I have:

Private Sub Zip_Code_AfterUpdate()
Dim rsCurr As DAO.Recordset
Dim strSql As String
strSql = "SELECT City, State " & _
"FROM tblZIP_CODES " & _
"WHERE Left(Zip_Code, 5) = '" & Left(txtZip_Code.Value, 5) & "'"
Set rsCurr = CurrentDb().OpenRecordset(strSql)
If rsCurr.EOF = False Then
State = rsCurr!State
txtCity = rsCurr!City
End If
End Sub

Thanks for the help.

Bill

 

[John Spencer]

Is that not working? It appears that it should work.
Are you getting any errors?

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

 

[Dennis]

What is txtZip_Code ? It is not defined in your code.
Can you not simply do a DlookUp as below ?

State = Nz(DLookup("[State]","[tblZIP_CODES]","Left([ZIP_Code],5) = '" &
Left(Zip_Code,5) & "'"),"State not found")

City = Nz(DLookup("[City]","[tblZIP_CODES]","Left([ZIP_Code],5) = '" &
Left(Zip_Code,5) & "'"),"City not found")

 

[Bill]

I get an error and debug highlights the following string

strSql = "SELECT City, State " & _
"FROM tblZIP_CODES " & _
"WHERE Left(Zip_Code, 5) = '" & Left(txtZip_Code.Value, 5) & "'"

 

[Bill]

That could be the problem. Ok so if I use the DLookup I can just use a
macro? I just want the user to see it on the form. When they enter the zip
code I want the city and state to update automatically. Thats why I was
thinking of using the afterupdate. I'm still learning this and trying to get
things to work for me.

Thanks

What is txtZip_Code ? It is not defined in your code.
Can you not simply do a DlookUp as below ?

State = Nz(DLookup("[State]","[tblZIP_CODES]","Left([ZIP_Code],5) = '" &
Left(Zip_Code,5) & "'"),"State not found")

City = Nz(DLookup("[City]","[tblZIP_CODES]","Left([ZIP_Code],5) = '" &
Left(Zip_Code,5) & "'"),"City not found")

I'm trying to do a zip code look up where the user enters a zip code then the
city and state is populated base on a table. My table is called ZIP_CODES and
the table which will be holding all data is "Address". When the user enters
the zip code into the form I want the city and state to autofill. Here's
what I have:

Private Sub Zip_Code_AfterUpdate()
Dim rsCurr As DAO.Recordset
Dim strSql As String
strSql = "SELECT City, State " & _
"FROM tblZIP_CODES " & _
"WHERE Left(Zip_Code, 5) = '" & Left(txtZip_Code.Value, 5) & "'"
Set rsCurr = CurrentDb().OpenRecordset(strSql)
If rsCurr.EOF = False Then
State = rsCurr!State
txtCity = rsCurr!City
End If
End Sub

Thanks for the help.

Bill