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.
|