Not knowing how to program could you be specific. Thanks.
If I am entering a customer mailing list just for one
state only. Can the form be setup so when after the town
and then the state is select from a combo box list. Can
the zip code be automatically inputted into the zip code
field of the form?
No, it cannot in general, because many, many towns have multiple zip
codes.
Here's some VBA code I use on my sbfrmAddresses (a generic address
form I use in several applications). It does this, and if there are
multiple Zip codes for the city, resets the zip code combo box cboZip
to just the zipcodes for that city.
Private Sub cboCity_AfterUpdate()
' Comments :
' Parameters: -
' Modified : 01/29/02 by JWV
'
' --------------------------------------------------
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)
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 the selected one
Me.cboZip = rs!Zip
Me.cboState = rs!State
Case Else
' If more than one zipcode, limit the combo to the
' selected one
Me.cboZip.RowSource = strSQL
End Select
rs.Close
Set rs = Nothing
Set db = Nothing
End If
PROC_EXIT:
Exit Sub
PROC_ERR:
MsgBox "Error " & Err.Number & " in cboCity_AfterUpdate:" _
& vbCrLf & Err.Description
Resume PROC_EXIT
End Sub
