Multiple Criteria Filter Subform

[rendon115]

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

.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:

'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]

Post was moderated. Now approved.
This can happen when new members do lengthy posts or use code tags.

 

[arnelgp]

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]

I had to drop the .Me on everything here, and now Im getting a run-time error '424' on this line:

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:

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

 

[arnelgp]

put the function on the main form not on separate module.

 

[rendon115]

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.