Solved

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

Posted on 2014-01-23
19
648 Views
Last Modified: 2014-01-27
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
Comment
Question by:napsternova
  • 10
  • 9
19 Comments
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
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
 

Author Comment

by:napsternova
Comment Utility
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
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
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
 

Author Comment

by:napsternova
Comment Utility
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
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
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
 

Author Comment

by:napsternova
Comment Utility
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
 
LVL 34

Expert Comment

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

Author Comment

by:napsternova
Comment Utility
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
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 

Author Comment

by:napsternova
Comment Utility
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
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
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
 

Author Comment

by:napsternova
Comment Utility
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
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
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
 

Author Comment

by:napsternova
Comment Utility
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
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
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
 

Author Comment

by:napsternova
Comment Utility
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
 

Author Comment

by:napsternova
Comment Utility
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
 
LVL 34

Accepted Solution

by:
PatHartman earned 500 total points
Comment Utility
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
 

Author Closing Comment

by:napsternova
Comment Utility
It works great.  Thank you so very much
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now