independent combo boxes

[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

 

[Tranman]

Hi,
Fascinating problem...it sounds like we may be in the same business--I'm the programming mgr for geometric and accident data in Kansas.

Anyway, here's a snippet. I named all of the combo boxes either txt<something> or nbr<something> in order to tell the routine whether the value in the combo needed single quotes in the where clause. The remainder of the name is the field name that you're applying the criterion to. In other words, if you're wanting to say "where XYZ = 'passing'", the combo is named txtXYZ. The routine strips off the "txt", and builds the rest of the expression.
Nbr works the same way, but without the single quotes. The blnFirstWhere is so you can run the sub without *any* combo having data.

The sub has to be run by a command button, because you don't know when you're through filling combo boxes otherwise.

Here's the code. Let me know if it works.

Private Sub Command32_Click()
Dim blnFirstWhere As Boolean
Dim ctrl As Control
Const strSQL1 As String = "SELECT CaseID, Milepost, Purpose, GuardrailType, TerminalType, HardwareComments " & _
"FROM qryCombo1"
Const strSQL3 = " ORDER by Milepost;"
Dim strSQL As String
Dim strWhereClause As String
Dim strWork As String

blnFirstWhere = True

For Each ctrl In Me.Controls
strWork = ""
If ctrl.ControlType = acComboBox Then
Select Case IsNull(ctrl.Value)
Case True
Case Else
If blnFirstWhere Then
strWhereClause = " WHERE "
Else
strWork = " AND "
End If
Select Case UCase(Left(ctrl.Name, 3))
Case "TXT"
strWork = strWork & Right(ctrl.Name, Len(ctrl.Name) - 3) & " = '" & ctrl.Value & "'"
Case "NBR"
strWork = strWork & Right(ctrl.Name, Len(ctrl.Name) - 3) & " = " & ctrl.Value
End Select
strWhereClause = strWhereClause & strWork
blnFirstWhere = False
End Select
End If
Next
strSQL = strSQL1 & strWhereClause & strSQL3
End Sub

Tranman

 

[willir]

A simple approach would be to "hide" the second combo box until the first combo box has valid data.

Richard