Prev: Force user to click on Main Form First??
Next: Allen Browne’s date range vba for more reports
From: learning_codes on 1 May 2010 11:24
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

From: Tom van Stiphout on 1 May 2010 14:52
On Sat, 1 May 2010 08:24:13 -0700 (PDT), "learning_codes(a)hotmail.com"
<learning_codes(a)hotmail.com> wrote:

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


>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
From: KenSheridan via AccessMonster.com on 3 May 2010 13:22
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

learning_codes(a)hotmail.com wrote:
>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

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/201005/1

 | 
Pages: 1
Prev: Force user to click on Main Form First??
Next: Allen Browne’s date range vba for more reports