By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,846 Members | 1,637 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,846 IT Pros & Developers. It's quick & easy.

Adding in an "All" option in a combo Box

P: n/a
I first read about this in mvps.org, but the code he gives doesn't seem
to work for my form. I"ll post my code below. I'd love it if someone
could point out the error in my code. BTW, this is my very first time
here and I will look forward to sifting through all of the posts and
answers. Thanks in advance!!!
I don't get an error when using the value option...but if I switch to a
Table/Query, I get the following error:

The number of columns in the two selected tables or queries of a union
query to not match.

My code looks like this:

SELECT auditor_calc_list.auditor From auditor_calc_list UNION Select
Null as AllChoice , "(All)" as Bogus FROM auditor_calc_list;

Mar 16 '06 #1
Share this Question
Share on Google+
6 Replies


P: n/a
"Mark" <mm****@gmail.com> wrote in
news:11**********************@j52g2000cwj.googlegr oups.com:
I first read about this in mvps.org, but the code he gives
doesn't seem to work for my form. I"ll post my code below.
I'd love it if someone could point out the error in my code.
BTW, this is my very first time here and I will look forward
to sifting through all of the posts and answers. Thanks in
advance!!!
I don't get an error when using the value option...but if I
switch to a Table/Query, I get the following error:

The number of columns in the two selected tables or queries of
a union query to not match.

My code looks like this:

SELECT
auditor_calc_list.auditor
From auditor_calc_list
UNION
Select
Null as AllChoice ,
"(All)" as Bogus
FROM auditor_calc_list;

There is one field in the top value, and two in the bottom,
which is exactly what the error message says.

try:
SELECT
auditor_calc_list.auditor as Hidden
auditor_calc_list.auditor AS Visible
From auditor_calc_list
UNION
Select
Null as AllChoice ,
"(All)" as Bogus
FROM auditor_calc_list;

then set the combobox for 2 columns, and make the first one Zero
width.

--
Bob Quintal

PA is y I've altered my email address.
Mar 16 '06 #2

P: n/a
"Mark" <mm****@gmail.com> wrote in message
news:11**********************@j52g2000cwj.googlegr oups.com...
I first read about this in mvps.org, but the code he gives doesn't seem
to work for my form. I"ll post my code below. I'd love it if someone
could point out the error in my code. BTW, this is my very first time
here and I will look forward to sifting through all of the posts and
answers. Thanks in advance!!!
I don't get an error when using the value option...but if I switch to a
Table/Query, I get the following error:

The number of columns in the two selected tables or queries of a union
query to not match.

My code looks like this:

SELECT auditor_calc_list.auditor From auditor_calc_list UNION Select
Null as AllChoice , "(All)" as Bogus FROM auditor_calc_list;


Try this:

SELECT Auditor FROM Auditor_Calc_List
UNION
SELECT "(All)" as Bogus FROM Auditor_Calc_List

Fred Zuckerman

Mar 16 '06 #3

P: n/a
Per Mark:
My code looks like this:

SELECT auditor_calc_list.auditor From auditor_calc_list UNION Select
Null as AllChoice , "(All)" as Bogus FROM auditor_calc_list;


A technique that's worked well for me in avoiding errors like the one in the SQL
above:
------------------------------------------------------------------
1) Create an MS Access query for the first part of the union.
Call it qryAuditorDropdown (SQL1).

2) Create an MS Access query for the second part of the union.
Call it qryAuditorDropdown (SQL2).

3) Test each query individually and confirm that it does what
you want it to do.

4) Create a third query: qryAuditorDropdown.

5) In SQL view, just paste in the SQL from the first two
queries with a UNION in-between.

6) Test it to see if it does what you want. Here's where
JET will tell you about the mismatch in number of fields
returned by each query.

7) If it doesn't do what you want (i.e. there's a sequencing
issue) rename qryAuditorDropdown to qryAuditorDropdown1,
create qryAuditorDropdown2 based on ...1 and add sorting
as needed.
--------------------------------------------------------------------

In the end, you wind up with 3 or 4 queries instead of none, but
the functionality is encapsulated and labeled for everybody to see
and it's modularized for testing.
--
PeteCresswell
Mar 17 '06 #4

P: n/a
"(PeteCresswell)" <x@y.Invalid> wrote in
news:6s********************************@4ax.com:
A technique that's worked well for me in avoiding errors like the
one in the SQL above:
------------------------------------------------------------------
1) Create an MS Access query for the first part of the union.
Call it qryAuditorDropdown (SQL1).

2) Create an MS Access query for the second part of the union.
Call it qryAuditorDropdown (SQL2).

3) Test each query individually and confirm that it does what
you want it to do.

4) Create a third query: qryAuditorDropdown.

5) In SQL view, just paste in the SQL from the first two
queries with a UNION in-between.

6) Test it to see if it does what you want. Here's where
JET will tell you about the mismatch in number of fields
returned by each query.

7) If it doesn't do what you want (i.e. there's a sequencing
issue) rename qryAuditorDropdown to qryAuditorDropdown1,
create qryAuditorDropdown2 based on ...1 and add sorting
as needed.
-------------------------------------------------------------------
-

In the end, you wind up with 3 or 4 queries instead of none, but
the functionality is encapsulated and labeled for everybody to see
and it's modularized for testing.


Why would you save *any* queries to do this, unless you were going
to utilize the same rowsource in multiple combo boxes?

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Mar 17 '06 #5

P: n/a
Per David W. Fenton:
Why would you save *any* queries to do this, unless you were going
to utilize the same rowsource in multiple combo boxes?


Ease of maintenance, speed of development, debuggability.
Makes the code clearer (to me, at least... YYMV)
--
PeteCresswell
Mar 17 '06 #6

P: n/a
"(PeteCresswell)" <x@y.Invalid> wrote in
news:7m********************************@4ax.com:
Per David W. Fenton:
Why would you save *any* queries to do this, unless you were going
to utilize the same rowsource in multiple combo boxes?


Ease of maintenance, speed of development, debuggability.
Makes the code clearer (to me, at least... YYMV)


How does saving the queries do *any* of these things? I don't see
any ease of maintenance coming from it at all -- you're
proliferating saved queries that don't need to be saved, which
increases the maintenance load. As to speed and debuggability, you
don't have to save the queries to get either of those benefits. As
to making "code" clearer, who is talking about code at all? There's
no code involved in creating a SQL rowsource.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Mar 17 '06 #7

This discussion thread is closed

Replies have been disabled for this discussion.