Multi-select listboxes and All

[Amy]

I have 3 Multi-select listboxes, all based on queries using union statements
to get the "all" to show up example - select [matl id], desc From
wdog_materials_list UNION Select "<ALL>","<ALL>" from wdog_materials_list
ORDER BY [matl id]; . I then use texboxes to create the sql statement
based on the selections in the three list boxes. This is working fine, my
questions are:

1) How can I get the ALL to be highlighted as the default. Everything I try
I get errors it can't recognize "<ALL>".

2) I would like to filter each text box based on what the user selects in
the one above but I cannot get the query to work with the ALL and the
multi-select.

 

[ryguy7272]

I can't quite envision what you're dealing with there, but this may help:
http://www.datapigtechnologies.com/flashfiles/listboxtrick.html

Good luck,
Ryan

 

[Pendragon]

if [matl id] is numeric, then you need to have UNION Select 0, "<ALL>". To
have the "<ALL>" appear first, change your order.

Also, is "desc" in your initial Select statement a field name? Not good as
DESC is a sort order abbreviation. If that's supposed to be something like
"description", you should consider changing your field name.

In the properties for your combo box, you can set your default value to 0
(or "<ALL>" depending on the field type of [matl id]).

For part 2, you need an IF statement.

IF me.cboSelectOption = 0 THEN
strSQL = "SELECT * FROM MyQuery" (use the basic query and apply no
filters)
ELSE
strSQL = "SELECT * FROM MyQuery WHERE " & <____> (append filters here)
END IF

You can then use the strSQL statement as the recordsource for a form or
report. What I've done is set up a generic display form which grabs the
filters from your combo box and text boxes, then uses the strSQL statement as
the recordsource for the display form of the query.

If you need help on how to append the filters, do a search in this forum for
Form Search or check out any of the MVPs' websites - lots of good stuff going
on there.

Hope that gets you started.