A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Using Forms
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

CBO List



 
 
Thread Tools Display Modes
  #1  
Old May 1st, 2010, 04:24 PM posted to microsoft.public.access.forms
[email protected]
external usenet poster
 
Posts: 96
Default CBO List

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

  #2  
Old May 1st, 2010, 07:52 PM posted to microsoft.public.access.forms
Tom van Stiphout[_2_]
external usenet poster
 
Posts: 1,653
Default CBO List

On Sat, 1 May 2010 08:24:13 -0700 (PDT), "
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

  #3  
Old May 3rd, 2010, 06:22 PM posted to microsoft.public.access.forms
KenSheridan via AccessMonster.com
external usenet poster
 
Posts: 1,610
Default CBO List

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

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/For...forms/201005/1

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 12:58 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.