Apply filters to form using two multi-select list boxes. (1 Viewer)

max333

New member
Local time
Today, 14:23
Joined
Dec 20, 2019
Messages
2
Hello all...New to this forum, old to Access and new to VBA...

Need help using multiple multi-select list boxes to filter a form. When I use the following code to filter using a single numerical list box (all list boxes are Multiselct Simple), everything is good.

Dim varitem90 As Variant
Dim Year As String

For Each varitem90 In Me.List90.ItemsSelected
Year = Year & Me.List90.ItemData(varitem90) & ","
Next varitem90
'Strip off the ending comma
Year = Left(Year, Len(Year) - 1)
'Turn it into an IN clause
Year = "year_is IN (" & Year & ")"

DoCmd.ApplyFilter "[year_is]", Year

When I use the following to filter using a single text list box, everything is good.

Dim varitem100 As Variant
Dim TRIM As String

For Each varitem100 In Me.List100.ItemsSelected
TRIM = TRIM & "'" & Me.List100.ItemData(varitem100) & "',"
Next varitem100
'Strip off the ending comma
TRIM = Left(TRIM, Len(TRIM) - 1)
'Turn into IN clause
TRIM = "TRIM_ss IN (" & TRIM & ")"

DoCmd.ApplyFilter "[trim_ss]", TRIM


I need help combining these two so that the user can select items from both list boxes, then apply filters to the form for both.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 14:23
Joined
Oct 29, 2018
Messages
18,764
Hi. Welcome to AWF!

Just wanted to say, you might want to consider using a different name for your variables because Year and Trim are function names in Access.


Have you tried something like?


Code:
DoCmd.ApplyFilter , Year & " AND " & TRIM
 

max333

New member
Local time
Today, 14:23
Joined
Dec 20, 2019
Messages
2
I tried your apply filter suggestion and it worked perfectly! I also changed the names of my variables to avoid potential problems. Thank you for taking the time to review and make suggestions on my post. I really appreciate it!
 

theDBguy

I’m here to help
Staff member
Local time
Today, 14:23
Joined
Oct 29, 2018
Messages
18,764
I tried your apply filter suggestion and it worked perfectly! I also changed the names of my variables to avoid potential problems. Thank you for taking the time to review and make suggestions on my post. I really appreciate it!

Hi. You're welcome. Glad to hear you got it to work. Good luck with your project.
 

Users who are viewing this thread

Top Bottom