Solved

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

Posted on 2015-01-19
6
687 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 85

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

 
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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
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.

726 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