Zip code back fills city and state

[kb1pwr]

Looking to use VBA code to back-fill city and state from a zip code. For the main table, I have peLoc1Zip (short text), peLoc1City, peLoc1State. All short text.
In the table 'zips' I have an autonumber, Zip, City, State - All are short text.
This is what I've tried so far:
=========================

 Private Sub peLoc1Zip_AfterUpdate()
 
       Zip = "'" & Me.peLoc1Zip & "'"
 
peLoc1City = DLookup("City", "Zips", "Zip = " & Me.peLoc1Zip)
 
peloc1State = DLookup("State", "Zips", "Zip = " & Me.peLoc1Zip)
 
End Sub

===============================
Second part of the problem is there are some zipcodes that have multiple towns. For instance, my Town (Southbridge) is also listed as Globe Village, Sandersdale, Southbridge, Southbridge Center, W Dudley, West Dudley (sorry just copy and pasted the results). To handle this, I would like to have a combo box populated with the results of the zipcode.

I'm at a complete loss and would appreciate some help. Thanks in advance!
Alan

[zmbd]

kb1pwr:
1)Please format you script using the [CODE/] button.

2)Respectfully, normally, only one topic per thread will be addressed, this is a site/thread requirement:

3)You've not told us what is wrong with your code, version of office/access

4)Normalizing your database will help you with both aspects of your question(s): > Database Normalization and Table Structures.

Because you've left some details out, such as (3), I'll offer you some advice to consider:

A properly normalized database will not store the same information in multiple tables unless there is a need for auditing or other special cases.

IMHO: What you should consider is the following database scheme:=

tbl_zipcode
[zipcode_pk] autonumber
[zipcode_main] text(20)

One might be tempted to use the zipcode as a primary key as it shouldn't be repeated. So long as you are dealing with the USA that will hold true for the forseeable; however, if you need to add Canada or other contries then you're in trouble as these are not numerics and numerics are easier and faster.

Tbl_State
[state_pk]autonumber
[state_fullname] text(25)
[state_abrv] text(5)

Should be obvious.

Tbl_City
[City_pk]autonumber
[City_name]text(50)

Now you can either use these as foriegn keys in an addressbook type table or do the following:

tbl_cityloc
[cityloc_pk] autonumber
[cityloc_FK_city] numeric(long)1:m with tbl_city
[cityloc_FK_state] numeric(long)1:m with tbl_state
[cityloc_FK_zipcode] numeric(long)1:m with tbl_zipcode

Now you would only use the [cityloc_pk] to refer say, Los Vegas, NV; or Greenville, AL; Greenville, AK; etc... instead of repeating the information a ton of times.

Once you have this tbl_cityloc, you can build a query that has that pulls the stuff from the related tables for a nice human readable text and yet the value of the control is just the [cityloc_pk]... you can also build cascading comboboxes and other filters that will make your searches easier.

Now this is just one way to do what I think you are after.

If you are a very new user to Access, VBA, database design, please let me know and I'll pm you a list of tutorials and references that will help you get out the kiddie pool and into the deepend in fairly quick time.

[NeoPa]

As Z says, focus on getting the design right and the rest falls neatly and easily into place.

Numerics are always good for a PK, but any short text that is demonstrably unique and doesn't exceed eight characters can be equally as good. In general, shorter (that take up less space in RAM) index values are better because more of them can fit into the same sized chunks of memory and so can be accessed without having to return to the (relatively much slower) Hard Disk Drive (HDD) to get them.