Combo box results populating a list box

[relax4delicutfew]

The problem I have is that I currently have two combo boxes that produce results to a list box, however both need to be used inorder for the results to show. I need to add more boxes, but be able to get data without having to select every box.

Any help would be greatly appreciated.

Private Const strSQL1 = "SELECT CaseID, Milepost, Purpose, GuardrailType, TerminalType, HardwareComments " & _
   "FROM qryCombo1 WHERE RouteNumber = '"
Private Const strSQL2 = "'AND PostedSpeed = "
Private Const strSQL3 = " ORDER by Milepost ;"
Private strSQL As String

Private Sub cboRoute_AfterUpdate()
   If Me!cboRoute.Value > 0 Then
      Call FillList
   End If
End Sub

Private Sub cboSpeed_AfterUpdate()
   If Me!cboSpeed.Value > 0 Then
      Call FillList
   End If
End Sub

Private Sub FillList()
   strSQL = strSQL1 & Me!cboRoute.Value & strSQL2 & Me!cboSpeed.Value & strSQL3
   Me!lstCrashes.RowSource = strSQL
   Me!lstCrashes.Requery
   
End Sub

Private Sub Form_Activate()
   If Me!cboRoute.Value <> "" Then
      Call FillList
   Else
   If Me!cboSpeed.Value > 0 Then
      Call FillList
   End If
   End If
End Sub

 

[PHV]

Take a look at the Like operator and the IIf function.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[relax4delicutfew]

The Like operator worked well in sorting the data from the combo boxes, but I still cant seem to make them work independent, and I am not sure where the apply the IIF function.

 

[PHV]

Something like:
"' AND PostedSpeed=IIf(Len('" & Nz(Me!cboSpeed,"") & "')>0," & Nz(Me!cboSpeed,0) & ",PostedSpeed)"
And for the Like:
"WHERE RouteNumber Like '" & Nz(Me!cboRoute,"*") & "'"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244