VBA-Apply Filter to Search Button (1 Viewer)

VBABeginner3

Registered User.
Local time
Today, 02:31
Joined
Apr 16, 2017
Messages
11
I'm having trouble getting this code to work. I have a blank form with a simple multi-select list box (GroupList) and a button underneath it (cmdSearch). I'm trying to put code into the button to pull select names from GroupList and display Group Affiliation names (field: [Group Affiliations] from table Group_Affiliations. Please help. I think I'm close but it wants me to set a parameter and then I receive an error code 2501.

Private Sub cmdSearch_Click()
Dim varItem As Variant
Dim strSearch As String
Dim Task As String

For Each varItem In Me!GroupList.ItemsSelected
strSearch = strSearch & "," & Me!GroupList.ItemData(varItem)
Next varItem
MsgBox (strSearch)
If Len(strSearch) = 0 Then
Task = "select * from Group_Affiliations"
Else
strSearch = Right(strSearch, Len(strSearch) - 1)
Task = "select * from Group_Affiliations where ([Group Affiliations] in (" & strSearch & "))"
End If
DoCmd.ApplyFilter Task
End Sub
 

MarkK

bit cruncher
Local time
Yesterday, 23:31
Joined
Mar 17, 2004
Messages
8,178
You might want to use the filter property of the form, and then you can just set it to the text of your where clause (without the word WHERE). Maybe this would work...
Code:
Private Sub cmdSearch_Click()
    Dim varItem As Variant
    Dim strSearch As String
    
    For Each varItem In Me!GroupList.ItemsSelected
        strSearch = strSearch & "," & Me!GroupList.ItemData(varItem)
    Next
    
    If Len(strSearch) Then
        strSearch = Mid(strSearch, 2)
        Me.Filter = "[Group Affiliations] in (" & strSearch & ")"
        Me.FilterOn = True
    Else
        Me.FilterOn = False
    End If
    
End Sub
 

VBABeginner3

Registered User.
Local time
Today, 02:31
Joined
Apr 16, 2017
Messages
11
Thank you Markk!

This is what I ended up with. I received a response from another forum so I didn't get a chance to try yours.

Private Sub cmdSearch_Click()
strSearch = ""
For Each varItem In Me!GroupList.ItemsSelected
strSearch = strSearch & "'" & Me!GroupList.ItemData(varItem) & "',"
Next varItem
MsgBox (strSearch)
If Len(strSearch) = 0 Then
Task = "select * from Group_Affiliations"
Else
strSearch = Left(strSearch, Len(strSearch) - 1)
Task = "select * from Group_Affiliations where ([Group Affiliations] in (" & strSearch & "))"
End If
DoCmd.ApplyFilter Task
End Sub
 

Users who are viewing this thread

Top Bottom