How can I re-use the same subreport within a larger report and pass a variable to each iteration of the subreport?

Posted on 2014-08-08
Last Modified: 2014-08-09
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?
Question by:newbie46
    LVL 30

    Expert Comment

    Upload a sample database of what you have done, including the table to report from
    Show an image of the required output.
    LVL 84
    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.
    LVL 74

    Accepted Solution

    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


    Author Closing Comment

    Thank you, Jeff.
    This will solve the issue.

    Featured Post

    Why You Should Analyze Threat Actor TTPs

    After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

    Join & Write a Comment

    Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
    Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
    Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
    In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now