Listbox filters

[cbende2]

Hello all,



I'm wondering if there is a more efficient way of doing the process I'm doing.

As of now, I have 2 list boxes filtering two fields on my form. They can work independently or together to narrow down even further.

I'm hoping to add a third list box to filter another field, however it seems like I would have to create several more if statements if I continue down this route.

Private Sub Command2_Click()

   Dim Criteria As String
   Dim Criteria2 As String
   Dim i As Variant


   ' Build criteria string from selected items in list box.
   Criteria = ""
   Criteria2 = ""
   For Each i In Me!
[List1].ItemsSelected
      If Criteria <> "" Then
         Criteria = Criteria & " OR "
      End If
      Criteria = Criteria & "[PlanStatusID]=" _
       & Me!
[List1].ItemData(i)
   Next i
   
   For Each i In Me!
[List0].ItemsSelected
      If Criteria2 <> "" Then
         Criteria2 = Criteria2 & " OR "
      End If
      Criteria2 = Criteria2 & "[PlanNameID]=" _
       & Me!
[List0].ItemData(i)
   Next i
   ' Filter the form using selected items in the list box.
   
   If Criteria = "" Then
        Me.Filter = Criteria2
        Me.FilterOn = True
   End If
   If Criteria2 = "" Then
        Me.Filter = Criteria
        Me.FilterOn = True
   End If
   If Criteria <> "" And Criteria2 <> "" Then
        Me.Filter = Criteria & "AND" & Criteria2
        Me.FilterOn = True
   End If
End Sub

[June7]

Probably.

Review:

http://www.allenbrowne.com/ser-62.html

and

http://allenbrowne.com/ser-50.html

[cbende2]

Thanks June, I'll give it a look. It appears like its only for using one list box, but I'll dive in and get back at you if I need any help with adding on another list box.

[June7]

Use as many controls as you want, including multiple listboxes. Just means more conditional and concatenation code.

[Micron]

I've done this by looping through the items selected for each list and adding them to a comma separated array to build a WHERE clause. Naturally, you need to test for the listbox itemsselected property not being null (or "", I forget) before running an event such as a button click or listbox double click and decide how to handle that. Consider passing the itemsselected to a function that returns the array so you don't have to repeat the code for every listbox. After the last listbox is evaluated, add the semicolon to the clause and append that to the sql string. Then run the sql.