Combo box help

[Dave]

I am trying to create a customers data base where i select there city from a
combo box and from that selection the state combo box will only list the
states that city name is in. Then after selecting the city and state, my last
combo box will display the postcodes related to the first two selections.

I know this has been covered before by John Vinson (12/3/2004) , however
when i have copied the code i get an error message.

The code i used is as follows:

Private Sub cboCity_AfterUpdate()

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 the selected one
Me.cboZip = rs!Zip
Me.cboState = rs!State
Case Else
' If more than one zipcode, limit the combo
' to the selected city's zipcodes
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

When i write this code i get this message " Compile error: Expected: list
separator or )

And the following code highlighted red:

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

can anyboby help please
regards dave

 

[Ofer Cohen]

I noticed this points:
1. There is a dot in the end of the SQL
2. You didn't close the IIf statement, what about the else?
3. The line before last should have & before _

Try:
strSQL = "SELECT Zip, State FROM CONtblZip WHERE " & _
"ContblZip.City=" & Chr(34) & cboCity & Chr(34) & _
IIf(IsNull(Me.cboState), " ", Me.cboState) & " AND CONtblZip.State =
'" & _
Me.cboState & "') ORDER BY City"

 

[Dave]

Thankyou very much, your help is most apperiated.
However i now have another problem,
In my customers form, after i select a suburb from my combo. i recieve this
error message.
Error 3021 in cboSuburb_AfterUpdate: No Current Record

here is the code, with the changes you suggested... Thankyou again.

Private Sub cboSuburb_AfterUpdate()
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 postcodes
Me.cboPostcode.ControlSource = "SELECT Postcode FROM tblPostcodes ORDER BY
Postcode;"
' If a suburb is selected, limit the postcode to those in the selected
' suburb; if the suburb has only one postcode, just set it to that value
If Not IsNull(cboSuburb) Then
Set db = CurrentDb
strSQL = "SELECT Postcode, State FROM tblPostcodes WHERE " & _
"tblPostcodes.Suburb=" & Chr(34) & cboSuburb & Chr(34) & _
IIf(IsNull(Me.cboState), " ", Me.cboState) & " AND tblPostcodes.State =
'" _
& Me.cboState & "' ORDER BY Suburb;"
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot, dbReadOnly)
rs.MoveLast
iCount = rs.RecordCount
Select Case iCount
Case 0
' do nothing if this suburb isn't in the postcode table
Case 1
' If there's just one suburb/postcode, set postcode and state
' to the selected one
Me.cboPostcode = rs!Postcode
Me.cboState = rs!State
Case Else
' to the selected suburb's postcodes
Me.cboPostcode.ControlSource = strSQL
End Select
rs.Close
Set rs = Nothing
Set db = Nothing
End If

PROC_EXIT:
Exit Sub

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

End Sub