Q. Zipcode table. Can someone please explain this to me?

[Jim Jones]

Hi,

I came across this code, on the web, and don't know how to get in
touch with the author, but they posted it freely (as long as credit
remains in tact).

************* Code Start **************
' This code was originally written by Erika Yoxall.
' It is not to be altered or distributed,
' except as part of an application.
' You are free to use it in any application,
' provided the copyright notice is left unchanged.
'
' Code Courtesy of
' Erika Yoxall
'
Sub Zip_OnExit(Cancel As Integer)
Dim varState, varCity As Variant
varState = DLookup("State", "tblZipCode", "ZipCode =[Zip] ")
varCity = DLookup("City", "tblZipCode", "ZipCode =[Zip] ")
If (Not IsNull(varState)) Then Me![State] = varState
If (Not IsNull(varCity)) Then Me![City] = varCity
End Sub
'************* Code End **************

I want to use the above code, but don't understand 2 things;

1) Where do can I get a zip code table ?
2) Is "State" the field in the current form ? Or is [Zip] The
field in the current form ?

Thanks,
Jim

 

[fredg]

Hi,

I came across this code, on the web, and don't know how to get in
touch with the author, but they posted it freely (as long as credit
remains in tact).

************* Code Start **************
' This code was originally written by Erika Yoxall.
' It is not to be altered or distributed,
' except as part of an application.
' You are free to use it in any application,
' provided the copyright notice is left unchanged.
'
' Code Courtesy of
' Erika Yoxall
'
Sub Zip_OnExit(Cancel As Integer)
Dim varState, varCity As Variant
varState = DLookup("State", "tblZipCode", "ZipCode =[Zip] ")
varCity = DLookup("City", "tblZipCode", "ZipCode =[Zip] ")
If (Not IsNull(varState)) Then Me![State] = varState
If (Not IsNull(varCity)) Then Me![City] = varCity
End Sub
'************* Code End **************

I want to use the above code, but don't understand 2 things;

1) Where do can I get a zip code table ?
2) Is "State" the field in the current form ? Or is [Zip] The
field in the current form ?

Thanks,
Jim

1) You can purchase a full set of US Zip codes.
You may be able to find a free set somewhere.
Search the Internet.
If your needs are only for a small subset of Zip codes (let's say
only of your customers), simply make your own limited table.

2) From what I see of the above code, the form has a control named
[ZIP] in which the user enters a Zip code number, and the State and
City controls are supposed to be automatically filled in by looking up
the Sate and City in a table, using [ZIP] to find the proper code in
the [ZIPCODE] field.

There is a potential problem with this approach.
ZIP codes can include more than one city (mine for instance), so
incorrect results may occur. The DLookUp will always find the first
city that matches the criteria, not all the cities that match.