Multiple Criteria Filter Subform (1 Viewer)

rendon115

Registered User.
Local time
Today, 13:29
Joined
Oct 24, 2019
Messages
10
Relatively simple, I have a Subform in datasheet view that currently takes in filters from both a Textbox entry, and a MultiSelect Listbox. It filters just fine, based on both of those entries/selections. But it only filters either the Textbox, or the Listbox, but not both. What I would like it to do, is prioritize the selection(s) from the Listbox, and then the user can type in the textbox search down further to a specific record. As a side note, the Textbox filters on the MNumber field, and the Multiselect Listbox on the TagsCONCAT field.

Not quite sure how to merge both together, by using a Module function call with both as the values...? Can you declare both the CriteriaLIST and SearchBox as public and pass Strings between the two? Such as
Code:
.Form.Filter = strCriteria AND like '*" & SearchBox.Text & "*'"
Even if that was the case, Im still very new to VBA and not quite sure where to start with that.

Code for both the Listbox and TextBoxSearch:

Code:
'Multi Select Listbox
Public Sub CriteriaLIST_Click()
Dim strCriteria As String
Dim var As Variant


For Each var In Me.CriteriaLIST.ItemsSelected
    strCriteria = strCriteria & " AND [TagCONCAT] Like '*" & Me.CriteriaLIST.ItemData(var) & "*'"
Next


If strCriteria <> "" Then strCriteria = Mid(strCriteria, 6)
Me.SearchSUBFORM.Form.Filter = strCriteria
Me.SearchSUBFORM.Form.FilterOn = True

End Sub

'Textbox Search
Public Sub SearchBox_Change()
 Me.SearchSUBFORM.Form.Filter = "[MNumber] like '*" & SearchBox.Text & "*'"
 Me.SearchSUBFORM.Form.FilterOn = True
 
End Sub
 

isladogs

MVP / VIP
Local time
Today, 20:29
Joined
Jan 14, 2017
Messages
18,186
Post was moderated. Now approved.
This can happen when new members do lengthy posts or use code tags.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 04:29
Joined
May 7, 2009
Messages
19,169
Code:
Public Function fncFilter()
Dim strCriteria As String
Dim var As Variant

strCriteria = "[MNumber] like '*" & SearchBox.Text & "*'"
For Each var In Me.CriteriaLIST.ItemsSelected
    strCriteria = strCriteria & " AND [TagCONCAT] Like '*" & Me.CriteriaLIST.ItemData(var) & "*'"
Next


Me.SearchSUBFORM.Form.Filter = strCriteria
Me.SearchSUBFORM.Form.FilterOn = True

End Function
remove the [Event Procedure] from both the MNumber textbox and CriteriaLIST listbox and instead type this:

=fncFilter()
 

rendon115

Registered User.
Local time
Today, 13:29
Joined
Oct 24, 2019
Messages
10
I had to drop the .Me on everything here, and now Im getting a run-time error '424' on this line:
Code:
strCriteria = "[MNumber] like '*" & SearchBox.Text & "*'"
On a semirelated note, while calling this function, do I need to specify exact locations for everything involved? Such as:

Code:
SearchSUBFORM.Form.Filter = strCriteria
'Full path including FORM name
MNumberSearch_FORM.SearchSUBFORM.Form.Filter = strCriteria
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 04:29
Joined
May 7, 2009
Messages
19,169
put the function on the main form not on separate module.
 

rendon115

Registered User.
Local time
Today, 13:29
Joined
Oct 24, 2019
Messages
10
Thank you for your help, that's exactly what I needed. I also found out I have to set the .GetFocus on the textbox first to get the values of of the SearchBox without it throwing a fit.
 

Users who are viewing this thread

Top Bottom