newbie46
asked on
How can I re-use the same subreport within a larger report and pass a variable to each iteration of the subreport?
I have a Report (ReportA) that contains the following code within the Report_Open event:
'gCode is a Global variable
if gCode = "A" then
me.RecordSource = "SELECT A1 AS Field1, A2 AS Field2 " & _
"FROM TableA"
elseif gCode = "B" then
me.RecordSource = "SELECT B1 AS Field1, B2 AS Field2 " & _
"FROM TableB"
elseif gCode = "C" then
me.RecordSource = "SELECT C1 AS Field1, C2 AS Field2 " & _
"FROM TableC"
end if
gCode is set on FormA. The user selects a radial button and then clicks a 'View Report' button.
My issue: I need to print all 3 iterations of this report (with gCode set to A, and then B, and then C) and have a cumulative counter at the bottom of each page. So, if each report is 5 pages long, along with Page 1 of 5, Page 2 of 5, etc. appearing on the page for each of the 3 reports, I also need to display an overall counter Page 1 of 15, Page 2 of 15 as well.
My question:
So as not to have to make 3 copies of this report and then force the value of gCode within each of the 3 copies, how can I have 3 iterations of this ReportA (as a subreport) on a Main report and pass each iteration the gCode value?
'gCode is a Global variable
if gCode = "A" then
me.RecordSource = "SELECT A1 AS Field1, A2 AS Field2 " & _
"FROM TableA"
elseif gCode = "B" then
me.RecordSource = "SELECT B1 AS Field1, B2 AS Field2 " & _
"FROM TableB"
elseif gCode = "C" then
me.RecordSource = "SELECT C1 AS Field1, C2 AS Field2 " & _
"FROM TableC"
end if
gCode is set on FormA. The user selects a radial button and then clicks a 'View Report' button.
My issue: I need to print all 3 iterations of this report (with gCode set to A, and then B, and then C) and have a cumulative counter at the bottom of each page. So, if each report is 5 pages long, along with Page 1 of 5, Page 2 of 5, etc. appearing on the page for each of the 3 reports, I also need to display an overall counter Page 1 of 15, Page 2 of 15 as well.
My question:
So as not to have to make 3 copies of this report and then force the value of gCode within each of the 3 copies, how can I have 3 iterations of this ReportA (as a subreport) on a Main report and pass each iteration the gCode value?
Why not just use a UNION statement, and show all of the data on a single report:
me.RecordSource = "SELECT A1 AS Field1, A2 AS Field2 FROM TableA" _
& " UNION" _
& " SELECT B1 AS Field1, B2 AS Field2 FROM TableB" _
& " UNION" _
& " SELECT C1 AS Field1, C2 AS Field2 FROM TableC"
You could further Group the data in a meaningful way, if that would help the presentation.
me.RecordSource = "SELECT A1 AS Field1, A2 AS Field2 FROM TableA" _
& " UNION" _
& " SELECT B1 AS Field1, B2 AS Field2 FROM TableB" _
& " UNION" _
& " SELECT C1 AS Field1, C2 AS Field2 FROM TableC"
You could further Group the data in a meaningful way, if that would help the presentation.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you, Jeff.
This will solve the issue.
This will solve the issue.
Show an image of the required output.