Scenario:
If I type in a city, how do I have the Zip Code automatically entered in the
appropriate field?
Which of the 200 or so zipcodes would you want entered if you type Los
Angeles? Many cities have multiple zipcodes.
If (as you should be) you are using a Form, you can use a Combo Box for both
the city and the zip code. You would need a zipcode table with fields for the
city, state and zip code, and VBA code in the AfterUpdate event of the City
combo box to set the rowsource of the Zip combo to the list of zipcodes for
that city (and, if you wish, vice versa - some zipcodes cover multiple
cities!).
Here's some code I use in my applications. It uses a zipcode table named
CONtblZip; you'll need to adapt it to your specific needs.
Private Sub cboCity_AfterUpdate()
' Comments :
' Parameters: -
' Copyright : 01/29/02 by John W. Vinson
' Offered for reuse provided this notice is kept intact
' --------------------------------------------------
On Error GoTo PROC_ERR
Dim rs As DAO.Recordset
Dim db As DAO.Database
Dim iCount As Integer
Dim strSQL As String
' Set default to all zipcodes
Me.cboZip.RowSource = "SELECT Zip FROM CONtblZip ORDER BY Zip;"
' If a city is selected, limit the Zip to those in the selected city;
' if the city has only one zip, just set it to that value
If Not IsNull(cboCity) Then
Set db = CurrentDb
strSQL = "SELECT Zip, State FROM CONtblZip WHERE " & _
"ContblZip.City=" & Chr(34) & cboCity & Chr(34) & _
IIf(IsNull(Me.cboState), " ", " AND CONtblZip.State = '" & _
Me.cboState & "'") & " ORDER BY City;"
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot, dbReadOnly)
rs.MoveLast
iCount = rs.RecordCount
Select Case iCount
Case 0
' do nothing if this city isn't in the ZIP table
Case 1
' If there's just one city/zip, set zip and state to selected one
Me.cboZip = rs!Zip
Me.cboState = rs!State
Case Else
' If more than one zipcode, limit the combo to the selected ones
Me.cboZip.RowSource = strSQL
End Select
rs.Close
Set rs = Nothing
Set db = Nothing
' set the RowSource of the Streets combo to this city's streets
strSQL = "SELECT Distinct Street FROM Streets" _
& " WHERE City = " & Chr(34) & Me.cboCity & Chr(34) _
& " AND State = '" & Me.cboState & "' ORDER BY Street;"
Me.cboStreet.RowSource = strSQL
' save the record to disk
Me.Dirty = False
End If
PROC_EXIT:
Exit Sub
PROC_ERR:
MsgBox "Error " & Err.Number & " in cboCity_AfterUpdate:" _
& vbCrLf & Err.Description
Resume PROC_EXIT
End Sub
