Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 662
  • Last Modified:

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

OK, so I have one form that needs to filter a second form.  The reason being is that the second form generates an email list from a UNION query.  So I have the first form with one multi-select list box containing Categories.  Then the user hits the GO button and I need a second form to open with it's multi-select list box filtered from the first using the same field Categories.  How would I go about this?  The second filter form just needs "pre-filtering" so to speak.
0
napsternova
Asked:
napsternova
  • 10
  • 9
1 Solution
 
PatHartmanCommented:
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;
0
 
napsternovaAuthor Commented:
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?
0
 
PatHartmanCommented:
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.
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
napsternovaAuthor Commented:
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;

Open in new window

0
 
PatHartmanCommented:
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.
0
 
napsternovaAuthor Commented:
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.
0
 
PatHartmanCommented:
Test your wrapper query with a hard-coded category.  If that works, you know you are not getting a value from the form.
0
 
napsternovaAuthor Commented:
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?
0
 
PatHartmanCommented:
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

Open in new window

Then the query would look like:
        strSQL = "SELECT * FROM yourquery WHERE "
        strSQL = strSQL & " Category In (" & Me.txtSelectedCategories & ");"
0
 
napsternovaAuthor Commented:
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
0
 
PatHartmanCommented:
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.
0
 
napsternovaAuthor Commented:
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.
0
 
PatHartmanCommented:
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.
0
 
napsternovaAuthor Commented:
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.
0
 
PatHartmanCommented:
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.
0
 
napsternovaAuthor Commented:
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

Open 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.
0
 
napsternovaAuthor Commented:
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.
0
 
PatHartmanCommented:
I had to touch everything starting with changing the table definition.  Relationships are made on Primarykey to foreignkey.  You were doing data field to data field.  So after I fixed the relationship, I had to create an update query to populate CategoryID in the contacts table (Please delete Category from this table, I forgot to do it.)

I changed the queries to fix up the joins.

Then I changed the OpenForm Method to get the argument in OpenArgs.  You had it in filter.

And finally, I added code in the Load event of form 2 to change the RowSource to apply the In(clause) you built in form 1.
ATL-Database-DEMO--Fixed.zip
0
 
napsternovaAuthor Commented:
It works great.  Thank you so very much
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 10
  • 9
Tackle projects and never again get stuck behind a technical roadblock.
Join Now