473,231 Members | 1,927 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,231 software developers and data experts.

Adding <All> as options in 2 combo boxes

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

Dec 6 '06 #1
5 2188
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
Dec 6 '06 #2
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
Dec 6 '06 #3
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
Dec 6 '06 #4
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
Dec 6 '06 #5
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
Dec 6 '06 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: nosferatu_1_1 | last post by:
Hi, I would like to create a schema that validate the following xml: <music> <pop/> <rock/> <pop/> </music>
0
by: Olly | last post by:
I am using the following pure css layout with a header and footer: http://www.fu2k.org/alex/css/layouts/3Col_NN4_FMFM.mhtml The left column floats to the left of the main content (middle...
4
by: Ferd Berfel | last post by:
given this code: <select name="mySelect" size="3" multiple> <option>one</option> <option>two</option> <option>three</option> <option>four</option> <option>five</option> <option...
4
by: joiv | last post by:
I'm making a <select></select> with lots of <option></option>. It contains all possible options. Because of the length of the list, I also have an <input type="text">. This is what I wish to do:...
11
by: Les Paul | last post by:
I'm trying to design an HTML page that can edit itself. In essence, it's just like a Wiki page, but my own very simple version. It's a page full of plain old HTML content, and then at the bottom,...
3
by: deko | last post by:
SELECT TxType_ID, TxTypeName FROM tblTxType UNION SELECT Top 1 TxType_ID, "<Payment Account>" FROM tblTxType ORDER BY ; The problem I'm having is that the Top 1 "TxType_ID" brings up a...
3
by: ajay2552 | last post by:
Hi, I have a query. All html tags start with < and end with >. Suppose i want to display either '<' or '>' or say some text like '<Company>' in html how do i do it? One method is to use &lt,...
7
Ranjan kumar Barik
by: Ranjan kumar Barik | last post by:
Hi! I want to create a combo box that will contain too many data like name of all the countries. Is there any other method I can use to do that without adding all of them using the <option>...
1
by: Axel Gallus | last post by:
I add some option items to a <selectbox by: ----- MySelect.length = null; // clears all options in select element for ( var i in Somename ) { MySelect.options = new Option(Somename,
0
by: VivesProcSPL | last post by:
Obviously, one of the original purposes of SQL is to make data query processing easy. The language uses many English-like terms and syntax in an effort to make it easy to learn, particularly for...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.