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?
Who is Participating?
Jeffrey CoachmanMIS LiasonCommented:
There is also code to add custom page numbers (reset Page numbers),...
Group 1, page 1
Group 1, page 2
Group 1, page 2
Group 2, page 1
Group 2, page 2
Group 2, page 3
Group 2, page 4
You may be able to customize this to reflect you "A,B, and C" designations, and also add things like:
    Group B, page 3, of 7, out of 82 total Report pages
...when combined with the basic page numbering

Upload a sample database of what you have done, including the table to report from
Show an image of the required output.
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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.
newbie46Author Commented:
Thank you, Jeff.
This will solve the issue.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.