Defining recordsource for report including "all"

I have a simple form with a combox box and a command button for opening a report. Currently, the recordsource for the report is based on a query that filters the data from the combo box selection and only shows those records belonging to the selected group on the report. I added code behind the combo box to include all groups as well:

Combo0.RowSource = "SELECT [BU_ID], [BU_NAME] FROM tbl_BUSINESS_UNIT ORDER BY BU_NAME " & _
"UNION SELECT 0 AS [BU_ID], 'All Groups' AS [BU_NAME] FROM tbl_BUSINESS_UNIT;"
Combo0 = ""

How do I write the code to set the recordsource for the report that can be either one of the groups from the combo box or all groups? Thanks.

Melanie,

There is no need for any code to do this. Just put the criteria in the
BU_ID field in your query like this...
[Forms]![YourFormName]![Combo0] Or [Forms]![YourFormName]![Combo0]=0

By the way, speaking of code, what is the purpose of the line Combo0 =
"" in the code you showed us? On the face of it, it would seem to be
incorrect.

--
Steve Schapel, Microsoft Access MVP


Melanie O wrote:
I have a simple form with a combox box and a command button for
opening a report. Currently, the recordsource for the report is
based on a query that filters the data from the combo box selection
and only shows those records belonging to the selected group on the
report. I added code behind the combo box to include all groups as
well:

Combo0.RowSource = "SELECT [BU_ID], [BU_NAME] FROM tbl_BUSINESS_UNIT
ORDER BY BU_NAME " & _ "UNION SELECT 0 AS [BU_ID], 'All Groups' AS
[BU_NAME] FROM tbl_BUSINESS_UNIT;" Combo0 = ""

How do I write the code to set the recordsource for the report that
can be either one of the groups from the combo box or all groups?
Thanks.

Never mind. I figured it out. Sorry for the extra post(s).

"Melanie O" wrote:

I have a simple form with a combox box and a command button for opening a report. Currently, the recordsource for the report is based on a query that filters the data from the combo box selection and only shows those records belonging to the selected group on the report. I added code behind the combo box to include all groups as well:

Combo0.RowSource = "SELECT [BU_ID], [BU_NAME] FROM tbl_BUSINESS_UNIT ORDER BY BU_NAME " & _
"UNION SELECT 0 AS [BU_ID], 'All Groups' AS [BU_NAME] FROM tbl_BUSINESS_UNIT;"
Combo0 = ""

How do I write the code to set the recordsource for the report that can be either one of the groups from the combo box or all groups? Thanks.