Trouble adding (All) to a combo

[Guest]

I have tried to follow the advice of Dev Ashish with other info recently in
this site to add (All) to my combo box. No luck yet, I think because of the
way the combo's RowSource is set up.

The cboB's Row Source is determined by the AfterUpdate() event of another
combo,cboA, with code like....

Me!cboB.RowSource = "SELECT [qryAB]. FROM [qryAB]
WHERE <= " & Me!cboA.Column(3) & ";"
Me!cboB.Value = [cboB].[ItemData](0)


typically, the values in the combo might be 1,2,3,4,5 with the combo
initially showing 1

I want the values to be (All),1,2,3,4,5 with the combo initially showing
(All)

It seems this can be done with a UNION in the above code, but i can't seem
to get the syntax correct. Any help is greatly appreciated.

 

[Marshall Barton]

I have tried to follow the advice of Dev Ashish with other info recently in
this site to add (All) to my combo box. No luck yet, I think because of the
way the combo's RowSource is set up.

The cboB's Row Source is determined by the AfterUpdate() event of another
combo,cboA, with code like....

Me!cboB.RowSource = "SELECT [qryAB]. FROM [qryAB]
WHERE <= " & Me!cboA.Column(3) & ";"
Me!cboB.Value = [cboB].[ItemData](0)


typically, the values in the combo might be 1,2,3,4,5 with the combo
initially showing 1

I want the values to be (All),1,2,3,4,5 with the combo initially showing
(All)

It seems this can be done with a UNION in the above code, but i can't seem
to get the syntax correct. Any help is greatly appreciated.

Me!cboB.RowSource = "SELECT [qryAB]. FROM qryAB " & _
& "WHERE <=" & Me!cboA.Column(3) & _
& "UNION SELECT "ALL" FROM FROM [any small table] "

 

[Guest]

Hello Marshall - thank you for takiong the time to answer

The code...

Me!cboB.RowSource = "SELECT [qryAB]. FROM qryAB " & _
& "WHERE <=" & Me!cboA.Column(3) & _
& "UNION SELECT "ALL" FROM FROM [any small table] "

is giving me a compile error (Expected: end of statement) at the "ALL". Is
there an easy fix? Also, when you mentioned [any small table], is it
necessary for some reason to make a new table? (I was using tblAB that qryAB
is based on) .FWIW, I removed the duplicate FROM. Also, for my own
learning, is a ";" not required at the end?

thanks again
--
cinnie

I have tried to follow the advice of Dev Ashish with other info recently in
this site to add (All) to my combo box. No luck yet, I think because of the
way the combo's RowSource is set up.

The cboB's Row Source is determined by the AfterUpdate() event of another
combo,cboA, with code like....

Me!cboB.RowSource = "SELECT [qryAB]. FROM [qryAB]
WHERE <= " & Me!cboA.Column(3) & ";"
Me!cboB.Value = [cboB].[ItemData](0)


typically, the values in the combo might be 1,2,3,4,5 with the combo
initially showing 1

I want the values to be (All),1,2,3,4,5 with the combo initially showing
(All)

It seems this can be done with a UNION in the above code, but i can't seem
to get the syntax correct. Any help is greatly appreciated.

Me!cboB.RowSource = "SELECT [qryAB]. FROM qryAB " & _
& "WHERE <=" & Me!cboA.Column(3) & _
& "UNION SELECT "ALL" FROM FROM [any small table] "

 

[Marshall Barton]

Hello Marshall - thank you for takiong the time to answer

The code...

Me!cboB.RowSource = "SELECT [qryAB]. FROM qryAB " & _
& "WHERE <=" & Me!cboA.Column(3) & _
& "UNION SELECT "ALL" FROM FROM [any small table] "

is giving me a compile error (Expected: end of statement) at the "ALL". Is
there an easy fix? Also, when you mentioned [any small table], is it
necessary for some reason to make a new table? (I was using tblAB that qryAB
is based on) .FWIW, I removed the duplicate FROM. Also, for my own
learning, is a ";" not required at the end?

Typing and proofreading are not in my primary skill set ;-)

In addition to the FROM FROM glitch, there is a missing
space on the last line:

. . .
& " UNION SELECT "ALL" FROM [any small table] "

You can use any table you want. It's just that Access might
have to do a lot of work to remove the duplicate records if
a table with a large number of records is used. Most
databases have a table with just one record for storing
miscellaneous values. If you use a table with one record,
the query can be made much more efficient by using UNION ALL
instead of UNION.

In Access/Jet the semicolon is optional. I choose to not
use it.

 

[Guest]

thanks!
--
cinnie

Hello Marshall - thank you for takiong the time to answer

The code...

Me!cboB.RowSource = "SELECT [qryAB]. FROM qryAB " & _
& "WHERE <=" & Me!cboA.Column(3) & _
& "UNION SELECT "ALL" FROM FROM [any small table] "

is giving me a compile error (Expected: end of statement) at the "ALL". Is
there an easy fix? Also, when you mentioned [any small table], is it
necessary for some reason to make a new table? (I was using tblAB that qryAB
is based on) .FWIW, I removed the duplicate FROM. Also, for my own
learning, is a ";" not required at the end?

Typing and proofreading are not in my primary skill set ;-)

In addition to the FROM FROM glitch, there is a missing
space on the last line:

. . .
& " UNION SELECT "ALL" FROM [any small table] "

You can use any table you want. It's just that Access might
have to do a lot of work to remove the duplicate records if
a table with a large number of records is used. Most
databases have a table with just one record for storing
miscellaneous values. If you use a table with one record,
the query can be made much more efficient by using UNION ALL
instead of UNION.

In Access/Jet the semicolon is optional. I choose to not
use it.