Auto Fill Form fields based on another field's value using a IIF

Auto Fill Form fields based on another field's value using a IIF

Post by Sm9obl » Mon, 01 Mar 2010 03:44:01


Hello once again...I'm back

I am trying to have the value for our Zipcode field to automatically be
added based on what city we select from our combo box for cities.

Zipcode field = Zipcode
City combobox field = lucity

I have tried using the IIF statement IIf([lucity]=Belton,[Zipcode]=29627,0)
placed in the properties of the Zipcode field in the form but it doesn't
work. I have about 12 cities to do this with.

Any suggestions? Thanks
--
JCW
 
 
 

Auto Fill Form fields based on another field's value using a IIF

Post by John W. Vi » Mon, 01 Mar 2010 05:48:02

n Sat, 27 Feb 2010 10:44:01 -0800, JohnW < XXXX@XXXXX.COM >
wrote:


One problem is that cities and zipcodes are emphatically NOT a one to one
match: knowing that an address is in Boston does not let you specify the zip
(it does narrow it down); for that matter, knowing that my zipcode is 83660
doesn't tell you if I live in Parma, Roswell or the Idaho portion of Adrian.

That said, here's some code that might help. You will of coruse need to adapt
the field, table and control names to your application.

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

Private Sub cboState_AfterUpdate()
' Comments :
' Parameters: -
' Modified : 01/29/02 by JWV
'
' --------------------------------------------------
On Error GoTo PROC_ERR


If IsNull(cboState) Then
' State has been cleared, set Zip and City combos to all
Me.cboCity.RowSource = "SELECT DISTINCT City, State FROM CONtblZip
ORDER BY City;"
Me.cboZip.RowSource = "SELECT Zip FROM CONtblZip ORDER BY Zip;"
Else
Me.cboCity.RowSource = "SELECT DISTINCT City, State FROM CONtblZip
WHERE CONtblZip.State = '" & cboState & "' ORDER BY City;"
Me.cboZip.RowSource = "SELECT Zip FROM CONtblZip WHERE CONtblZip.State
= '" & cboState & "' ORDER BY Zip;"
End If

PROC_EXIT:
Exit Sub

PROC_ERR:
MsgBox "Error " & Err.Number & " in cboState_AfterUpdate:" _
& vbCrLf & Err.Description
Resume PROC_EXIT


End Sub

Private Sub cboZip_AfterUpdate()
' Comments :
' Parameters: -
' Modified : 01/29/02 by JWV
'
' --------------------------------------------------
On Error GoTo PROC_ERR


Dim rs As DAO.Recordset
Dim db As DAO.Database