Problem getting value in a subreport displayed in main report correctly

[Max Moor]

Hi All,
I have a weird problem I hope someone can help with. I'll describe
the report first, and the problem below.
I have a report used to display receipt information. The sales data
comes from two dissimilar tables, so I display each in a subreport embedded
in the detail section of the main report.
Each subreport has a detail section, and one grouping footer section.
The detail shows info abou the item purchaed, including the amount paid.
The footer section has three text boxes with their control source set to
"=Sum([<amount>])", where <amount> is a disserent field from the
subreport's recordsource query. For what it's worth, one is a simple table
field name, and the other two are calculated in the query.
None of the footer text boxes are set to running sum or anything. I
simply want the total of the values of each of those things in the detail
section. For the sake of discussion, say one of these is called
"txtAmountTotal".
The main report also has a detail section (where the subreports are)
and a single grouping, with a header and footer enabled. The grouping
forces a page break after the section, so I can print multiple receipts for
a single user, if the report is opened with multiple records.
In the grouping footer of the main form, I have a textbox with an
extremely ugly test for its control source. Inside an IIf() function, it
checks if the subreport has data with:

IIf(Reports!rptMainReport!rsub_Purchases.Report.HasData,

If so, It displays the value of "txtAmountTotal" from the subreport or
0 with:

Reports!rptMainReport!rsub_Purchases.Report!txtAmountTotal, 0)

Now the problem...

If I call the main report with a single record, for a single receipt,
this works fine. When I call the report with multiple records, the value
shown on the main report is sometimes wrong. As an example, I have a user
with three receipts, so I open the report with their set of three records.
On the first, the reported value on the main report should be 0, and it is.
On the second one, it should be $175, and it is. On the third one, it
should be 0 again, but it displays $175 still.
I went into the subreport, and made the "totals" boxes in the footer
section visible, and they show the correct values. It's as though, for
that third receipt page, the text box on the main form is still reading the
subreport from receipt (page) 2.
Again, I don't have running totals set anywhere, but it sure acts like
that what it's doing. Does anyone have any thoughts for me?

Thanks, Max

 

[Marshall Barton]

I have a weird problem I hope someone can help with. I'll describe
the report first, and the problem below.
I have a report used to display receipt information. The sales data
comes from two dissimilar tables, so I display each in a subreport embedded
in the detail section of the main report.
Each subreport has a detail section, and one grouping footer section.
The detail shows info abou the item purchaed, including the amount paid.
The footer section has three text boxes with their control source set to
"=Sum([<amount>])", where <amount> is a disserent field from the
subreport's recordsource query. For what it's worth, one is a simple table
field name, and the other two are calculated in the query.
None of the footer text boxes are set to running sum or anything. I
simply want the total of the values of each of those things in the detail
section. For the sake of discussion, say one of these is called
"txtAmountTotal".
The main report also has a detail section (where the subreports are)
and a single grouping, with a header and footer enabled. The grouping
forces a page break after the section, so I can print multiple receipts for
a single user, if the report is opened with multiple records.
In the grouping footer of the main form, I have a textbox with an
extremely ugly test for its control source. Inside an IIf() function, it
checks if the subreport has data with:

IIf(Reports!rptMainReport!rsub_Purchases.Report.HasData,

If so, It displays the value of "txtAmountTotal" from the subreport or
0 with:

Reports!rptMainReport!rsub_Purchases.Report!txtAmountTotal, 0)

Now the problem...

If I call the main report with a single record, for a single receipt,
this works fine. When I call the report with multiple records, the value
shown on the main report is sometimes wrong. As an example, I have a user
with three receipts, so I open the report with their set of three records.
On the first, the reported value on the main report should be 0, and it is.
On the second one, it should be $175, and it is. On the third one, it
should be 0 again, but it displays $175 still.
I went into the subreport, and made the "totals" boxes in the footer
section visible, and they show the correct values. It's as though, for
that third receipt page, the text box on the main form is still reading the
subreport from receipt (page) 2.
Again, I don't have running totals set anywhere, but it sure acts like
that what it's doing. Does anyone have any thoughts for me?

Not sure I unraveled all that, so check if this sumarizes
correctly.

a) The main report detail contains a subreport.

b) The sub report's report footer section contains a total
text box.

c) The main report group footer contains a text box that
refers to thesubreport's total text box.

If that's accurate, then the problem is that the main
report's group footer text box is referring to the subreport
total only for the last main report detail.

To add the totals for all the subreport's winthin a main
report group, you need to move the text box from the group
footer up to the detail section and set its RunningSum
property to Over Group. Then the group footer can use a
text box that refers to the running sum text box.

 

[Max Moor]

Not sure I unraveled all that, so check if this sumarizes
correctly.

a) The main report detail contains a subreport.

b) The sub report's report footer section contains a total
text box.

c) The main report group footer contains a text box that
refers to thesubreport's total text box.

If that's accurate, then the problem is that the main
report's group footer text box is referring to the subreport
total only for the last main report detail.

To add the totals for all the subreport's winthin a main
report group, you need to move the text box from the group
footer up to the detail section and set its RunningSum
property to Over Group. Then the group footer can use a
text box that refers to the running sum text box.

Hi Marshall,
You got it. (Sorry, I tend to the wordy). I moved the textbox on the
main report, that shows the total from the subreports, to the detail
section and it works fine. I'm not 100% sure I understand the difference
between placement in the detail section (with the subreports) and the
grouping footer (still only one set of subreports per detail), but at least
it works as expected. I'll have to ponder this one a little.
At any rate, I appreciate the response. It's awfully nice to see the
thing do what it ought to.
- Max

 

[Marshall Barton]

Marshall Barton wrote


Hi Marshall,
You got it. (Sorry, I tend to the wordy). I moved the textbox on the
main report, that shows the total from the subreports, to the detail
section and it works fine. I'm not 100% sure I understand the difference
between placement in the detail section (with the subreports) and the
grouping footer (still only one set of subreports per detail), but at least
it works as expected. I'll have to ponder this one a little.
At any rate, I appreciate the response. It's awfully nice to see the
thing do what it ought to.

Glad you got it sorted.

The issue with the group footer referring to the subreport
in the detail is that there is only one group footer and
many details. So the question becomes - which detail is the
group footer referring to. The only sensible answer to that
is the last detail.

Since you want the total of all the details in the group,
you need each detail to extract the value from the subreport
in that detail. Furthermore, you have to do something (in
this case running sum), to add up the extracted value in
each detail to display a total in the group footer.

Well, that sounds like it's probably just going to muddy the
waters for you, so continue to contemplate the mysteries of
grouping records and eventually it will become clear ;-)