Dlookup on form not working

[vba__6229]

I am trying to set up a form where you enter a zip code and the city and state automatically populate. I can't get the Dlookup statement to work. I am hoping I can get some help.
I have a table called tblZipCodeData it has fields Zipcode, City, and State all three fields have a datatype of ShortText.
The form has three textboxes called txtZipCode, txtCity and txtState.
The txtCity textbox has the code in the afterupdate event box:

 =DLookUp("City","tblZipCodeData","ZipCode='" & txtZipCode & "'")

The txtState textbox has:

 =DLookUp("State","tblZipCodeData","ZipCode='" & txtZipCode & "'")

I don't get any errors but the textboxes don't populate. I am entering a zip code that I know exists in the table. At first, I was entering a zip code that had a zero in front of it and the zero keeps on disappearing so I started entering a zipcode that starts with 1 to avoid that issue.

 

[Andrzejek]

Did you try in txtZipCode_afterupdate event:

txtCity =DLookUp("City","tblZipCodeData","ZipCode='" & txtZipCode & "'") 
txtState =DLookUp("State","tblZipCodeData","ZipCode='" & txtZipCode & "'")

---- Andy

There is a great need for a sarcasm font.

 

[dhookom]

Are you expecting to store the City and State in your form's record source? Hint: this is not typically a good idea if the zipcode has unique city and state.

I would change the txtZipCode to a combo box with a Row Source of:

SELECT ZipCode, City, State FROM tblZipCodeDate ORDER BY ZipCode;

Then you can get rid of the DLookup() and use:

=cboZipCode.Column(1)
=cboZipCode.Column(2)

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016