Multi-value Listbox Filter on Split Form (1 Viewer)

JustinS

Member
Local time
Today, 19:05
Joined
Apr 11, 2020
Messages
58
I am trying to filter a split form using a multi-value list box. It is necessary to have the ability to include multiple values in the "Base Model" filter, but I want to see everything when there is no selection made. I have used the following code:

Code:
     Dim strWhere As String
     Dim ctl As Control
     Dim varItem As Variant


     If Me.lstBaseModel.ItemsSelected.Count = 0 Then
       MsgBox "Must select at least 1 employee"  'I'm really not using this part because it doesn't really serve my application
       Exit Sub
     End If


     Set ctl = Me.lstBaseModel
     For Each varItem In ctl.ItemsSelected
       strWhere = strWhere & ctl.ItemData(varItem) & ","

     Next varItem

     strWhere = Left(strWhere, Len(strWhere) - 1)

     strWhere = "[Base Model] IN( " & strWhere & ")"

I found this code on @pbaldy website. Code works great, however, the problem that I am having is when I clear the selections. I use the following code to clear the listbox selections:

Code:
       Dim varItm As Variant

       For Each varItm In Me.lstBaseModel.ItemsSelected
           Me.lstBaseModel.Selected(varItm) = False

This code also does what it is intended to do, it removes all the selections. It does not remove the filter from the column that the multi-value list box was filtering. Additionally I cannot use Me.FilterOn=False to remove the filter because this is one of many filters that must work to together. Also, when I use the Me.FilterOn=False technique it turns all the filters off until the form is reloaded.



Filters - Base.PNG


Does anyone have an idea how to fix this?
 
Last edited:

CJ_London

Super Moderator
Staff member
Local time
Today, 23:05
Joined
Feb 19, 2013
Messages
16,553
One way would be to keep the filter string value - perhaps as a string declared at the top of your form module, then as part of the code that clears the listbox have something like

me.filter=replace(me.filter,lastlistboxfilter,"")
me.filteron=true
 

theDBguy

I’m here to help
Staff member
Local time
Today, 16:05
Joined
Oct 29, 2018
Messages
21,358
Hi. Depending on how you built the entire filter in the first place, why not just build the whole thing again but skipping the listbox this time?
 

JustinS

Member
Local time
Today, 19:05
Joined
Apr 11, 2020
Messages
58
@theDBguy Could I use an if statement to specify the filter, such as:


Code:
If Me.lstBaseModel.ItemsSelected.Count = 0 Then
    Me.Filter= 'Everything but strWhere
Else if
     Me.lstBaseModel.ItemsSelected.Count > 0 Then
     Me.Fiter= 'Everything including strWhere
End if

Me.FilterOn=True

Would that work?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 16:05
Joined
Oct 29, 2018
Messages
21,358
@theDBguy Could I use an if statement to specify the filter, such as:


Code:
If Me.lstBaseModel.ItemsSelected.Count = 0 Then
    Me.Filter= 'Everything but strWhere
Else if
     Me.lstBaseModel.ItemsSelected.Count > 0 Then
     Me.Fiter= 'Everything including strWhere
End if

Me.FilterOn=True

Would that work?
Hi Justin. You could try and let us know. But, this is what I usually have. Let's say the user can use one textbox, one combobox, and one multi-select listbox to filter the form. The button code to apply the filter or do the search might look something like this:
Code:
Dim var As Variant
Dim strWhere As String
Dim strIn As String

strWhere = ""
strIn = ""

If Me.Textbox > "" Then
    strWhere = strWhere & " AND FieldName='" & Me.Textbox & "'"
End If

If Me.Combobox > "" Then
    strWhere = strWhere & " AND FieldName=" & Me.Combobox
End If

For Each var In Me.Listbox.ItemsSelected
    strIn = strIn & Me.Listbox.ItemData(var) & ","
Next

If strIn <> "" Then strWhere = strWhere & " AND FieldName In(" & strIn & ")"
If strWhere <> "" Then strWhere = Mid(strWhere,6)

Me.Filter = strWhere
Me.FilterOn = True
Hope that helps...
 
Last edited:

JustinS

Member
Local time
Today, 19:05
Joined
Apr 11, 2020
Messages
58
Just wanted to update. I think I got this worked out. Here is what I did:

Code:
Private Sub AddFilter()
TempVars("BaseMode")= "1=1"

Dim strWhere As String
Dim ctl As Control
Dim varItem As Variant

strWhere = ""


 Set ctl = Me.txtBaseModel
     For Each varItem In ctl.ItemsSelected
       strWhere = strWhere & ctl.ItemData(varItem) & ","
     Next varItem
          
    If strWhere <> "" Then
         strWhere = Left(strWhere, Len(strWhere) - 1)
    End If

     If strWhere <> "" Then
          strWhere = "[Base Model] IN( " & strWhere & ")"
     Else If
     strWhere = "" Then
     strWhere = TempVars("BaseModel")  'This allowed me to clear the multi-value list box without clearing all filters, learned this from @thedbguy on a previous question'
     End If

Me.Filter=strWhere & " AND "  'a lot of other stuff'
Me.FilterOn=True
End Sub
So it is kind of a mash-up between several things I've seen on here, but hey it takes a village right? Thank you guys for your help on this. I look forward to learning more soon.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 16:05
Joined
Oct 29, 2018
Messages
21,358
Just wanted to update. I think I got this worked out. Here is what I did:

Code:
Private Sub AddFilter()
TempVars("BaseMode")= "1=1"

Dim strWhere As String
Dim ctl As Control
Dim varItem As Variant

strWhere = ""


Set ctl = Me.txtBaseModel
     For Each varItem In ctl.ItemsSelected
       strWhere = strWhere & ctl.ItemData(varItem) & ","
     Next varItem
         
    If strWhere <> "" Then
         strWhere = Left(strWhere, Len(strWhere) - 1)
    End If

     If strWhere <> "" Then
          strWhere = "[Base Model] IN( " & strWhere & ")"
     Else If
     strWhere = "" Then
     strWhere = TempVars("BaseModel")  'This allowed me to clear the multi-value list box without clearing all filters, learned this from @thedbguy on a previous question'
     End If

Me.Filter=strWhere & " AND "  'a lot of other stuff'
Me.FilterOn=True
End Sub
So it is kind of a mash-up between several things I've seen on here, but hey it takes a village right? Thank you guys for your help on this. I look forward to learning more soon.
Hi Justin. Congratulations! Glad to hear you got it sorted out. Good luck with your project.
 

Users who are viewing this thread

Top Bottom