Query using all rows of a list box

[oleBucky]

Is it possible to have a query use all the rows of a list box (turn off the filter) when using two list boxes to drive a query?



Example:
List box 1 contains all the teams in my fantasy baseball league
List box 2 contains all the MLB teams
The list boxes pull from a table of MLB Players
User wants to find all the fantasy league players from the Cincinnati Reds
OR
User wants to find all the players for one fantasy league team regardless of the MLB team

BTW, I am not fixed with list boxes. If there is an option which might be better, I can certainly give it a try.

[oleBucky]

Nearly 50 views and no replies... Let me try rephrasing.

Is it possible to add an element to a list box to allow a query based on the selection from that list box to select all the rows in the list box? If so, I can't figger out how.

[June7]

See if this helps:
http://www.datapigtechnologies.com/f...earchform.html

[oleBucky]

Good stuff there which will help on some other situation I am encountering, but not quite what I am looking for.

I am currently trying to apply this idea.

http://support.microsoft.com/kb/210290

[June7]

Which are you doing - the function or the UNION? Show the RowSource statement for analysis.

[orange]

Good stuff there which will help on some other situation I am encountering, but not quite what I am looking for.

I am currently trying to apply this idea.

http://support.microsoft.com/kb/210290

Take a look at this one
http://www.tek-tips.com/faqs.cfm?fid=2330

[oleBucky]

Take a look at this one
http://www.tek-tips.com/faqs.cfm?fid=2330

That worked exactly as I envisioned! I will add it to my Terribly Twisted Query Tale and work on the next step. I have three list boxes and one text box I am trying to integrate. I will post if I run into a problem.

[oleBucky]

That worked exactly as I envisioned! I will add it to my Terribly Twisted Query Tale and work on the next step. I have three list boxes and one text box I am trying to integrate. I will post if I run into a problem.

One little glitch. I originally plagiarized the SQL as follows:

SELECT T1.HLBTeamID AS HLBID, T1.TeamName AS HLBName
FROM tblHLBTeams AS T1
ORDER BY HLBName;

UNION

Select "*" AS HLBID, "(All HLB Teams)" AS HLBName
FROM tblHLBTeams AS T1;

This worked until I closed the form then reopened it. Upon reopening, Access opened a message box asking me to "Enter Parameter Value" for HLBName. You could cancel through this, then use the list box as intended. However, I did not want the message box. Out of blissful ignorance I tried reversing the SQL sequence to:

Select "*" AS HLBID, "(All HLB Teams)" AS HLBName
FROM tblHLBTeams AS T1;

UNION

SELECT T1.HLBTeamID AS HLBID, T1.TeamName AS HLBName
FROM tblHLBTeams AS T1
ORDER BY HLBName;

For whatever reason, this eliminated the message box. Might someone provide insight as to why?