Adding <All> as options in 2 combo boxes

[consonanza]

I am working on a report filter form. It has 2 combo boxes
(cmboSelectSubject and cmboSelectCategory) to select criteria.
Selecting an entry in combo 1
restricts the options available in combo 2.

The row source for combo2 is:

SELECT DISTINCT tblComplaintCategory.fldComplaintCategory,
tblComplaintCategory.fldComplaintCategoryID,
tblComplaintSubjects.fldComplaintSubjectID
FROM (tblComplaints INNER JOIN tblComplaintCategory ON
tblComplaints.fldComplaintCategory =
tblComplaintCategory.fldComplaintCategoryID) INNER JOIN
tblComplaintSubjects ON tblComplaints.fldComplaintSubject =
tblComplaintSubjects.fldComplaintSubjectID
WHERE
(((tblComplaintSubjects.fldComplaintSubjectID)=[Forms]![frmReports]![cmboSelectSubject]))
ORDER BY tblComplaintCategory.fldComplaintCategory;
Pressing OK opens the report and the SQL for the report is constructed
on the fly. This is straightforward enough, but I want to add an
option "All" in

both combo boxes. I understand how to build the code to get the "All"
option in one combo box:-

SELECT 0 as fldComplaintsubjectID, "[All]" as fldComplaintsubject from
tblComplaintsubjects UNION select
tblComplaintsubjects.fldComplaintsubjectID,
tblComplaintsubjects.fldComplaintsubject from tblComplaintsubjects
ORDER BY fldComplaintsubject;
- but the <row sourcecode for combo 2 won't work to restrict the
options in combo 2 when using the code for the "All" option in combo1.
How do I overcome this?

Secondly, I want to add an "All" option to combo2. How do I make this
work with the restriction aspect of combo1?

Finally, once I get the 2 "All" options working, how do I test for
their presence when the user selects tham both as options and presses
OK for the report?

Thanks for any help you can give.
Gordon

[Tom van Stiphout]

On 6 Dec 2006 02:23:38 -0800, co********@hotmail.co.uk wrote:

Read this article:
http://www.mvps.org/access/forms/frm0043.htm
And bookmark that site!

-Tom.

>I am working on a report filter form. It has 2 combo boxes
(cmboSelectSubject and cmboSelectCategory) to select criteria.
Selecting an entry in combo 1
restricts the options available in combo 2.

The row source for combo2 is:

SELECT DISTINCT tblComplaintCategory.fldComplaintCategory,
tblComplaintCategory.fldComplaintCategoryID,
tblComplaintSubjects.fldComplaintSubjectID
FROM (tblComplaints INNER JOIN tblComplaintCategory ON
tblComplaints.fldComplaintCategory =
tblComplaintCategory.fldComplaintCategoryID) INNER JOIN
tblComplaintSubjects ON tblComplaints.fldComplaintSubject =
tblComplaintSubjects.fldComplaintSubjectID
WHERE
(((tblComplaintSubjects.fldComplaintSubjectID)=[Forms]![frmReports]![cmboSelectSubject]))
ORDER BY tblComplaintCategory.fldComplaintCategory;
Pressing OK opens the report and the SQL for the report is constructed
on the fly. This is straightforward enough, but I want to add an
option "All" in

both combo boxes. I understand how to build the code to get the "All"
option in one combo box:-

SELECT 0 as fldComplaintsubjectID, "[All]" as fldComplaintsubject from
tblComplaintsubjects UNION select
tblComplaintsubjects.fldComplaintsubjectID,
tblComplaintsubjects.fldComplaintsubject from tblComplaintsubjects
ORDER BY fldComplaintsubject;
- but the <row sourcecode for combo 2 won't work to restrict the
options in combo 2 when using the code for the "All" option in combo1.
How do I overcome this?

Secondly, I want to add an "All" option to combo2. How do I make this
work with the restriction aspect of combo1?

Finally, once I get the 2 "All" options working, how do I test for
their presence when the user selects tham both as options and presses
OK for the report?

Thanks for any help you can give.
Gordon

[consonanza]

