Class: ZipCode_DAO in Category Geospatial Mapping : Zip Codes from Total Visual SourceBook

Use DAO to lookup a zip code's corresponding city and state from a zip code table in VB6 or VBA.

FMS, in conjunction with the USPS, offers a monthly subscription to current US zip codes. See our Total ZipCode Database page for details.

Procedure Name Type Description
(Declarations) Declarations Declarations and private variables for the CZipCode_DAO class.
Class_Initialize Initialize Initialize files for zip code lookup by opening database directly to enable SEEK command.
GetLinkedPath Private Retrieve the linked path of a table in the current database.
Class_Terminate Terminate Close variables.
GetCityState Method Lookup city and state based on zip code.
VerifyCityZip Method Verify that the zip code, city, and state match.
' Example of CZipCode_DAO

' NOTE: The sample table for this form, tblZipCodes, comes the database named Sample.mdb in the directory where you installed sample files.
'       This sample includes a subset of Zip Codes from MD, VA, and DC.
'       FMS, in conjunction with the USPS, offers a monthly subscription to current US zip codes.
'       See http://www.fmsinc.com/MicrosoftAccess/ZipCodeDatabase.html for details.

' To try this example, do the following:
' 1. Import or link to the table named tblZipCodes, from C:\Total Visual SourceBook 2013\Samples\Sample.mdb
' 2. Create a new form
' 3. Add the following controls to the form:
'       Text box named txtCity
'       Text box named txtState
'       Text box named txtZip
'       Command button named cmdGetCityState
'       Command button named cmdValidate
' 4. Paste all the code from this example to the new form's module.
' 5. Run the form

Private mclsZipCodeDAO As CZipCode_DAO

Private Sub cmdGetCityState_Click()
  Dim strCity As String
  Dim strState As String

  If Nz(Me.txtZip, "") = "" Then
    Me.txtZip = "22182"
  End If

  If Not mclsZipCodeDAO.GetCityState(Me.txtZip, strCity, strState) Then
    MsgBox "City/State combo not found, or zip code not in the sample table." & vbCrLf & "Sample table includes zip codes from MD, VA, and DC." & vbCrLf & "Try zip code 22182."
  End If

  Me.txtCity = strCity
  Me.txtState = strState

End Sub

Private Sub cmdValidate_Click()

  If (Nz(Me.txtCity, "") = "") Or (Nz(Me.txtState, "") = "") Or (Nz(Me.txtZip, "") = "") Then
    MsgBox "Enter City/State/Zip combination to validate. Must be in VA, MD, or DC."
  Else
    Select Case Nz(Me.txtState)
      Case "DC", "MD", "VA"
        If mclsZipCodeDAO.VerifyCityZip(Me.txtZip, Me.txtCity, Me.txtState) Then
          MsgBox "This is a valid City/State/Zip combination."
        Else
          MsgBox "This is NOT a valid City/State/Zip combination, or the data does not exist in our sample table."
        End If

      Case Else
        MsgBox "Please use a City/State/Zip combination in MD, VA, or DC" & vbCrLf & "(e.g. Vienna, VA 22182)"
    End Select
  End If

End Sub

Private Sub Form_Load()
  ' Initialize the class
  Set mclsZipCodeDAO = New CZipCode_DAO

  ' Setup controls
  With Me.txtZip
    .Value = "22182"
    .Top = 200
    .Left = 1000
    .Width = 2000
  End With

  With Me.txtCity
    .Value = ""
    .Top = 800
    .Left = 1000
    .Width = 2000
  End With

  With Me.txtState
    .Value = ""
    .Top = 1200
    .Left = 1000
    .Width = 2000
  End With

  With Me.cmdGetCityState
    .Caption = "Get City/State"
    .Top = 200
    .Left = 3200
    .Width = 1500
  End With

  With Me.cmdValidate
    .Caption = "Validate City/State/Zip combo"
    .Top = 2000
    .Left = 500
    .Width = 3500
  End With

End Sub

Private Sub Form_Unload(Cancel As Integer)
  Set mclsZipCodeDAO = Nothing
End Sub

Total Visual SourceBook The source code in Total Visual Sourcebook includes modules and classes for Microsoft Access, Visual Basic 6 (VB6), and Visual Basic for Applications (VBA) developers. Easily add this professionally written, tested, and documented royalty-free code into your applications to simplify your application development efforts.

Total Visual SourceBook is written for the needs of a developer using a source code library covering the many challenges you face. Countless developers over the years have told us they learned some or much of their development skills and tricks from our code. You can too!

Additional Resources

Total Visual SourceBook CD and Printed Manual

Microsoft Access/ Office 2016, 2013, 2010, and 2007 Version
is Shipping!

New features in Total Visual SourceBook for Access, Office and VB6

Supports Access/Office 2016, 2013, 2010 and 2007, and Visual Basic 6.0!


View all FMS products for Microsoft Access All Our Microsoft Access Products

Reviews

Reader Choice Award for MS Access Source Code Library
Reader Choice

"The code is exactly how I would like to write code and the algorithms used are very efficient and well-documented."

Van T. Dinh, Microsoft MVP

SourceBook Info

Additional Info

Question

 

 

Free Product Catalog from FMS