Multi-Select menu form to filter another multi-select filter form

Assuming the first form remains open (it could be hidden if you like), just reference it in the criteria for the combo's RowSource.

Where somefield = Forms!firstform!somecombo;

The RowSource for the second combo, the one needing filtering, is populated by a UNION query.  Should I add the Where clause to the Union Query?

Yes but that can be confusing so I would create a new "wrapper" query that selects the Union query and put the criteria in the wrapper query.

hmmmm, my result returns no value.  My first form is called frmATLReportFilter and the list is called lstCategories  The UNION Query (Query1) contains a value of Category and it's list is called lstContacts  Here's my SQL that is in the RowSource for the filtered form

SELECT Query1.*, *
FROM Query1
WHERE Category = Forms!frmATLReportFilter!lstCategories;

Select allOpen in new window

The first form must be open when the query runs.

lstCategories must be on the main form - frmATLReportFilter.  If it is on a subform, you need to change the reference.

That's the way I have it set up but get no values on Enter.  Can I test it somehow with out using the first filter form?  I don't get any errors, the first form stays open when the second form opens but something isn't quite right.

Test your wrapper query with a hard-coded category.  If that works, you know you are not getting a value from the form.

Yes hardcoded filter does filter the form.  So it looks like the first filter isn't passing the value.

OK, so my first filter form has a button with a Macro to open the second filter form.  How do I "pass" the value(s) that were selected?  Do I do that in the Macro Where clause?  or should I create an action script and pass it that way?

I just reread your original post and noticed the "multi-select" listbox part.  Multi-select listboxes are not bound and do not return a .value property.  You have to create a code loop to get all the values and then use them to create an In() clause.  The following builds the In() clause in a hidden form field because it is used outside of the form that builds it.

    For Each i In Me.lstCategory.ItemsSelected
        strIN = strIN & QUOTE & Me.lstCategory.ItemData(i) & QUOTE & ","
    Next i
    If strIN & "" = "" Then
        Me.txtSelectedCategories = Null
    Else
        strIN = Left(strIN, Len(strIN) - 1) ' remove trailing comma.
        Me.txtSelectedCategories = strIN
    End If

Select allOpen in new window

Then the query would look like:
        strSQL = "SELECT * FROM yourquery WHERE "
        strSQL = strSQL & " Category In (" & Me.txtSelectedCategories & ");"

omg, maybe there's an easier way.  I am really lost now.  Maybe you can have a look at my database file.  I took out a lot of stuff but the meat is still there.  The default form that opens needs to filter the frmSendEmail form.
ATL-Database-DEMO.accdb

I'm not sure why you replaced the code with a macro so I got rid of it.

I also don't know why you are opening a second form.

However, I do know you can't send the string you built as a filter because the form you are opening isn't bound.  If you want to send the string to this form, send it in the OpenArgs argument.

You probably need to use the string you built to change the RowSource of lstContacts.

The second form is the form I need to be able to send emails from the filtered list.  Right now the email form has 1000+ entries in all different categories.  If I want to send an email blast to just Banks I wanted to filter the form to just show Banks.  Or if I want to send to just eateries the people in the baby clothes business shouldn't get an email.  That's why I have two forms.  One form filters the other.  Either I filter it or my end-user has to scroll through the list hold shift and select just the categories she wants to send too.

If you are sending the email to the entire contents of the second lstbox, then you don't need it or the second form.  You can send the email from the first form.

If you are using the second listbox to further filter, then use the filter you built on the first form to modify the RowSource for the second listbox so that it shows only the people in the selected categories.  Then let the user select multiple names from the second listbox and build another In() clause and use that to create the recordset you use to send the emails.

This is where I am stuck.  The email list box by itself works, I am just having the darndest time figuring out the filtering filter.

It isn't a filter.  It is selection criteria for a WHERE clause.  In the Open event of the second form, rebuild the RowSource query for the listbox to include the In() you developed in the first form.

Select ... From ... Where somefield In(...)


Are you filtering again using the listbox on the second form?  If so, you'll need to use similar code to pick out the IDs for the people you select in the second listbox.  Then using that In() clause, you build the query that you will use in your email loop.

Yes, I am filtering again if the end-user for some reason want to pick and choose who to email.  That action works fine.

Private Sub Form_Open(Cancel As Integer)
Dim emName As String, varItem As Variant
Dim emailsubject As String
Dim emailBody As String

emailsubject = "Confirmation Email"
'note hte carriage return line feed to insert a blank line between
'the greeting and the message body. Also, there is no need for the
' & at the beginning of the second and third lines because we used a
' comma after the greeting - just before the close quote on the first line.

emailBody = "Dear Customer, " & vbCrLf & vbCrLf & _
    "We are sending this email to let you know that your request has been" _
    & " confirmed."

On Error GoTo cmdSendEMail_Click_error:

' If there are no selected items (Contacts) in the list, exit.
If Me!lstContacts.ItemsSelected.Count = 0 Then Exit Sub
           
For Each varItem In Me!lstContacts.ItemsSelected
    ' char(34) adds quotes to Me!lstContacts
    ' Then add a comma to separate the selected items
    emName = emName & Chr(34) & Me!lstContacts.Column(2, varItem) & Chr(34) & ","

Next varItem
'removes the last comma telling Access to stop looking
emName = Left$(emName, Len(emName) - 1)
'email only
DoCmd.SendObject acSendNoObject, , , , , emName, emailsubject, emailBody, True, False


cmdSendEMail_Click_error:
    If Err.Number = 2501 Then
        MsgBox "You just cancelled the Email. You'll have to start over.", vbCritical, "Alert!"
    
    ElseIf Err.Number > 0 Then
        MsgBox "error sending email." & vbCrLf & Err.Description, _
            vbCritical, "Send Error"
    End If
    

End Sub

Select allOpen in new window

I had lots of help with that as well.  I'm not a programmer but more of a designer trying to finish a previous employees project.

Pat,
Can you please just take a look at my file and try and see if you can get it to work?  I am not having any success.

It works great.  Thank you so very much