How do I filter a Listbox with multiple comboboxes?

[Jeroen3131]

I have a Form with four comboboxes (cboSelectDepartment, cboSelectOperation, cboSelectModel and cboSelectVariant), a Listbox (selectionList) and a Button (cmdSelectStep).

The Listbox displays a StepID (number) and a StepName (text).I want to filter the Listbox based upon the values in the comboboxes. After each combobox selection the List becomes shorter and shorter.

Next, the user selects a StepID number from the Listbox and presses the Button to set a Temporary Variable (StepRecord).

To make things complicated: The combobox cboSelectOperation is Cascaded from cboSelectDepartment and cboSelectVariant is Cascaded from cboSelectModel. So I the user selects a Department, the Operation combobox list is limited, the same with Model and Variants.

I've found a tutorial database ("Cascading Combo Boxes") where all the comboboxes are cascaded. Next, I've copied the code and adapted it to my database. But now I'm stuck with the SQL code for filtering (see Private Sub filterList()). This is the code I have now:

Option Compare Database
Option Explicit
 
Private Sub filterList()
    Dim strRS As String
 
  ' Filter the list box appropriately based on the combo box selection(s)
  strRS = "SELECT qryFilterList.StepID, qryFilterList.StepName FROM qryFilterList"
 
  If Not IsNull(Me.cboSelectVariant) Then
    strRS = strRS & " WHERE VariantID = " & Me.cboSelectVariant
  ElseIf Not IsNull(Me.cboSelectModel) Then
    strRS = strRS & " WHERE ModelID = " & Me.cboSelectModel
  ElseIf Not IsNull(Me.cboSelectOperation) Then
    strRS = strRS & " WHERE OperationID = " & Me.cboSelectOperation
  ElseIf Not IsNull(Me.cboSelectDepartment) Then
    strRS = strRS & " WHERE DeptID = " & Me.cboSelectDepartment
  End If
 
  strRS = strRS & " ORDER BY qryFilterList.StepName;"
  Me.selectionList.RowSource = strRS
  Me.selectionList.Requery
End Sub
 
Private Sub cboSelectDepartment_AfterUpdate()
 
  Me.cboSelectOperation.RowSource = "SELECT tblOperations.OperationID,tblOperations.Operation, tblOperations.Description FROM tblOperations " & _
     " WHERE DeptID = " & Nz(Me.cboSelectDepartment) & _
     " ORDER BY Operation"
  Me.cboSelectOperation = Null
  filterList
End Sub
 
Private Sub cboSelectModel_AfterUpdate()
 
  Me.cboSelectVariant.RowSource = "SELECT qryVariant.variantID, qryVariant.Variant FROM qryVariant " & _
     " WHERE ModelID = " & Nz(Me.cboSelectModel) & _
     " ORDER BY Variant"
  Me.cboSelectVariant = Null
  filterList
End Sub
 
Private Sub cboSelectOperation_AfterUpdate()
  filterList
End Sub
 
Private Sub cboSelectVariant_AfterUpdate()
  filterList
End Sub
 
Private Sub Form_Load()
  filterList
  Me.selectionList.RowSource = ""
End Sub
 
Private Sub EnableControls()
 
  ' Clear the combo boxes
  If IsNull(Me.cboSelectDepartment) Then
     Me.cboSelectOperation = Null
  End If
 
  If IsNull(Me.cboSelectOperation) Then
    Me.cboSelectModel = Null
  End If
 
  If IsNull(Me.cboSelectModel) Then
    Me.cboSelectVariant = Null
  End If
 
  ' Enable or disable combo boxes based on whether the combo box preceeding it has a value.
  Me.cboSelectOperation.Enabled = (Not IsNull(Me.cboSelectDepartment))
  Me.cboSelectModel.Enabled = (Not IsNull(Me.cboSelectOperation))
  Me.cboVariant.Enabled = (Not IsNull(Me.cboSelectModel))
End Sub

This code partially works! If I select from the Department and Operation comboboxes the List is filtered properly. But when I select a Model, the Department and Operation filter is removed/ignored.

I think I need to put a AND operator in the SQL part to combine all the filters. Unfortunately I don't have any experience with creating a SQL code. Can yo guys help me or point me in the right direction?

Thanks!

[MikeTheBike]

Hi

This is completly air code but should be somewhere near

Private Sub filterList()
    Dim strRS As String
 
    ' Filter the list box appropriately based on the combo box selection(s)
    If Me.cboSelectVariant.ListIndex >= 0 Then strRS = strRS & " AND VariantID = " & Me.cboSelectVariant
 
    If Me.cboSelectModel.ListIndex >= 0 Then strRS = strRS & " AND ModelID = " & Me.cboSelectModel
 
    If Me.cboSelectOperation.ListIndex >= 0 Then strRS = strRS & " AND OperationID = " & Me.cboSelectOperation
 
    If Me.cboSelectDepartment.ListIndex >= 0 Then strRS = strRS & " AND DeptID = " & Me.cboSelectDepartment
 
 
    'IF FILTER FOUND ADD 'WHERE' AND REMOVE LEADING 'AND' (IF NOT FOUND IT WILL REMAIN A NULL STRING)
    If strRS <> "" Then strRS = " WHERE " & Mid(strRS, 6)
 
    'ADD FILTER TO QUERY
    strRS = "SELECT qryFilterList.StepID, qryFilterList.StepName FROM qryFilterList " & strRS
 
    'ADD ORDER BY CLAUSE
    strRS = strRS & " ORDER BY qryFilterList.StepName;"
 
 
    Me.selectionList.RowSource = strRS
    Me.selectionList.Requery
End Sub

I have used the 'ListIndex' property, which is -1 if nothing is selected, instead of Not IsNull()

It also assumes all criteria are numeric.

HTH


MTB

[Jeroen3131]

Hey Mike,

The code works Flawless! It's exactly what I wanted!

Thank you.