Hi all
I have a list box set to allow multiple selections. This list box is on a split form. It's values are from another table. I'm trying to get this list box to filter the data sheet based on whatever the user selects. I was thinking of doing this where the user makes the selection(s) and then pressing a button which would filter the datasheet. The code I have below is from a form that opens up a report based on whatever selections made but with some changes since this should filter a datasheet and not open a report:
Code:
Private Sub cmdCodeFilter_Click()
Dim strWhere As String
Dim ctl As Control
Dim varItem As Variant
'make sure a selection has been made
If Me.lstProgramCodes.ItemsSelected.Count = 0 Then
MsgBox "Must select at least 1 program code"
Exit Sub
End If
'add selected values to string
Set ctl = Me.lstProgramCodes
For Each varItem In ctl.ItemsSelected
strWhere = strWhere & "'" & ctl.ItemData(varItem) & "',"
Next varItem
'trim trailing comma
strWhere = Left(strWhere, Len(strWhere) - 1)
'Debug.Print strWhere
'filter datasheet
Me.Filter = "ProgramCode IN(" & strWhere & ")"
Me.FilterOn = True
End Sub
Similar idea, however I'm not sure how the code at the bottom is supposed to look. What comes up is this whenever I press the button:
Any help is greatly appreciated!