anyone have an example of how to layout the tables for a zip code autofill look up?

[CFP - Jason]

anyone have an example of how to layout the tables for a zip code autofill look up? i am trying to get this to work and in the ones i found in the search, they werent straight forward enough. If anyone has a method or example, that would be much appreciated.
thanks
jason

 

[IgorB]

Re: anyone have an example of how to layout the tables for a zip code autofill look up?

00000-9999;0;_

 

[CFP - Jason]

actually i was looking for a working sample database. but thanks tho

JAson

 

[Pat Hartman]

Base your form on a query that joins the Address table to the Zip code table. That way fields from both tables are available in the form's recordsource. When you choose a zip code either by typing it in or by selecting from a combo or list, Access autonmatically fills in the city and state.

tblAddress:
AddressID (autonumber pk)
ZipCode (fk to zip table)
etc.

tblZip
ZipCode (pk)
City
State

 

[CFP - Jason]

Pat - ok right now i have a table called ZIPCodes. It has like 71000 entrys in it for every zip in the US. Now i wasnt sure what u ment by "fk", and i am not too sure how to make it look up tha values. I am really new at this so the more basic the answer the easier for me to understand.

From what i read, i need to make a table address with 2 fields and a second table with zip city state and county. Then i dont know what to do fromt there or if i understood that right.
Thanks
Jason

 

[Pat Hartman]

Your address table needs all the additional address fields. That's what the etc. was for. Also, sometimes addresses are stored in a table with other information. That happens when only a single address needs to be stored for an entity.

Once you have created both the table to store the address and the table to store the zipcodes, make sure their primary keys are defined as I said. Then open the relationships window - the icon that looks like three squares and connect the ZipCode field in one table to the ZipCode field in the other table. Check the box that says "Enforce Referential Integrity". That establishes the relationship between the two tables and makes the ZipCode field in the Address table a "foreign key (fk)" to the ZipCode table.

 

[CFP - Jason]

hey i figured it out some wierd different way, but thanks soo much tho
Jason

 

[CoddFish]

I have attached a sample database. If you open tblLocation, you will notice that the ZipCode field has a drop-down list that contains a bunch of ZipCodes (these should autofill if you type in the field). I have populated this list by using the Lookup feature on the ZipCode field in tblLocation. This field uses tlbZipCodeList.ZipCode as its row source.

Hope this is what you were looking for...


-CoddFish