Problems with adding "All" value to combo box.

W

Wendy

Hi Guru's
I'm really sorry, you must be so sick of this question. But I've searched
extensivley for a solution to my "hacked" code without any luck.

I just want to add the <All> option to my combo box. <sniff>

The value is set in the rowsource of my combo box located on a form

Any pointers on where i'm going wrong. Thanks again

SELECT tblTopic_Sub.Topic_Sub_Description, tblTopic_Sub.Topic_Sub_Main_ID,
tblTopic_Sub.Topic_Sub_Active FROM tblTopic_Sub UNION SELECT '(All)', null
FROM tblTopic_Sub WHERE
(((tblTopic_Sub.Topic_Sub_Main_ID)=[Forms]![frmTopic_MCQ_Mainform]![Topic_MCQ_Main_ID]) AND ((tblTopic_Sub.Topic_Sub_Active)=Yes));
 
P

perry

SELECT tblTopic_Sub.Topic_Sub_Description, tblTopic_Sub.Topic_Sub_Main_ID,
tblTopic_Sub.Topic_Sub_Active FROM tblTopic_Sub UNION SELECT '(All)', null,
0
FROM tblTopic_Sub WHERE
(((tblTopic_Sub.Topic_Sub_Main_ID)=[Forms]![frmTopic_MCQ_Mainform]![Topic_MCQ_Main_ID]) AND ((tblTopic_Sub.Topic_Sub_Active)=Yes));

Note the value 0 added to null after the UNION
Union queries require two tables containing same number of fields.
Y're had one column missing.

Kindly repost if this doesn't remedy
 
R

Rod Plastow

How about a low value leading character before the (All) and an ORDER BY
clause to get the (All) line to the its conventional place at the top of the
list?

Rod

perry said:
SELECT tblTopic_Sub.Topic_Sub_Description, tblTopic_Sub.Topic_Sub_Main_ID,
tblTopic_Sub.Topic_Sub_Active FROM tblTopic_Sub UNION SELECT '(All)', null,
0
FROM tblTopic_Sub WHERE
(((tblTopic_Sub.Topic_Sub_Main_ID)=[Forms]![frmTopic_MCQ_Mainform]![Topic_MCQ_Main_ID]) AND ((tblTopic_Sub.Topic_Sub_Active)=Yes));

Note the value 0 added to null after the UNION
Union queries require two tables containing same number of fields.
Y're had one column missing.

Kindly repost if this doesn't remedy
--
Krgrds,
Perry


Wendy said:
Hi Guru's
I'm really sorry, you must be so sick of this question. But I've searched
extensivley for a solution to my "hacked" code without any luck.

I just want to add the <All> option to my combo box. <sniff>

The value is set in the rowsource of my combo box located on a form

Any pointers on where i'm going wrong. Thanks again

SELECT tblTopic_Sub.Topic_Sub_Description, tblTopic_Sub.Topic_Sub_Main_ID,
tblTopic_Sub.Topic_Sub_Active FROM tblTopic_Sub UNION SELECT '(All)', null
FROM tblTopic_Sub WHERE
(((tblTopic_Sub.Topic_Sub_Main_ID)=[Forms]![frmTopic_MCQ_Mainform]![Topic_MCQ_Main_ID]) AND ((tblTopic_Sub.Topic_Sub_Active)=Yes));
Click to expand...
Click to expand...
 
D

Douglas J. Steele

Hate to disagree with what you've already been told, but the WHERE clause
should be on the first subselect, not the second:

SELECT Topic_Sub_Description, Topic_Sub_Main_ID, Topic_Sub_Active
FROM tblTopic_Sub
WHERE Topic_Sub_Main_ID=[Forms]![frmTopic_MCQ_Mainform]![Topic_MCQ_Main_ID]
AND Topic_Sub_Active=True
UNION
SELECT '(All)', null, 0 FROM tblTopic_Sub
ORDER BY 1

I'm assuming that Topic_Sub_Active is a Yes/No field. If so, you should be
using True, not Yes. (Yeah, I know it doesn't make sense to call it a Yes/No
field, but to expect values of True and False!)
 
W

Wendy

hi Perry, Rod & Douglas
Many many many thanks for the responses. I've run with the final suggestion
and it words an abosolute treat.

Kind regards
Wendy

Douglas J. Steele said:
Hate to disagree with what you've already been told, but the WHERE clause
should be on the first subselect, not the second:

SELECT Topic_Sub_Description, Topic_Sub_Main_ID, Topic_Sub_Active
FROM tblTopic_Sub
WHERE Topic_Sub_Main_ID=[Forms]![frmTopic_MCQ_Mainform]![Topic_MCQ_Main_ID]
AND Topic_Sub_Active=True
UNION
SELECT '(All)', null, 0 FROM tblTopic_Sub
ORDER BY 1

I'm assuming that Topic_Sub_Active is a Yes/No field. If so, you should be
using True, not Yes. (Yeah, I know it doesn't make sense to call it a Yes/No
field, but to expect values of True and False!)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Wendy said:
Hi Guru's
I'm really sorry, you must be so sick of this question. But I've searched
extensivley for a solution to my "hacked" code without any luck.

I just want to add the <All> option to my combo box. <sniff>

The value is set in the rowsource of my combo box located on a form

Any pointers on where i'm going wrong. Thanks again

SELECT tblTopic_Sub.Topic_Sub_Description, tblTopic_Sub.Topic_Sub_Main_ID,
tblTopic_Sub.Topic_Sub_Active FROM tblTopic_Sub UNION SELECT '(All)', null
FROM tblTopic_Sub WHERE
(((tblTopic_Sub.Topic_Sub_Main_ID)=[Forms]![frmTopic_MCQ_Mainform]![Topic_MCQ_Main_ID])
AND ((tblTopic_Sub.Topic_Sub_Active)=Yes));
Click to expand...
Click to expand...
 
Top