I need a Module that auto completes city and state by entering the zip 1

[RobertBlackwell]

Can you guys recommend something like this for me?

Maybe even a LE edition so I can demo it and see how it integrates.

Thanks
Robbie

 

[CosmoKramer]

Check out the following web site where you can download the data to create your own lookup tables to accomplish this:

http://www.census.gov/geo/www/gazetteer/places.html

 

[MichaelRed]

Unless you have mis-stated the issue or I am mis-reading it, you cannot go from City/state to Zip code. this is a one to many relationship, so the best you could get is a restricted sub-set of possible zip codes. You cannot even (completly) go the other direction, as numerous Zip codes cross political jurisdiction boundaries - although you get a much smaller set of choices going from Zip to political jurisdiction.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[ToeShot]

I have a piece of Code that does this. If you still need it Email me

ToeShot@Hotmail.com

 

[jfhewitt]

If what you want is to enter the zip, and have the city and state autofill, create a three field table containing, zip, city, state. create a query joining the zip fields of your address and zip tables. In the query, take city and state from the joined zip table.

 

[BoxHead]

As Michael points out, there are instances of multiple cities using the same zip code. However, the post office does distinguish one city as the primary selection for each zip.

You will need to deal with this by having fields for secondary cities and you might want to consider a popup that asks the user to choose when there are multiple choices of City.

If you're going to incorporate the zip code database in your app, you might also want to verify addresses by checking for matches on the afterUpdate of the city and state fields.

 

[JohnYingling]

If you can stand a 650K character (1.3mb) string containing ALL the zips and City State, try this. It is VB6 with a test form and a class. I use it for City, State fill in.
Download the Zips.txt at

http://www.census.gov/geo/www/gazetteer/places.html

Unzip to App.Path & "\Zips.txt"
The class Opens the Zips.txt and constructs a long string of
,<ARLINGTON,VA>,22201,22202,22203,22204,22205,22206,22207.
It uses Instr to find the Zip and InstrRev to fback up to the < that marks the city. On a 667MZ HP in VB IDE mode, it takes 13 seconds to locate the last zip code, 84127, 1000 times i.e. .013 seconds for longest look up in IDE mode.

VERSION 5.00
Begin VB.Form frmZipCode 
   Caption         =   "Zip Codes"
   ClientHeight    =   3195
   ClientLeft      =   60
   ClientTop       =   345
   ClientWidth     =   7185
   LinkTopic       =   "Form1"
   ScaleHeight     =   3195
   ScaleWidth      =   7185
   StartUpPosition =   3  'Windows Default
   Begin VB.TextBox txtZipcode 
      Height          =   330
      Left            =   210
      MaxLength       =   5
      TabIndex        =   4
      Top             =   2010
      Width           =   1725
   End
   Begin VB.CommandButton cmdLookUp 
      Caption         =   "LookUp"
      Height          =   420
      Left            =   210
      TabIndex        =   2
      Top             =   1500
      Width           =   1920
   End
   Begin VB.CommandButton cmdOpenZipcode 
      Caption         =   "Open Zip Code"
      Height          =   420
      Left            =   210
      TabIndex        =   0
      Top             =   285
      Width           =   1920
   End
   Begin VB.Label lblCityState 
      Height          =   360
      Left            =   2280
      TabIndex        =   3
      Top             =   1545
      Width           =   4770
   End
   Begin VB.Label lblError 
      Height          =   870
      Left            =   2340
      TabIndex        =   1
      Top             =   330
      Width           =   4665
   End
End
Attribute VB_Name = "frmZipCode"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Option Explicit
Private objZip As New clsZipcode

Private Sub cmdLookUp_Click()
    Dim strCityState As String
    Dim I As Long
    Debug.Print Now & " start"
    For I = 0 To 1000
        strCityState = objZip.Lookup(txtZipcode.Text)
    Next
    Debug.Print Now & " End"
    lblCityState = strCityState
End Sub

Private Sub cmdOpenZipcode_Click()
    On Error Resume Next
        objZip.OpenZip App.Path & "\zips.txt"
        lblError = Err.Description
    On Error GoTo 0
