Multiple Multi-Select Listboxes as Parameters

  • Thread starter Thread starter croy
  • Start date Start date
C

croy

For analysis purposes, I'd like to set up a starter form
with four parameters (each capable of multiple selections)
to select criteria for a charting report, which would be
opened from a command button on the form.

I could set up a table(s?) to hold the parameter info, and
use four subforms containing comboboxes on the starter form
to select the parameters, or I could put four multi-select
listboxes on the form, and try to slug my way thru the code
to generate the query parameters (probably way over my
head).

I'm pretty sure I could make the subform idea work, but it
wouldn't be nearly as handy and graphical as the listbox
idea, nor as tidy.

I've gathered some examples of code to generate query
parameters from *one* multi-select listbox, but I haven't
seen anything for using more than one.

Is there a barrier (or other pain) to using more than one
multi-select listbox that I'm not seeing? If not, could
somebody give me nudge as to how to best combine the code
for using more than one? Or is there a better direction,
altogether?
 
I have a generic function that can be used at
http://www.tek-tips.com/faqs.cfm?fid=6099. This won't be the most efficient
solution but might work for you.
Click to expand...

I've been able to make my report starter form with subforms
for each of the four listboxes, much like your example, but
am stimied as to how to hand that off to the starting query
for the report.

I tried simply putting parameters in the report query that
points to the subforms, but as you probably know, that only
fetches the first record from each subform, returning fewer
records for the report than it should.

Then I tried replacing the four subforms with just one, that
would hold all the necessary fields, and adding that query's
output to the starting report query, but keep getting popups
asking for parameters, and the subform remains empty no
matter which items I choose on any of the multi-select
listboxes.

Does it sound like I'm at least heading in a workable
direction?
 
I have a generic function that can be used at
http://www.tek-tips.com/faqs.cfm?fid=6099. This won't be the most efficient
solution but might work for you.
Click to expand...

Thanks Duane, this looks very promising, but I don't
understand all the code, and haven't gotten it working
completely yet.

Individual subforms for each "parameter" doesn't get me to
the goal-line, so I made a single subform to get filtered by
selecting from the four listboxes.

I have two of the listboxes working beautifully with the
subform, but when I try to get another involved, the wheels
come off. A error pops up when I open the form:
"Microsoft Visual Basic
Run-time error '2455':
You entered an expression that has an invalid
reference to the property ItemsSelected."

If I click Debug, it takes me to the module code line

"For Each item In lbo.ItemsSelected"

If I click Help (from the error dialog), a totally blank
help screen opens.

I can't figure out why two of my listboxes work so well, and
the other two not at all. I've checked the field and
control names for accuracy dozens of times.

Access 2002 on WinXP all patched.

Any thoughts appreciated.
 
I can't see anything that you have tried. Could you provide the SQL view of
your query? Do you use spaces in any control or form names?
 
I can't see anything that you have tried. Could you provide the SQL view of
your query? Do you use spaces in any control or form names?
Click to expand...

SQL for the subform query:

SELECT tblIvDetail.IvDetId, tblIvSurv.GeoLocId,
Year([SurveyDate]) AS Years, tblIvDetail.GeoLocId,
IsSelectedVar("frmGoAngEffAcc2","lstYears",[Years]) AS Expr1
FROM tblIvSurv INNER JOIN tblIvDetail ON tblIvSurv.IvSurvId
= tblIvDetail.IvSurvId
WHERE
(((IsSelectedVar("frmGoAngEffAcc2","lstAccess",[tblIvSurv].[GeoLocId]))=-1)
AND
((IsSelectedVar("frmGoAngEffAcc2","lstExclude",[tblIvDetail].[GeoLocId]))=0)
AND ((tblIvDetail.Done)=-1));

No spaces used in any names.

Late yesterday, another approach dawned on me: leave the
four subforms in place (like your sample/example), and just
put their underlying querys in the starter query for the
report. It seems to be working, although the results aren't
quite right yet--working on that. This approach has the
advantage of the starter form being very quick when
selecting items in the multi-select listboxes, and speeding
up the report query by the strong filtering of the starting
input.

I still don't know why the single subform approach wouldn't
work for me, but at least *something* is working.

One question on the "IsSelectedVar()" function: it has the
arguments of "strFormName As String", "strListBoxName As
String", and "varValue As Variant", and "As Boolean" after
that. Does that mean that a) it could be called from
anywhere? b) the result will be true or false which is (?)
Yes or No (?) which is -1 or 0? It seems like I should know
this, but I don't... sigh.
 
I wouldn't send a calculated column into the function.
SELECT tblIvDetail.IvDetId, tblIvSurv.GeoLocId,
Year([SurveyDate]) AS Years, tblIvDetail.GeoLocId,
IsSelectedVar("frmGoAngEffAcc2","lstYears",Year([SurveyDate])) AS Expr1
FROM tblIvSurv INNER JOIN tblIvDetail ON tblIvSurv.IvSurvId
= tblIvDetail.IvSurvId
WHERE
(((IsSelectedVar("frmGoAngEffAcc2","lstAccess",[tblIvSurv].[GeoLocId]))=-1)
AND
((IsSelectedVar("frmGoAngEffAcc2","lstExclude",[tblIvDetail].[GeoLocId]))=0)
AND
IsSelectedVar("frmGoAngEffAcc2","lstYears",Year([SurveyDate])) =-1
AND ((tblIvDetail.Done)=-1));

Does that mean that
a) it could be called from anywhere?
Yes, the form must be open
b) the result will be true or false which is (?) Yes or No (?) which is -1
or 0?
Yes, the return value will be True/-1 or False/0
--
Duane Hookom
Microsoft Access MVP


croy said:
I can't see anything that you have tried. Could you provide the SQL view of
your query? Do you use spaces in any control or form names?
Click to expand...

SQL for the subform query:

SELECT tblIvDetail.IvDetId, tblIvSurv.GeoLocId,
Year([SurveyDate]) AS Years, tblIvDetail.GeoLocId,
IsSelectedVar("frmGoAngEffAcc2","lstYears",[Years]) AS Expr1
FROM tblIvSurv INNER JOIN tblIvDetail ON tblIvSurv.IvSurvId
= tblIvDetail.IvSurvId
WHERE
(((IsSelectedVar("frmGoAngEffAcc2","lstAccess",[tblIvSurv].[GeoLocId]))=-1)
AND
((IsSelectedVar("frmGoAngEffAcc2","lstExclude",[tblIvDetail].[GeoLocId]))=0)
AND ((tblIvDetail.Done)=-1));

No spaces used in any names.

Late yesterday, another approach dawned on me: leave the
four subforms in place (like your sample/example), and just
put their underlying querys in the starter query for the
report. It seems to be working, although the results aren't
quite right yet--working on that. This approach has the
advantage of the starter form being very quick when
selecting items in the multi-select listboxes, and speeding
up the report query by the strong filtering of the starting
input.

I still don't know why the single subform approach wouldn't
work for me, but at least *something* is working.

One question on the "IsSelectedVar()" function: it has the
arguments of "strFormName As String", "strListBoxName As
String", and "varValue As Variant", and "As Boolean" after
that. Does that mean that a) it could be called from
anywhere? b) the result will be true or false which is (?)
Yes or No (?) which is -1 or 0? It seems like I should know
this, but I don't... sigh.
Click to expand...
 
Back
Top