Zip code populate other fields table help needed (1 Viewer)

plog

Banishment Pending
Local time
Today, 00:41
Joined
May 11, 2011
Messages
10,126
Would any of my coding or wanting to give the user options be easier if I included a zip code type field in my table?

I don't understand your question. Perhaps an example.
 

KathCobb

Registered User.
Local time
Yesterday, 22:41
Joined
Jun 29, 2012
Messages
46
This is how USPS classifies them..
1586960065235.png
 

plog

Banishment Pending
Local time
Today, 00:41
Joined
May 11, 2011
Messages
10,126
Ok? Does that help you in any manner?
 

KathCobb

Registered User.
Local time
Yesterday, 22:41
Joined
Jun 29, 2012
Messages
46
Lol...I was asking you. Should I keep those columns? I was thinking I'd eliminate that column and just make those one of the options. So fo 1483 there would be two combinations in the zip code table. Then the Dlookup of greater than one would kick in. Is that the easiest?
 

plog

Banishment Pending
Local time
Today, 00:41
Joined
May 11, 2011
Messages
10,126
So fo 1483 there would be two combinations in the zip code table. Then the Dlookup of greater than one would kick in. Is that the easiest?

I don't follow the value of 1483. But if you only want one record per Zip code, then just include only one zip code in the table and make that the primary key.

I thought you were going to allow the user to select the specific record if their were multiple matches in the Zip Code table?
 

KathCobb

Registered User.
Local time
Yesterday, 22:41
Joined
Jun 29, 2012
Messages
46
I'm sorry that was a typo of the zip code from above table. 14830 has an alternate city. So yes, I guess I am going to take those alternates and make them their own record in table Zips instead of trying to incorporate a Zip "Type". I do want the user to select if there is multiple choices.

I am really back to basics here. Right now I am trying to figure out how to change my form. Right now City, County, State, Zip are four individual combo boxes. What do I do with them now? Do I make them four text boxes? I'm in Chapter One on Forms trying to refresh my memory. I've done zero things with access coding in ten years....
 

plog

Banishment Pending
Local time
Today, 00:41
Joined
May 11, 2011
Messages
10,126
You change them to text inputs.
Disable them (because they are for display purposes not for user input).
Change their datasource to Dlookups using the record's fkZipID value into tblZips
 

KathCobb

Registered User.
Local time
Yesterday, 22:41
Joined
Jun 29, 2012
Messages
46
"You change them to text inputs.
Disable them (because they are for display purposes not for user input)."

I live the Zip Code enabled because it is going to be where the type that to populate the other fields. Is that correct?
 

plog

Banishment Pending
Local time
Today, 00:41
Joined
May 11, 2011
Messages
10,126
Yes. Then you put a button next to it that the user clicks. Thats where you put the code to handle the 3 options:

0 results
1 result
>1 result
 

KathCobb

Registered User.
Local time
Yesterday, 22:41
Joined
Jun 29, 2012
Messages
46
It has to be a button click? It can't just be an after update event from zip code filed? Such as, they type in the zip code then tab or click out of the text box?
 

plog

Banishment Pending
Local time
Today, 00:41
Joined
May 11, 2011
Messages
10,126
I am sure that coudl work too.
 

KathCobb

Registered User.
Local time
Yesterday, 22:41
Joined
Jun 29, 2012
Messages
46
Would the zip code need to be a combo box that hides the pkZip and shows that zip code number only?
 

plog

Banishment Pending
Local time
Today, 00:41
Joined
May 11, 2011
Messages
10,126
Not if you want to allow zip codes not in the system. It should be a text input.
 

KathCobb

Registered User.
Local time
Yesterday, 22:41
Joined
Jun 29, 2012
Messages
46
Ok, I have combo boxes the if its not in the system they get asked if they want to ass and it takes them to an "Add A Whatever" form. I won't let them add anything directly in the form. But right now I am beyond confused where to even start.

Am I putting a Dlookup or a Dcount? Do I put this in my Zip Code data source on my form? Or do these go in the City or County fields? And when I am in a text vox and click the ... it takes to an expression builder. Is that where I want to be?

I literally have twenty tabs open in three different internet searches trying to figure this out backwards and I just lost. Every where I look it says use combo box to populate unbound text boxes. I don't understand how typing in the Zip Code doesn't somehow require the ZipCodeID?

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.

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?


I really appreciate all your help. I'm sorry I'm so confused :(
 

plog

Banishment Pending
Local time
Today, 00:41
Joined
May 11, 2011
Messages
10,126
So, I'm not going to answer your questions because you need to be set in a direction. Here's what you need to do:

Start a new blank form, not based on any table.

Add a text input on it to enter a zip code.

Add 3 text inputs for City/County/State. These will not have any control source.

Add a button to it. I know you want to use an AfterUpdate, but this will be easier to debug for now.

Add code to the button to update the values of the City/County/State inputs based on the value in the input zip code.

Type in a zip, click that button and make those 3 fields populate.

Now make the above happen and post back here your issues or when you are succesful.
 

KathCobb

Registered User.
Local time
Yesterday, 22:41
Joined
Jun 29, 2012
Messages
46
Start a new blank form, not based on any table.

Add a text input on it to enter a zip code. <<---Doesn't this need a control source? Which I believe should be the actual ZipCode field in table zips?

Add 3 text inputs for City/County/State. These will not have any control source.
 

KathCobb

Registered User.
Local time
Yesterday, 22:41
Joined
Jun 29, 2012
Messages
46
Its really that first text box that is troubling for me. I have both of these websites that say start with a combo box and hide the ID field. I cannot figure out where or what to do without an ID. What am I missing form these steps that I can apply to having a text box instead of a combo box?

Alos, I am still stuck in the same place...am I building an expression or using VBA? Because what I typed in vba got an error.


 

plog

Banishment Pending
Local time
Today, 00:41
Joined
May 11, 2011
Messages
10,126
Add that form to a blank database, also include tblZips. Upload the database so I can see where you are at.
 

zeroaccess

Active member
Local time
Today, 00:41
Joined
Jan 30, 2020
Messages
608
Start a new blank form, not based on any table.

Add a text input on it to enter a zip code. <<---Doesn't this need a control source? Which I believe should be the actual ZipCode field in table zips?

Add 3 text inputs for City/County/State. These will not have any control source.
I would follow plog's instructions...but just for the record, the control source would be the table you're adding records to, not the Zip code table.

For now, keep it unbound.
 

strive4peace

AWF VIP
Local time
Today, 00:41
Joined
Apr 3, 2020
Messages
571
hi Kathy,

I have zip codes from the 1999 US census in a reference database you can download free. It has most of the zips you'd need, along with city, country, state, latitude, and longitude. It is also in another free download to manage contact information. Once you fill the zip, it fills other information. That you can get here:

MyContacts
msAccessGurus.com/tool/MyContacts.htm

look at f_Contact_ADDRESSes_pop
 

Users who are viewing this thread

Top Bottom