Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 04-17-2017, 07:33 AM   #1
VBABeginner3
Newly Registered User
 
Join Date: Apr 2017
Location: Boston, MA
Posts: 11
Thanks: 1
Thanked 0 Times in 0 Posts
VBABeginner3 is on a distinguished road
VBA-Apply Filter to Search Button

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

VBABeginner3 is offline   Reply With Quote
Old 04-17-2017, 08:06 AM   #2
MarkK
Super Moderator
 
MarkK's Avatar
 
Join Date: Mar 2004
Location: Vancouver BC
Posts: 7,761
Thanks: 10
Thanked 1,289 Times in 1,227 Posts
MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all
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
__________________
formerly known as lagbolt | Windows 10 | Access 2010 | Visual Studio 2013 | "Institutions have a vested interest in perpetuating the problems to which they are the solution." - Clay Shirky
MarkK is offline   Reply With Quote
The Following User Says Thank You to MarkK For This Useful Post:
VBABeginner3 (04-17-2017)
Old 04-17-2017, 12:43 PM   #3
VBABeginner3
Newly Registered User
 
Join Date: Apr 2017
Location: Boston, MA
Posts: 11
Thanks: 1
Thanked 0 Times in 0 Posts
VBABeginner3 is on a distinguished road
Re: VBA-Apply Filter to Search Button

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

VBABeginner3 is offline   Reply With Quote
Reply

Tags
access 2013 , vba

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
MS Access - apply filter to report, using combo boxes but can't apply a manual filter meanie Reports 11 02-15-2017 04:17 PM
apply filter button not working accesslearner Modules & VBA 16 01-05-2011 11:21 AM
Need a button to apply a filter chaostheory Forms 3 10-28-2008 09:11 AM
apply filter button? sir_dan_mitchell General 1 09-13-2002 09:23 AM
[SOLVED] Command Button to Apply Filter ntolani Forms 0 05-22-2001 10:14 AM




All times are GMT -8. The time now is 10:29 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World