Tom,
Thanks for responding but your reply does not help me. If you read my
original post again, you will see that the second bit of code I quoted
uses the same kind of code (union query) that you referred me to. My
question is more complicated than how simply to add an "All" option to
a combo box.

Gordon

Tom van Stiphout wrote:

On 6 Dec 2006 02:23:38 -0800, co********@hotmail.co.uk wrote:

Read this article:
http://www.mvps.org/access/forms/frm0043.htm
And bookmark that site!

-Tom.

I am working on a report filter form. It has 2 combo boxes
(cmboSelectSubject and cmboSelectCategory) to select criteria.
Selecting an entry in combo 1
restricts the options available in combo 2.

The row source for combo2 is:

SELECT DISTINCT tblComplaintCategory.fldComplaintCategory,
tblComplaintCategory.fldComplaintCategoryID,
tblComplaintSubjects.fldComplaintSubjectID
FROM (tblComplaints INNER JOIN tblComplaintCategory ON
tblComplaints.fldComplaintCategory =
tblComplaintCategory.fldComplaintCategoryID) INNER JOIN
tblComplaintSubjects ON tblComplaints.fldComplaintSubject =
tblComplaintSubjects.fldComplaintSubjectID
WHERE
(((tblComplaintSubjects.fldComplaintSubjectID)=[Forms]![frmReports]![cmboSelectSubject]))
ORDER BY tblComplaintCategory.fldComplaintCategory;
Pressing OK opens the report and the SQL for the report is constructed
on the fly. This is straightforward enough, but I want to add an
option "All" in

both combo boxes. I understand how to build the code to get the "All"
option in one combo box:-

SELECT 0 as fldComplaintsubjectID, "[All]" as fldComplaintsubject from
tblComplaintsubjects UNION select
tblComplaintsubjects.fldComplaintsubjectID,
tblComplaintsubjects.fldComplaintsubject from tblComplaintsubjects
ORDER BY fldComplaintsubject;
- but the <row sourcecode for combo 2 won't work to restrict the
options in combo 2 when using the code for the "All" option in combo1.
How do I overcome this?

Secondly, I want to add an "All" option to combo2. How do I make this
work with the restriction aspect of combo1?

Finally, once I get the 2 "All" options working, how do I test for
their presence when the user selects tham both as options and presses
OK for the report?

Thanks for any help you can give.
Gordon

[consonanza]

Tom,
Thanks for responding but your reply does not help me. If you read my
original post again, you will see that the second bit of code I quoted
uses the same kind of code (union query) that you referred me to. My
question is more complicated than how simply to add an "All" option to
a combo box.

Gordon

Tom van Stiphout wrote:

On 6 Dec 2006 02:23:38 -0800, co********@hotmail.co.uk wrote:

Read this article:
http://www.mvps.org/access/forms/frm0043.htm
And bookmark that site!

-Tom.

I am working on a report filter form. It has 2 combo boxes
(cmboSelectSubject and cmboSelectCategory) to select criteria.
Selecting an entry in combo 1
restricts the options available in combo 2.

The row source for combo2 is:

SELECT DISTINCT tblComplaintCategory.fldComplaintCategory,
tblComplaintCategory.fldComplaintCategoryID,
tblComplaintSubjects.fldComplaintSubjectID
FROM (tblComplaints INNER JOIN tblComplaintCategory ON
tblComplaints.fldComplaintCategory =
tblComplaintCategory.fldComplaintCategoryID) INNER JOIN
tblComplaintSubjects ON tblComplaints.fldComplaintSubject =
tblComplaintSubjects.fldComplaintSubjectID
WHERE
(((tblComplaintSubjects.fldComplaintSubjectID)=[Forms]![frmReports]![cmboSelectSubject]))
ORDER BY tblComplaintCategory.fldComplaintCategory;
Pressing OK opens the report and the SQL for the report is constructed
on the fly. This is straightforward enough, but I want to add an
option "All" in

both combo boxes. I understand how to build the code to get the "All"
option in one combo box:-

