none
Referring to a field in a subreport from its parent report RRS feed

  • Question

  • Hi there.

    I have a report which contains a number of subreports. In turn, each of those subreports contains the data from a query, and a text box that contains the sum of one of the columns in that query. There is a fixed number (3) of each of these subreports with a query.

    On the parent report, I need to create a text box that has the sum of each of these three subreport sums.

    I've tried referring to this value in a couple of different ways:

    1. Referring to the text in the text box with "=Reports!SubReport!TextBoxName", but this results in a "#NAME?" error

    2. Creating a Sum function, and referring to the query, "Sum(SubQuery!ColumnName)", but this returns the incorrect value. Specifically, it returns the last result in that column from the query.

    What is the best procedure for summing these items? How do you refer to a value on a subreport, from it's parent report?

    Saturday, July 29, 2017 7:33 AM

All replies

  • 1. Referring to the text in the text box with "=Reports!SubReport!TextBoxName", but this results in a "#NAME?" error

    Hi Anthoven,

    You could try:

        Reports(Reportname)!SubReport1!TextBoxName1 + Reports(Reportname)!SubReport2!TextBoxName2 + ...

    or

        Me!SubReport1!TextBoxName1 + Me!SubReport2!TextBoxName2 + ...

    Imb.

    Saturday, July 29, 2017 9:48 AM
  • Hi, thanks for your reply!

    Both those options result in a "#Func!" error.

    Sorry!

    Saturday, July 29, 2017 10:21 AM
  • Hi, thanks for your reply!

    Both those options result in a "#Func!" error.

    Sorry!

    Hi Anthoven,

    You can place an equal sign (=) at the beginning.

    Imb.

    Saturday, July 29, 2017 10:49 AM
  • Anthoven,

    The syntax in #1 in your original post will work, adapted to forms and subforms.  However it fails when you apply similar logic/syntax to reports and subreports -- in short, because reports behave quite differently from forms.

    The 'Me' syntax above also fails, because the 'Me" keyword is only recognized in VBA code, not in property sheets of controls or in queries.

    An alternative you can try in your main report is to calculate each of your three sums separately and add them together:

    = DSum("Field1", "Table1", {optional criteria})  +  DSum("Field2", "Table2", {optional criteria})  etc

    Look up the DSUM function for additional details.

    Note that DSUM and other domain aggregate functions can be performance killers when used in queries or in other scenarios, but if you are simply doing a one time grand total for your report, the performance hit should be small/negligible.


    Miriam Bizup Access MVP


    • Edited by mbizup MVP Monday, July 31, 2017 11:54 AM
    Monday, July 31, 2017 11:16 AM
  • Hi Anthoven,

    Has your original issue been resolved? If it has, I would suggest you mark the helpful reply or provide your solution and then mark it as answer to close this thread. 
    If not, please feel free to let us know your current issue.

    Best Regards,

    Terry

    Thursday, August 3, 2017 8:34 AM