Almost got it - need a push!!

[rmyslik]

It has been a while since I used this database and I really need to make a change. I have a form that I want to auto fill the state and zip code. I started with the zip using code I found here. My Table is Zip Codes which has State, Zip, and City. Below is what I came up with in the onExit area.

Me!City = DLookup("City", "Zip Codes", "City=[Zip]&quot

WHat stupid thing am I missing? I know it hasn't clicked in my brain but it has to be simple!

 

[RobertT687]

I think you need to test for "[Zip] = Zip".

 

[rmyslik]

Private Sub Zip_Exit(Cancel As Integer)
Me!Zip = DLookup("Zip", "zip codes", "city=[Zip]&quot
End Sub
Gives me the first record of the city field?
The table is names Zip Codes
The fields are
City
State
Zip.
This seems like it should be easy? What am I doing or not doing?

 

[B827]

Try

Me!City = DLookup("[City]", "[Zip Codes]", "[Zip]=" & Me!Zip & &quot

 

[hsitz]

B827's version looks good to me, minus the ending quote:

Me!City = DLookup("[City]", "[Zip Codes]", "[Zip]=" & Me!Zip )

 

[rmyslik]

Hmmm?
I am getting an error. Runtime error '3075'
Syntex error missing operator (in query expression) '[zip]='

 

[rmyslik]

Hmmm?
I am getting an error. Runtime error '3075'
Syntex error missing operator (in query expression) '[zip]='

from this code....

Private Sub Zip_Exit(Cancel As Integer)
Me!City = DLookup("[CITY]", "[ZIP CODES]", "[ZIP]= " & Me!Zip)
End Sub
I tried with and without the ". I must assume that this opens the zip codes table and looks up the zip code that corrosponds with the CITY column and should give the zip code, right?

 

[hsitz]

Yes.

That is the correct syntax for a dlookup function, assuming that all the field and table name references are accurate.

Are you sure that me!Zip has a value when the procedure is run? It will definitely result in an error if the zip textbox is blank, and you should have code to take care of that. Something like:

if not isnull(me!zip) then
Me!City = DLookup("[CITY]", "[ZIP CODES]", "[ZIP] = " & Me!Zip)
end if

 

[Hmadyson]

If Zip is text you will need single quotes around it,so DLookup("[CITY]", "[ZIP CODES]", "[ZIP]= '" & Me!Zip & "'&quot

 

[hsitz]

Hmadyson -- I think you got it! Embarrassed to have missed that little item. -- Herb

 

[rmyslik]

Thanks all. I appreciate all your help!