Form: Combo selection with "ALL"

I have a Db with a form that reads the data table. On the form I have two
combo box that feeds the other combo box. What I need now is to have All as
options for the two. To clarify, here is the example:

The 1st combo box selects - Sites i.e. NY, Boston, London
Within each site there are a number of groups, so the e.g.
NY has G1, G2
Boston has G3, G4
London G5

Now the 2nd Combo box based on the site location, gives you the options for
that site i.e. If you selected NY, it gives you G1 and G2 selections.

Now what I need is to add an option to both combo boxes "All" i.e.
Combo1 = All (should give all the 3 sites)
Combo2 = All (should give all the groups)

Could anyone give me directions, how to go about adding this "All" option to
the combo boxes on the form.

Thanks in advance for your assistance.

MSA

This site has the info you need:
http://www.mvps.org/access/forms/frm0043.htm

"MSA" wrote:

I have a Db with a form that reads the data table. On the form I have two
combo box that feeds the other combo box. What I need now is to have All as
options for the two. To clarify, here is the example:

The 1st combo box selects - Sites i.e. NY, Boston, London
Within each site there are a number of groups, so the e.g.
NY has G1, G2
Boston has G3, G4
London G5

Now the 2nd Combo box based on the site location, gives you the options for
that site i.e. If you selected NY, it gives you G1 and G2 selections.

Now what I need is to add an option to both combo boxes "All" i.e.
Combo1 = All (should give all the 3 sites)
Combo2 = All (should give all the groups)

Could anyone give me directions, how to go about adding this "All" option to
the combo boxes on the form.

Thanks in advance for your assistance.

MSA

Hi Klatuu,

Thx for your input. Unfortunately I am using -

RowSourceType = Table/Query

So the suggested approach at the site is dealing with RowSourceType = Value
List.

Please do advise in this case how to proceed with "All".

Thanks!
MSA
"Klatuu" wrote:

This site has the info you need:
http://www.mvps.org/access/forms/frm0043.htm

"MSA" wrote:

I have a Db with a form that reads the data table. On the form I have two
combo box that feeds the other combo box. What I need now is to have All as
options for the two. To clarify, here is the example:

The 1st combo box selects - Sites i.e. NY, Boston, London
Within each site there are a number of groups, so the e.g.
NY has G1, G2
Boston has G3, G4
London G5

Now the 2nd Combo box based on the site location, gives you the options for
that site i.e. If you selected NY, it gives you G1 and G2 selections.

Now what I need is to add an option to both combo boxes "All" i.e.
Combo1 = All (should give all the 3 sites)
Combo2 = All (should give all the groups)

Could anyone give me directions, how to go about adding this "All" option to
the combo boxes on the form.

Thanks in advance for your assistance.

MSA

Have a careful look at the site. The top half of the instructions address
your situation.

"MSA" wrote in message
...
Hi Klatuu,

Thx for your input. Unfortunately I am using -

RowSourceType = Table/Query

So the suggested approach at the site is dealing with RowSourceType =
Value
List.

Please do advise in this case how to proceed with "All".

Thanks!
MSA
"Klatuu" wrote:

This site has the info you need:
http://www.mvps.org/access/forms/frm0043.htm

"MSA" wrote:

I have a Db with a form that reads the data table. On the form I have
two
combo box that feeds the other combo box. What I need now is to have
All as
options for the two. To clarify, here is the example:

The 1st combo box selects - Sites i.e. NY, Boston, London
Within each site there are a number of groups, so the e.g.
NY has G1, G2
Boston has G3, G4
London G5

Now the 2nd Combo box based on the site location, gives you the options
for
that site i.e. If you selected NY, it gives you G1 and G2 selections.

Now what I need is to add an option to both combo boxes "All" i.e.
Combo1 = All (should give all the 3 sites)
Combo2 = All (should give all the groups)

Could anyone give me directions, how to go about adding this "All"
option to
the combo boxes on the form.

Thanks in advance for your assistance.

MSA

Also I forgot to mention earlier that my combo box is unbounded. So its only
picking up the sites - NY, Boston, London individually. I need to add All on
top, which if selected should show all the sites.

"MSA" wrote:

Hi Klatuu,

Thx for your input. Unfortunately I am using -

RowSourceType = Table/Query

So the suggested approach at the site is dealing with RowSourceType = Value
List.

Please do advise in this case how to proceed with "All".

Thanks!
MSA
"Klatuu" wrote:

This site has the info you need:
http://www.mvps.org/access/forms/frm0043.htm

"MSA" wrote:

I have a Db with a form that reads the data table. On the form I have two
combo box that feeds the other combo box. What I need now is to have All as
options for the two. To clarify, here is the example:

The 1st combo box selects - Sites i.e. NY, Boston, London
Within each site there are a number of groups, so the e.g.
NY has G1, G2
Boston has G3, G4
London G5