SELECT 0 as fldComplaintsubjectID, "[All]" as fldComplaintsubject from
tblComplaintsubjects UNION select
tblComplaintsubjects.fldComplaintsubjectID,
tblComplaintsubjects.fldComplaintsubject from tblComplaintsubjects
ORDER BY fldComplaintsubject;
- but the <row sourcecode for combo 2 won't work to restrict the
options in combo 2 when using the code for the "All" option in combo1.
How do I overcome this?

Secondly, I want to add an "All" option to combo2. How do I make this
work with the restriction aspect of combo1?

Finally, once I get the 2 "All" options working, how do I test for
their presence when the user selects tham both as options and presses
OK for the report?

Thanks for any help you can give.
Gordon

[Wayne Gillespie]

On 6 Dec 2006 02:23:38 -0800, co********@hotmail.co.uk wrote:

>I am working on a report filter form. It has 2 combo boxes
(cmboSelectSubject and cmboSelectCategory) to select criteria.
Selecting an entry in combo 1
restricts the options available in combo 2.

The row source for combo2 is:

SELECT DISTINCT tblComplaintCategory.fldComplaintCategory,
tblComplaintCategory.fldComplaintCategoryID,
tblComplaintSubjects.fldComplaintSubjectID
FROM (tblComplaints INNER JOIN tblComplaintCategory ON
tblComplaints.fldComplaintCategory =
tblComplaintCategory.fldComplaintCategoryID) INNER JOIN
tblComplaintSubjects ON tblComplaints.fldComplaintSubject =
tblComplaintSubjects.fldComplaintSubjectID
WHERE
(((tblComplaintSubjects.fldComplaintSubjectID)=[Forms]![frmReports]![cmboSelectSubject]))
ORDER BY tblComplaintCategory.fldComplaintCategory;
Pressing OK opens the report and the SQL for the report is constructed
on the fly. This is straightforward enough, but I want to add an
option "All" in

both combo boxes. I understand how to build the code to get the "All"
option in one combo box:-

SELECT 0 as fldComplaintsubjectID, "[All]" as fldComplaintsubject from
tblComplaintsubjects UNION select
tblComplaintsubjects.fldComplaintsubjectID,
tblComplaintsubjects.fldComplaintsubject from tblComplaintsubjects
ORDER BY fldComplaintsubject;
- but the <row sourcecode for combo 2 won't work to restrict the
options in combo 2 when using the code for the "All" option in combo1.
How do I overcome this?

Change the rowsource of combo 2 depending on the selection of combo 1 using the
AfterUpdate event of Combo1. ie. Remove the Where clause from the rowsource if
"All" is selected in combo 1.

Something like (syntax unchecked) -

Dim strSQL as String
If Me.Combo1=0 Then 'All selected
strSQL="SELECT 'All' As fldComplaintCategory, 0 AS fldComplaintSubjectID,
0 As fldComplaintSubjectID FROM tblComplaintCategory
UNION SELECT tblComplaintCategory.fldComplaintCategory,
tblComplaintCategory.fldComplaintCategoryID,
tblComplaintSubjects.fldComplaintSubjectID
FROM (tblComplaints INNER JOIN tblComplaintCategory ON
tblComplaints.fldComplaintCategory =
tblComplaintCategory.fldComplaintCategoryID) INNER JOIN
tblComplaintSubjects ON tblComplaints.fldComplaintSubject =
tblComplaintSubjects.fldComplaintSubjectID
ORDER BY tblComplaintCategory.fldComplaintCategory;"
Else
strSQL="SELECT 'All' As fldComplaintCategory, 0 AS fldComplaintSubjectID,
0 As fldComplaintSubjectID FROM tblComplaintCategory
UNION SELECT tblComplaintCategory.fldComplaintCategory,
tblComplaintCategory.fldComplaintCategoryID,
tblComplaintSubjects.fldComplaintSubjectID
FROM (tblComplaints INNER JOIN tblComplaintCategory ON
tblComplaints.fldComplaintCategory =
tblComplaintCategory.fldComplaintCategoryID) INNER JOIN
tblComplaintSubjects ON tblComplaints.fldComplaintSubject =
tblComplaintSubjects.fldComplaintSubjectID
WHERE

