Solved

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

Posted on 2015-01-19
6
619 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Suggested Solutions

In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
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.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

919 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

16 Experts available now in Live!

Get 1:1 Help Now