DLookup Help

[barbarao]

Hi, I am trying to use DLookup on an after update. When the end user enters a zip code in the PracticeZip field, I want to have the County appear in the PracticeCounty field. There is a table called ZipCode that houses the zip codes and counties. If someone can help me figure out why this isn't correct, that could be great. Thanks.

Dim varX As Variant
varX = DLookup("[County]", "ZipCode", "ZipCode = 'Form![PracticesForm]![PracticeZip]'")
 

[ADezii]

Assuming [ZipCode] Field is a String:

varX = DLookup("[County]", "ZipCode", "ZipCode = '" & Forms![PracticesForm]![PracticeZip] & "'")

[barbarao]

Yes,Zip is a nvarchar. I tried what you suggested and nothing. I then moved it to the GotFocus event on the PracticeCounty field to see if the code was just in the wrong place and nothing then either. Any other thoughts?

[Rabbit]

That can't be all the code you use to populate the control, what's the rest of it?

[barbarao]

That was all of it. What I was doing is trying to get the filed "County" to autopopulate after the end user has entered a value in the "PracticeZip" field. I have a separate table that stores Counties and Zips.

[Rabbit]

If that's all your code, then your control isn't going to have a value. You assigned your DLookup to a variable called varX. But you never assign varX to your control.

Think of it this way, I want to give you ten dollars so I give your friend ten dollars but I don't tell your friend to pass that ten dollars on to you. How come you don't have the ten dollars?

Having said that, the only reason to do this through VBA is because you plan on storing the county. But unless your you need that county for posterity, you are breaking one of the rules of normalization by storing duplicate data.

What you should do is just put the DLookup in the control source and not store the county again in PracticeCounty. You can always get the county when needed by joining to your zip code table.

[barbarao]

I'm having a hard time following you. Sorry. I don't want to store the name of the County in the Practice Table but the county's number value. I hope that explains what I had in mind. I'm gong to keep rereading what you wrote so maybe I wil get it.

[barbarao]

Hi,

I finally understand what you wrote and have things under control here. Thanks as usual for your help.

[Rabbit]

No problem, good luck with the rest of your project.