Now the 2nd Combo box based on the site location, gives you the options for
that site i.e. If you selected NY, it gives you G1 and G2 selections.

Now what I need is to add an option to both combo boxes "All" i.e.
Combo1 = All (should give all the 3 sites)
Combo2 = All (should give all the groups)

Could anyone give me directions, how to go about adding this "All" option to
the combo boxes on the form.

Thanks in advance for your assistance.

MSA

You didn't read the whole page at the site. The large print in the upper
portion describes that. Now, you may be thinking that doesn't apply to you
because you are using a query. Open the query in design view, switch to SQL
view, copy and paste it into the Row Source property of the combo, and modify
it to include the code to add the ALL to your list.

"MSA" wrote:

Hi Klatuu,

Thx for your input. Unfortunately I am using -

RowSourceType = Table/Query

So the suggested approach at the site is dealing with RowSourceType = Value
List.

Please do advise in this case how to proceed with "All".

Thanks!
MSA
"Klatuu" wrote:

This site has the info you need:
http://www.mvps.org/access/forms/frm0043.htm

"MSA" wrote:

I have a Db with a form that reads the data table. On the form I have two
combo box that feeds the other combo box. What I need now is to have All as
options for the two. To clarify, here is the example:

The 1st combo box selects - Sites i.e. NY, Boston, London
Within each site there are a number of groups, so the e.g.
NY has G1, G2
Boston has G3, G4
London G5

Now the 2nd Combo box based on the site location, gives you the options for
that site i.e. If you selected NY, it gives you G1 and G2 selections.

Now what I need is to add an option to both combo boxes "All" i.e.
Combo1 = All (should give all the 3 sites)
Combo2 = All (should give all the groups)

Could anyone give me directions, how to go about adding this "All" option to
the combo boxes on the form.

Thanks in advance for your assistance.

MSA

Hi Bruce,

Thx, yes I did and it did not work. Here is my SQL statement for the 1st
combo box.
SELECT xA_rt_CC_Site.Site
FROM xA_rt_CC_Site
GROUP BY xA_rt_CC_Site.Site;

Any help would be greatly appreciated, thx.

MSA

"BruceM" wrote:

Have a careful look at the site. The top half of the instructions address
your situation.

"MSA" wrote in message
...
Hi Klatuu,

Thx for your input. Unfortunately I am using -

RowSourceType = Table/Query

So the suggested approach at the site is dealing with RowSourceType =
Value
List.

Please do advise in this case how to proceed with "All".

Thanks!
MSA
"Klatuu" wrote:

This site has the info you need:
http://www.mvps.org/access/forms/frm0043.htm

"MSA" wrote:

I have a Db with a form that reads the data table. On the form I have
two
combo box that feeds the other combo box. What I need now is to have
All as
options for the two. To clarify, here is the example:

The 1st combo box selects - Sites i.e. NY, Boston, London
Within each site there are a number of groups, so the e.g.
NY has G1, G2
Boston has G3, G4
London G5

Now the 2nd Combo box based on the site location, gives you the options
for
that site i.e. If you selected NY, it gives you G1 and G2 selections.

Now what I need is to add an option to both combo boxes "All" i.e.
Combo1 = All (should give all the 3 sites)
Combo2 = All (should give all the groups)

Could anyone give me directions, how to go about adding this "All"
option to
the combo boxes on the form.

Thanks in advance for your assistance.

MSA

I was just pointing out something you may have overlooked at the site. I
see that Klatuu has responded, so I will refer you to that reply for any
further help you need with this.

"BruceM" wrote in message
...
Have a careful look at the site. The top half of the instructions address
your situation.

"MSA" wrote in message
...
Hi Klatuu,

Thx for your input. Unfortunately I am using -

RowSourceType = Table/Query

So the suggested approach at the site is dealing with RowSourceType =
Value
List.

Please do advise in this case how to proceed with "All".

Thanks!
MSA
"Klatuu" wrote:

This site has the info you need:
http://www.mvps.org/access/forms/frm0043.htm

"MSA" wrote:

I have a Db with a form that reads the data table. On the form I have
two
combo box that feeds the other combo box. What I need now is to have
All as
options for the two. To clarify, here is the example:

The 1st combo box selects - Sites i.e. NY, Boston, London
Within each site there are a number of groups, so the e.g.
NY has G1, G2
Boston has G3, G4
London G5

Now the 2nd Combo box based on the site location, gives you the
options for
that site i.e. If you selected NY, it gives you G1 and G2 selections.

Now what I need is to add an option to both combo boxes "All" i.e.
Combo1 = All (should give all the 3 sites)
Combo2 = All (should give all the groups)

Could anyone give me directions, how to go about adding this "All"
option to
the combo boxes on the form.

