Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Dlookup on form not working

Status
Not open for further replies.

vba__6229

Programmer
Mar 24, 2017
3
US
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:

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

The txtState textbox has:
Code:
 =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.



 
Did you try in txtZipCode_afterupdate event:

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



---- Andy

There is a great need for a sarcasm font.
 
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:

Code:
SELECT ZipCode, City, State FROM tblZipCodeDate ORDER BY ZipCode;
Then you can get rid of the DLookup() and use:

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




Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top