End Sub

Private Sub Form_Load()
    cmdLookUp.Enabled = False
End Sub

Private Sub txtZipcode_Change()
    cmdLookUp.Enabled = Len(txtZipcode.Text) = 5
End Sub
VERSION 1.0 CLASS
BEGIN
  MultiUse = -1  'True
  Persistable = 0  'NotPersistable
  DataBindingBehavior = 0  'vbNone
  DataSourceBehavior  = 0  'vbNone
  MTSTransactionMode  = 0  'NotAnMTSObject
END
Attribute VB_Name = "clsZipcode"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = True
Attribute VB_PredeclaredId = False
Attribute VB_Exposed = False
Option Explicit
Private mstrCityStateZip As String
Sub OpenZip(strFileName As String)
    Dim lngHandle       As Long
    Dim strResponse     As String
    Dim strWhile        As String
    Dim strFips         As String
    Dim strZipcode      As String
    Dim strState        As String
    Dim strCity         As String
    Dim strLongitude    As String
    Dim strLatitude     As String
    Dim strPopulation   As String
    Dim strAllocation   As String
    Dim strCityPrev     As String
    Dim strStatePrev    As String
    Dim lngIndex        As Long
    Dim lngLenCS        As Long
    Dim aryCityStateZip()   As String
    ReDim aryCityStateZip(0)
    lngIndex = 0
    Do
        lngHandle = FreeFile
        strWhile = " Opening"
        On Error Resume Next
            Open strFileName For Input Access Read As lngHandle
            strResponse = Err.Description
        On Error GoTo 0
        If Len(strResponse) > 0 Then Exit Do
        
        Do While (Not EOF(lngHandle))
            strWhile = "Reading"
            On Error Resume Next
                Input #lngHandle, strFips, strZipcode, strState, strCity, _
                            strLongitude, strLatitude, _
                            strPopulation, strAllocation
                strResponse = Err.Description
            On Error GoTo 0
            If Len(strResponse) > 0 Then Exit Do
            If strState <> strStatePrev Or strState <> strStatePrev Then
                lngIndex = lngIndex + 1
                If lngIndex > UBound(aryCityStateZip) Then
                    ReDim Preserve aryCityStateZip(lngIndex * 2)
                End If
                aryCityStateZip(lngIndex) = &quot;<&quot; & strCity & &quot;,&quot; & strState & &quot;>,&quot; & strZipcode
            Else
                aryCityStateZip(lngIndex) = aryCityStateZip(lngIndex) & &quot;,&quot; & strZipcode
            End If
            strCityPrev = strCity
            strStatePrev = strStatePrev
        Loop
        If Len(strResponse) > 0 Then Exit Do
    
    Exit Do: Loop
    
    ReDim Preserve aryCityStateZip(lngIndex)
    On Error Resume Next
        Close lngHandle
    On Error GoTo 0
    If Len(strResponse) > 0 Then
        strResponse = strResponse & vbCrLf & _
            &quot;while &quot; & strWhile & vbCrLf & _
            strFileName
        Err.Raise vbObjectError + 513, &quot;clsZipcode&quot;, strResponse
    End If
    mstrCityStateZip = Join(aryCityStateZip, &quot;,&quot;)
    lngLenCS = Len(mstrCityStateZip)
End Sub

Public Function Lookup(strZipcode As String) As String
    Dim I As Long
    Dim J As Long
    I = InStr(1, mstrCityStateZip, strZipcode)
    If I = 0 Then Exit Function
    
    I = InStrRev(mstrCityStateZip, &quot;<&quot;, I)
    If I = 0 Then Exit Function
    J = InStr(I, mstrCityStateZip, &quot;>&quot;)
    Lookup = Mid$(mstrCityStateZip, I + 1, J - I - 1)
    
End Function

http://www.VBCompare.com

http://www.VBSortGen.com

 

[morrisdev]

I use a table of all Zipcodes, Cities and States. I just run a lookup on the Zipcode table upon exit of the field. If you want the table, email me and I'll send it to you.

-dan morris
mailto:dmorris@morrisdev.com