Access VBA

css navigation by Css3Menu.com

Address Lookup

Building a new database in which we must have County as that is the factor for determining Territory for a salesman. Got a list of zip codes and winnowed it down to the preferred names of the towns.

The user types the Zip Code and tabs away and this does the lookup. They can still type whatever they want for the City and County. We have done our part.

Private Sub PostalCode_LostFocus()
    Dim dB      As Database
    Dim RS      As Recordset
    Dim QUO     As String
    Dim SQLstr  As String
    Dim Zip     As String
' Info borrowed from http://federalgovernmentzipcodes.us -- updated often enough for me
    Set dB = CurrentDb()
    On Error Resume Next
    QUO = Chr(34)				'Set straight quotes as string
    If Left(Me.PostalCode, 5) Mod 1 = 0 Then	'Determine if PostalCode is numeric
   ' Debug.Print Me.PostalCode Mod 1
    SQLstr = "SELECT County,City,State,LocationText"		'Build QUERY string
    SQLstr = SQLstr & " FROM [County-zipcode]"
    SQLstr = SQLstr & " WHERE ((([County-zipcode].Zipcode) Like " _
	& QUO & Left(Me.PostalCode, 5) & "*" & QUO & " ));"
    Set RS = dB.OpenRecordset(SQLstr, dbOpenDynaset)	'Query in RecordSet
        RS.MoveFirst					'Get first
        Do Until RS.EOF				
            Me.County.Value = RS!County			'Set County in Address table
            Me.City.Value = RS!locationtext			'City
            Me.State.Value = RS!State			'State
        RS.MoveNext
        Loop
    RS.Close
    Set dB = Nothing
    Set RS = Nothing
    Else
        GoTo ErrHandle
    End If
    Exit Sub
ErrHandle:
    MsgBox "The Postal code is not numeric " & Err.Number & vbCrLf _
		& Err.Description, vbInformation
    Exit Sub
End Sub


The database I found has Latitude and Longitude. My customer does not care about that, but I have a DB of my own where it will be useful. The ’net is full of cool free stuff if you know how to search.

© 2011-2024

Updated:  01/23/2024 13:34
This page added:  09 June 2011