(((tblComplaintSubjects.fldComplaintSubjectID)=[Forms]![frmReports]![cmboSelectSubject]))
ORDER BY tblComplaintCategory.fldComplaintCategory;"
End If

Me.Combo2.RowSource=strSQL

>Secondly, I want to add an "All" option to combo2. How do I make this
work with the restriction aspect of combo1?

See above

>
Finally, once I get the 2 "All" options working, how do I test for
their presence when the user selects tham both as options and presses
OK for the report?

If Me.Combo1 = 0 AND Me.Combo2 = 0 Then
'open report to display all records
Else
'open report using to display filtered records.
End If

>
Thanks for any help you can give.
Gordon

Wayne Gillespie
Gosford NSW Australia

[Tim Marshall]

co********@hotmail.co.uk wrote:

I am working on a report filter form. It has 2 combo boxes
(cmboSelectSubject and cmboSelectCategory) to select criteria.
Selecting an entry in combo 1
restricts the options available in combo 2.

The row source for combo2 is:

Pressing OK opens the report and the SQL for the report is constructed
on the fly.

This is straightforward enough, but I want to add an
option "All" in

As you've noted you have a good understanding how to add the option in
the link Tom provided.

- but the <row sourcecode for combo 2 won't work to restrict the
options in combo 2 when using the code for the "All" option in combo1.
How do I overcome this?

Secondly, I want to add an "All" option to combo2. How do I make this
work with the restriction aspect of combo1?

I think what you're saying is that the where clause:

fldComplaintSubjectID=[Forms]![frmReports]![cmboSelectSubject]

doesn't work if the value of cmboSelectSubject is 0, from picking the
All option.

Personally, what I do is construct the SQL for the second combo box's
row source in the on got focus event of the second combo. You could
also do it in the afterupdate event of the first combo box. For example:

Sub cmboSelectCategory_GotFocus()

dim strS as string

on Error goto err_Proc

strS = "SELECT fldComplaintCategory, "
strS = strS & "fldComplaintCategoryID, "
strS = strS & "fldComplaintSubjectID "
strS = strS & "FROM (tblComplaints INNER JOIN tblComplaintCategory ON
tblComplaints.fldComplaintCategory =
tblComplaintCategory.fldComplaintCategoryID) "
strS = strS & "INNER JOIN
tblComplaintSubjects ON tblComplaints.fldComplaintSubject =
tblComplaintSubjects.fldComplaintSubjectID"

'Now, set up the where clause as per the choice in
'cmboSelectSubject:

If me.cmboSelectSubject <0 then 'if 0, don't use a where clause
strs = strS & "WHERE fldComplaintSubjectID = " & me.cmboSelectSubject
end if

'Add the dummy select clause for the union query

strS = strS & SELECT ""<All>"", "
strS = strS & "0, "
strS = strS & "0 "
strS = strS & "FROM tblComplaints"

strs = strs & "ORDER BY 1"

'Now give cmboSelectCategory the new rowsource
'Don't need to change it unless the SQL is different

if me.cmboSelectCategory.rowsource <strs then
me.cmboSelectCategory.rowsource =strS
'A requery might not be necessary as changing the rowsource
'My automatically requery.
me.cmboSelectCategory.requery
end if

Exit_Proc:

Exit Sub

Err_Proc:
Select case err.number
case else
msgbox "Error " & err.number & " " & err.description, _
vbexclamation, "cmboSelectCategory got focus", _
err.helpfile, err.helpcontext
resume exit_Proc
end select

End Sub
The same way you did the union query for

Finally, once I get the 2 "All" options working, how do I test for
their presence when the user selects tham both as options and presses
OK for the report?

You say the SQL is being constructed on the fly. In the report on open
or whatever even you're using just inspect cmboSelectSubject and
cmboSelectCategory, ie,

If Forms.frmName.cmboSelectSubject =6 then...
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Be Careful, Big Bird!" - Ditto "TIM-MAY!!" - Me