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:
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:
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.
Does anyone have an idea how to fix this?
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.
Does anyone have an idea how to fix this?
Last edited: