Solved

Which is better? Using a multiselect list box to filter a query or to filter a report?

Posted on 2015-01-19
6
593 Views
Last Modified: 2015-01-21
I am setting up a form in my Access database that has several multi select list boxes that are going to be used to set the criteria for a query that will serve as the basis for a report.

I have been scanning the internet trying to find the best way to do this and now I have a question for you all.

What is better? Using the list boxes to filter the query or using them to set the criteria for the report? Is there much of a difference?

I am pretty fuzzy on how to do either, but would like to see some opinions about which would be better.

Some factors may include: The query has many tables and there are multiple multiselect list boxes.

All advice is welcome!
0
Comment
Question by:Megin
6 Comments
 
LVL 84

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 125 total points
ID: 40558758
Generally speaking, I think you're better off sending criteria to the report via the OpenReport method. This normally allows you to design more "generic" forms and filter them it as needed.

To use this method, you just create a standard WHERE clause without the word "WHERE", and send that as the WhereClause argument of the OpenReport method:

Dim s As String
Dim var As Variant

For each var in Me.YourListBox.ItemsSelected
  s = s & Me.YourListBox.ItemsSelected(var, 0) & ","
Next

s = Left(s, Len(s)-1)

DoCmd.OpenReport "MyReport", acViewPreview, , "SomeField IN (" & s & ")"

http://msdn.microsoft.com/en-us/library/office/ff192676%28v=office.15%29.aspx

And, if you're using MultiSelect listboxes, you're pretty well stuck with the OpenReport method.
0
 

Author Comment

by:Megin
ID: 40558867
Thank you! I am going to start working on this tomorrow.
0
 
LVL 26

Assisted Solution

by:Nick67
Nick67 earned 125 total points
ID: 40558954
Using the list boxes to filter the query or using them to set the criteria for the report? Is there much of a difference?
There's, by definition, going to be absolutely no difference in the end result.  After all, you want to create a report that has a certain set of data in it.

There's three ways of going about it, and all three of them are useful to have in the toolbox going forward.
A 'parameter and launching' form that has controls on it that supply critical information to a report and command buttons (or perhaps comboboxes using the AfterUpdate event) is a very good thing to develop.paramter form
The WhereClause argument of OpenReport as @Scott mentioned is very useful when you have a general report that contains many, many records and it is useful to generate subsets of those records in smaller, more detailed subsets.

The third is the last optional argument of OpenReports.  That's OpenArgs.  It's a string of great possible length--and it can be anything you can compose.  I like passing in a string of arguments delimited by semi-colons  "PrintPaperYes;EmailPDFYes;Select * from TempTable where ID = 40;BadID = 75"  You then put OpenArgs through a Split() in the Report_Open Event and do whatever you'd like with the results
Dim MyArgs() as string
MyArgs = Split(Nz(me.OpenArgs,""),";")
If MyArgs(0) = "PrintPaperYes" then ....
If MyArgs(1) = "EmailPDFYes" then ....
Me.recordsource = MyArgs(2)
Me.Filter = MyArgs(3)
me.FilterOn =True
 
These possibilities are pretty much endless.

All three are very highly useful techniques to be able to work with.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 125 total points
ID: 40561026
As with anything, there is no "Better" way to do anything, ...different approaches work for varying circumstances.

I, personally, would filter the report.
If you filter the query, ...and the query gets deleted by accident, ...you loose your filter.

Other than that, pick the way that you are more comfortable with...
;-)

JeffCoachman
0
 
LVL 31

Accepted Solution

by:
Helen_Feddema earned 125 total points
ID: 40562724
My Fancy Filters sample database illustrates using criteria of various types to filter a subform or report:
http://www.helenfeddema.com/Files/accarch129.zip
Fancy Filters form
and this one shows how to filter by multiple selections in a multi-select listbox:
http://www.helenfeddema.com/Files/accarch197.zip
Multi-select listbox filtering
0
 

Author Closing Comment

by:Megin
ID: 40563330
Thank you, everyone, for the suggestions! I think I can move forward with this now. I just wanted to know what everyone thought and you all gave me more than enough to work with.

Again, Thank you!!!!!!
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

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…
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…
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 …
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.

705 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

14 Experts available now in Live!

Get 1:1 Help Now