Zip Code and DLookup

[KathCobb]

I have no idea how I am going about this so wrong. I literally have twenty tabs open in three different internet searches trying to figure this out and now I am just completely lost.

I am trying to build an address form that if you type in a zip code it populate City, County, State from that zip code. I am keeping it simple for this example. Let's all pretend. One zip code is unique for all cities, counties and state. The table is already populated with some data.


tblZIp
pkZipCodeID
txtZipCode
txtZipCity
txtZipCounty
txtZipState


I was advised to keep the Zip Code field on the form where the user enters it as a TEXT box. But every where I look online it says use combo box to populate unbound text boxes. Which I understand. Combo box, two fields, one hidden(theID) the one showing is for the zip code. I really don't want the user to scroll--just type in all five digits of the zip code. If I don't use a combo box then I don't understand how typing in the Zip Code doesn't somehow require the ZipCodeID?

I was advised to use D lookup. I have never used D lookup. I built this database over ten years ago. I haven't touched a thing access or vba related since. I remember very little.

So I tried to figure out the Dlookup code from this example:
Look up the CompanyName field from table Company, where CompanyID = 874. This translates to:
=DLookup("CompanyName", "Company", "CompanyID = 874") --> then there was stuff about nulls, etc

To me the above translate to: Lookup the ZipCode field (txtZipCode) from table ZipCode where pkZipCodeID = some zipcode ID number


So I wrote this: =DLookup("txtZipCode", "tblZipCode", "txtZipCode = " & Nz([txtZipCode],0))

But I got stuck...because where is my zipcodeID? I don't have that on my form. Also I have NO IDEA where to put this code. When I google D lookup, it says build an expression. I've never done that before. Am I building this expression in the text box where the user types the zip code? Am I putting it the city box? I am so so lost. I've been at this for hours and hours and just going around in a circle. Can someone please please please either point me to a website that explains this start to finish or tell me where to start at all with this?

Thank you for any and all assistance.

Kathy






Should it be:
=DLookup("txtCity", "tblZipCode", "txtZipCode = " & Nz([txtZipCode],0))
If so where does that go? Then how do I get the county and state to fill in?

Can you please help me with the next step? Where do I start? I have my previous form. I've made all four fields text boxes. In my mind, I need them to type in the Zip Code text box after they finish typing in the zip code and hit TAB, I want the other three fields to fill in. Let's for a minute just pretend there aren't any other choices. Just where do I start to make that happen?

[ranman256]

dlookup is good only for 1 return item.
if you want to fill in all boxes; city, st
then you either pull 2 dlookups or create a subform that loads the data fields based on zip.
NOTE: there are more than 1 city for 1 zip code. (thanks USPS) Yep, its not 1 to 1.
also zipcodes must be alpha (not numeric) since some start with zero

dookup:
DLookup("[return field]", "tblZipCode", "[field]= '" & vVariable & "'") & ""

to prevent NULL returns , add the (& "") on the end
make some custom function:

function getCityViaZip(pvZip)
getCityViaZip=DLookup("[City]", "tblZipCode", "[ZipCode]='" & pvZip & "'") & ""
end function



function getStateViaZip(pvZip)
getStateViaZip=("[State]", "tblZipCode", "[ZipCode]='" & pvZip & "'") & ""
end function

[ranman256]

you really should return a recordset, since you could get more than 1 city,

select case rst.recordcount 
 case 0
   'no city found
 case 1 
   'load this city
 case else
   'put list into combo box , let user pick the correct city
end select

[davegri]

Have a look at this

https://www.accessforums.net/showthread.php?t=79265

[KathCobb]

I couldn't et back to work on this until today. I'm trying to figure out one thing at a time.

"then you either pull 2 dlookups or create a subform that loads the data fields based on zip"
Where do I put these Dlookup? On the AfterUpdate event of the text box where I am entering the zip code?


"you really should return a recordset, since you could get more than 1 city"

The recordset thing is exactly what I want to do, I am just trying to get it to work on step at time because I am so confused.

What I am going to need to do is also after the user enters the information, the fkZipCode will have to save back to the address table. There is a lot going on here.

I was going to have a form open up to give the user a choice if there is more than one because yes, there are actually a few different combinations when you add in county. There can be two cities in one county with the same zip code but there can also be two cities in two different counties with the same zip code.

"https://www.accessforums.net/showthread.php?t=79265"

And that link may answer a lot of my questions, but on a quick glance it doesn't seem to have a Primary Key in that table. I'm going to study this and report back. If you know of what I would have to do to work with a primary key any tips are greatly appreciated.

THANK YOU ALL SO MUCH!