Filter subform

[rendon115]

Ive been searching for several hours, but I have not found a single solution to what Im looking for. To make a long story short, I have a query that I need to put into a subform in datasheet view, or alternatively a listbox. And then have another listbox, with multiselect enabled, to filter through a single field in the query. That query field has multiple text words in it, I will also note.

i.e. Query field NAME has

John Nick Derick
Nick Derick Dave
Trevor Bill Dave
etc...​

And my Multiselect listbox has

Derick
Dave
Nick
John
Trevor
Bill
etc...​

As clickable options

When I click on one, it needs to only display the records with that keyword in NAME


Apparently this has never been done before in the history of mankind, according to literal hours of searching. So here I am, asking away.

 

[theDBguy]

Hi. Welcome to AWF!


So, if a user selects the first three items (Derick, Dave, and Nick), do you want the query/form to show one record that matches all selections or two or more records matching "some" of the selections?

 

[rendon115]

Hi. Welcome to AWF!


So, if a user selects the first three items (Derick, Dave, and Nick), do you want the query/form to show one record that matches all selections or two or more records matching "some" of the selections?

When the user selects Derick Dave and Nick, I want all records with field NAME that contain those selections. If they only pick Nick, then display the records with at least Nick.

EDIT: New to Access, kind of thrown on my by my boss randomly. So Ive been learning this past week. Im starting to get it down, just hung up on this

 

[theDBguy]

When the user selects Derick Dave and Nick, I want all records with field NAME that contain those selections. If they only pick Nick, then display the records with at least Nick.

EDIT: New to Access, kind of thrown on my by my boss randomly. So Ive been learning this past week. Im starting to get it down, just hung up on this

Hi. Using your sample data above, I was trying to determine what "with field NAME that contain those selections" actually mean. For example, the first record contains Derrick and Nick but not Dave, the second record contains all selections, and the third record contains only Dave. So, if the user selects Derrick, Dave, and Nick, do you want the query to return record number two only or all three?

 

[rendon115]

Hi. Using your sample data above, I was trying to determine what "with field NAME that contain those selections" actually mean. For example, the first record contains Derrick and Nick but not Dave, the second record contains all selections, and the third record contains only Dave. So, if the user selects Derrick, Dave, and Nick, do you want the query to return record number two only or all three?

Only the record number two.

 

[theDBguy]

Only the record number two.

Thank you, that clarifies it. So, you'll need to loop through the listbox selections and build your criteria using the AND operator. For example:

Dim strCriteria As String
Dim var As Variant


For Each var in Me.ListboxName.ItemsSelected
    strCriteria = " AND [FieldName] Like '*" & Me.Listbox.ItemData(var) & "*'"

Next


If strCriteria<>"" Then strCriteria = Mid(strCriteria, 6)

Hope that helps...

 

[arnelgp]

add code to AfterUpdate event of the filtering list:

private sub listName_AfterUpdate()
    Dim sFilter As Variant
    Dim var As Variant
    Dim coll As New Collection
    Dim i As Integer
    sFilter = Null
    If lstName.ItemsSelected.Count < 1 Then
        Me.List1.RowSource = "Select [Name] From QueryName;"
    Else
        For Each var In listName.ItemsSelected
            Coll.Add  Chr(34) & ListName.ItemData(var) & Chr(34), var & ""
        End If
        For i = 1 To coll.Count
            sFilter  = (sFilter + " Or ") & "Instr(1, [Name], " & col(i) & ")>1"
        Next
        Me.List1.RowSource = "SELECT [Name] From Query Where " & sFilter & ";"
    End If
    Me.List1.Requery
End Sub

 

[rendon115]

Thank you, that clarifies it. So, you'll need to loop through the listbox selections and build your criteria using the AND operator....Hope that helps...

Thank you. By passing strCriteria into my subform, it works exactly how I need it to. Thank you, saved me a headache of a time. Out of curiousity though, because I would like to learn more about VBA as its heavily used in my office, can you explain the line

strCriteria = Mid(strCriteria, 6)

to me? Everything else makes sense, but Im not familiar with whats going on there.

Code just because:

Private Sub CriteriaLIST_Click()
Dim strCriteria As String
Dim var As Variant


For Each var In Me.CriteriaLIST.ItemsSelected
    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

Whereas SearchSUBFORM being the subform, and CriteriaLIST being the list of criteria to choose from

 

[isladogs]

The last post by the OP was moderated. Posting this to trigger email notifications

 

[theDBguy]

Thank you. By passing strCriteria into my subform, it works exactly how I need it to. Thank you, saved me a headache of a time. Out of curiousity though, because I would like to learn more about VBA as its heavily used in my office, can you explain the line

strCriteria = Mid(strCriteria, 6)

to me? Everything else makes sense, but Im not familiar with whats going on there.

Code just because:

Private Sub CriteriaLIST_Click()
Dim strCriteria As String
Dim var As Variant


For Each var In Me.CriteriaLIST.ItemsSelected
    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

Whereas SearchSUBFORM being the subform, and CriteriaLIST being the list of criteria to choose from

Hi. Glad to hear you got it to work. If you step through the code and examine the content of strCriteria, it might give you an idea what the Mid(strCriteria, 6) does. But to give you a clue, it removes the initial "AND." Cheers!

 

[rendon115]

Hi. Glad to hear you got it to work. If you step through the code and examine the content of strCriteria, it might give you an idea what the Mid(strCriteria, 6) does. But to give you a clue, it removes the initial "AND." Cheers!

Well consider me dumb, but I hadn't checked to see if multiple selected criteria actually worked. I've been working on the rest of the database, trying to get it set up, and didnt notice until just now. It works, technically, but for only for whichever Criteria I selected last. Any idea of where I can go from here?

 

[theDBguy]

Well consider me dumb, but I hadn't checked to see if multiple selected criteria actually worked. I've been working on the rest of the database, trying to get it set up, and didnt notice until just now. It works, technically, but for only for whichever Criteria I selected last. Any idea of where I can go from here?

I'm sorry, I missed that part. Change the code to use this instead:

strCriteria = strCriteria & " AND [TagCONCAT] Like '*" & Me.CriteriaLIST.ItemData(var) & "*'"

 

[rendon115]

I'm sorry, I missed that part. Change the code to use this instead:

strCriteria = strCriteria & " AND [TagCONCAT] Like '*" & Me.CriteriaLIST.ItemData(var) & "*'"

Once again, thank you. Now I checked it completely (So I dont have to bother you again) and it works perfectly. Thanks again

 

[theDBguy]

Once again, thank you. Now I checked it completely (So I dont have to bother you again) and it works perfectly. Thanks again

Hi. You're welcome. We're all happy to assist. Good luck with your project.