|
Quick Tip Details |
Question:
Automatically Fill City, State and County based on Zip (postal) Code. |
Answer:
A zip code lookup function is a great way to enhance a user interface.
Create a table to store the data.
tblZipCode
-ZipCodeID (AutoNumber - PK)
-ZipCode (text, 5)
-State (text,2)
-City (text, 50)
-County (text, 50)
Use the Dlookup Function and to retrieve the additional values on the AfterUpdate event of the zip code control.
IMPORTAT: Zip codes are NOT unique. Cities can have multiple zip codes, cross multiple counties. Also the same zip code can be used for different cities. This will need to be addressed within the application.
FMS, Inc. offers a complete USPS fisting with an example application. I would definably suggest obtaining this product; it is not expensive, saves time and adds value to the application.
|
Code:Private Sub ZipCode_AfterUpdate()
Me.City = DLookup("City", "tblZipCode", "ZipCode='" & Me.ZipCode & "'")
Me.State = DLookup("State", "tblZipCode", "ZipCode='" & Me.ZipCode & "'")
Me.County = DLookup("County", "tblZipCode", "ZipCode='" & Me.ZipCode & "'")
End Sub |
|