Thanks in advance for your assistance.

MSA

Thx, yes I did and it did not work. Here is my original SQL statement for the
1st
combo box.
SELECT xA_rt_CC_Site.Site
FROM xA_rt_CC_Site
GROUP BY xA_rt_CC_Site.Site;

and the one I tried:
SELECT xA_rt_CC_Site.Site
FROM xA_rt_CC_Site UNION Select Null as AllChoice , "(All)"as Bogus From
xA_rt_CC_Site
GROUP BY xA_rt_CC_Site.Site;

I kep get an error msg: The number of columns in the two selected tables or
queries of a union query do not match

First off I have only one column and one table or query - all I need is the
combination of the rows of that coumn together (all sites).

Sorry for the inconvenience, thx.

MSA

"Klatuu" wrote:

You didn't read the whole page at the site. The large print in the upper
portion describes that. Now, you may be thinking that doesn't apply to you
because you are using a query. Open the query in design view, switch to SQL
view, copy and paste it into the Row Source property of the combo, and modify
it to include the code to add the ALL to your list.

"MSA" wrote:

Hi Klatuu,

Thx for your input. Unfortunately I am using -

RowSourceType = Table/Query

So the suggested approach at the site is dealing with RowSourceType = Value
List.

Please do advise in this case how to proceed with "All".

Thanks!
MSA
"Klatuu" wrote:

This site has the info you need:
http://www.mvps.org/access/forms/frm0043.htm

"MSA" wrote:

I have a Db with a form that reads the data table. On the form I have two
combo box that feeds the other combo box. What I need now is to have All as
options for the two. To clarify, here is the example:

The 1st combo box selects - Sites i.e. NY, Boston, London
Within each site there are a number of groups, so the e.g.
NY has G1, G2
Boston has G3, G4
London G5

Now the 2nd Combo box based on the site location, gives you the options for
that site i.e. If you selected NY, it gives you G1 and G2 selections.

Now what I need is to add an option to both combo boxes "All" i.e.
Combo1 = All (should give all the 3 sites)
Combo2 = All (should give all the groups)

Could anyone give me directions, how to go about adding this "All" option to
the combo boxes on the form.

Thanks in advance for your assistance.

MSA

The SQL used for this type of query is a Union query. A Union query required
the same number of fields in both tables. Since you have only one field in
your the first select, you can have only one field in the second select.

Change it to:
UNION Select "(All)"as Bogus From xA_rt_CC_Site

Here is the code from one of my forms:

SELECT EmployeeNum FROM tbllkEmployee UNION Select "(All)" as Bogus From
tbllkEmployee;

"MSA" wrote:

Thx, yes I did and it did not work. Here is my original SQL statement for the
1st
combo box.
SELECT xA_rt_CC_Site.Site
FROM xA_rt_CC_Site
GROUP BY xA_rt_CC_Site.Site;

and the one I tried:
SELECT xA_rt_CC_Site.Site
FROM xA_rt_CC_Site UNION Select Null as AllChoice , "(All)"as Bogus From
xA_rt_CC_Site
GROUP BY xA_rt_CC_Site.Site;

I kep get an error msg: The number of columns in the two selected tables or
queries of a union query do not match

First off I have only one column and one table or query - all I need is the
combination of the rows of that coumn together (all sites).

Sorry for the inconvenience, thx.

MSA

"Klatuu" wrote:

You didn't read the whole page at the site. The large print in the upper
portion describes that. Now, you may be thinking that doesn't apply to you
because you are using a query. Open the query in design view, switch to SQL
view, copy and paste it into the Row Source property of the combo, and modify
it to include the code to add the ALL to your list.

"MSA" wrote:

Hi Klatuu,

Thx for your input. Unfortunately I am using -

RowSourceType = Table/Query

So the suggested approach at the site is dealing with RowSourceType = Value
List.

Please do advise in this case how to proceed with "All".

Thanks!
MSA
"Klatuu" wrote:

This site has the info you need:
http://www.mvps.org/access/forms/frm0043.htm

"MSA" wrote:

I have a Db with a form that reads the data table. On the form I have two
combo box that feeds the other combo box. What I need now is to have All as
options for the two. To clarify, here is the example:

The 1st combo box selects - Sites i.e. NY, Boston, London
Within each site there are a number of groups, so the e.g.
NY has G1, G2
Boston has G3, G4
London G5

Now the 2nd Combo box based on the site location, gives you the options for
that site i.e. If you selected NY, it gives you G1 and G2 selections.

Now what I need is to add an option to both combo boxes "All" i.e.
Combo1 = All (should give all the 3 sites)
Combo2 = All (should give all the groups)

Could anyone give me directions, how to go about adding this "All" option to
the combo boxes on the form.

Thanks in advance for your assistance.

MSA