Solved

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

Posted on 2014-01-23
19
649 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
ID: 39805043
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
ID: 39805066
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
ID: 39805105
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
ID: 39805123
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
ID: 39805169
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
ID: 39805195
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
ID: 39805612
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
ID: 39806633
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
ID: 39806770
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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 

Author Comment

by:napsternova
ID: 39806888
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
ID: 39806975
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
ID: 39806997
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
ID: 39807328
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
ID: 39807374
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
ID: 39807475
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
ID: 39807576
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
ID: 39809683
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
ID: 39810867
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
ID: 39812087
It works great.  Thank you so very much
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

932 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

11 Experts available now in Live!

Get 1:1 Help Now