Filter ListBox for Multiple Groups

[burrina]

I need to be able to filter my list box for multiple groups,individual or all. Your ideas would be very much appreciated. Having a brain dead day!

[Rudi]

How do you propose to filter for an individual?
A second dropdown with ONLY email address, or at least two pieces of info; group AND email address listed in the dropdown.
One for groups and <<ALL>> and the other for individual and <<ALL>>

[HansV]

If you want to be able to select multiple groups, you'll have to use a multi-select list box instead of the combo box cbogroup.

[burrina]

I tried adding a new list box and then a requery of the list box for the results with no success. What am I missing?
Changed the record source to the below to reflect the new list box.

SELECT tblEmailTo.eemailto, tblEmailGroup.egroup, tblEmailTo.eemailaddress FROM tblEmailGroup INNER JOIN tblEmailTo ON tblEmailGroup.eGroupID = tblEmailTo.eGroupID WHERE (((tblEmailGroup.egroup) Like "*" & [lstgrps]));

[HansV]

See if the attached version does what you want (I didn't bother about the details, I just changed the combo box to a list box and added the code needed to select groups).

MultipleGroups.zip

[burrina]

Almost. How can I then after DeSelecting make the ListBox show All records?

[HansV]

Change the On Click event of cbogroup to

Code: Select all

Private Sub cbogroup_Click()
    Dim varItem As Variant
    Dim strIn As String
    
    For Each varItem In Me.cbogroup.ItemsSelected
        strIn = strIn & "," & Me.cbogroup.ItemData(varItem)
    Next varItem
    If strIn <> "" Then
        strIn = Mid(strIn, 2)
        Me.lstlist.RowSource = "SELECT * FROM qryEmailClientsByGroup WHERE eGroupID In (" & strIn & ")"
    Else
        Me.lstlist.RowSource = "SELECT * FROM qryEmailClientsByGroup"
    End If
End Sub

[burrina]

Thank You so much! Exactly what I was after.