CBO List

[learning_codes]

Hi,

I create combo list that has a list of sports. The combo list table
has

- hockey
- baseball
- Bowling
- Tennis

I don't want to add "all sports" to combo list table. Is there a
way
to add "all sports" to combo list on the form instead of table.
My query has a field called SPORT. If I want to select "ALL sports"
from the form
then I get All sport names.

Your help would be much appreciated

Thanks

 

[Tom van Stiphout]

On Sat, 1 May 2010 08:24:13 -0700 (PDT), "(e-mail address removed)"

Yes. I'm guessing your combobox currently has a RowSource that is the
table name. Change that to a query name instead. Create a new query,
switch to SQL view (that's the only view in which you can write a
union query), and write something like this:
select -1, "All Sports" from tblSports
union all
select SportID, SportName from tblSports

-Tom.
Microsoft Access MVP

 

[KenSheridan via AccessMonster.com]

For the combo box's RowSource property use a UNION operation like this:

SELECT "ALL Sports" AS Sport, 0 As SortColumn
FROM Sports
UNION
SELECT Sport, 1
FROM Sports
ORDER BY SortColumn, Sport;

In your case the SortColumn column isn't at present necessary as 'ALL Sports'
will sort first in the list anyway, but should you add 'Aardvark Wrestling'
to the list of sports you'd need it .

In a query which references the combo box as a parameter you'd change the
parameter on the Sport column from something like:

[Forms]![YourForm]![YourComboBox]

to:

[Forms]![YourForm]![YourComboBox] OR [Forms]![YourForm]![YourComboBox]
= "ALL Sports"

This should be entered as a single line; it will probably have been split
over two by your news reader. Then if a particular sport is selected in the
combo box the query would be restricted to that sport; if 'ALL Sports' is
selected it would return all rows.

BTW if you change the parameter like this in design view and save the query,
when you reopen it again in design view you'll find Access has moved things
around. Don't worry, the underlying logic is the same and it will work in
the same way.

Ken Sheridan
Stafford, England