Running Total in Subreport Inaccurate

[Jana]

Using Access 97.
Background:
I have a main report called rptTrustHeader with a subreport
rptTrustDetails in the Details section of the main report. The main
report is grouped by MasterClientID. There are also several other
subreports in the main report's footer. The rptTrustDetails subreport
has two grouping levels MasterClientID and ClientID, with headers and
footers for each grouping level. In the ClientID footer, I have an
invisible running total unbound field called CountOfClientID, with its
control source set to = 1, with it set to total Over Group. In the
MasterClientID footer, I have some grand total fields that I want to
show up ONLY if there is more than one ClientID on the report. Then in
the OnFormat event of the MasterClientID footer, I make the grand total
fields show up if the CountOfClientID is greater than 1, and invisible
if CountOfClientID is equal to 1. This should prevent showing the same
totals in the ClientID footer down in the MasterClientID footer.

Problem:
When I open the subreport rptTrustDetails without viewing it through
the main report, the running total field accurately counts the number
of ClientID footers that it encountered and shows/hides the
MasterClientID footer totals exactly as expected. Great! The problem
occurs when I open the main report. Suddenly, my running total field
is randomly inaccurate. Sometimes the field accurately reflects 1,
then on the next MasterClientID's report with only 1 ClientID footer,
the running total shows 2! My only thought is that maybe something is
triggering the ClientID footer multiple times somehow during the
formatting of the main report, but I am at a total loss as to how I can
overcome this.

I have searched and found similar postings, but none with a resolution.
So, I throw myself upon the mercy of the Access gurus out there. As
such, I would welcome and solutions and/or workarounds for this
problem. Am I going to be reduced to doing a DCount in my OnFormat
event and writing a separate query, just to determine if there is more
than one ClientID reporting under the MasterClientID?

Thanks in advance for any suggestions!
Jana

[Tom van Stiphout]

On 1 Dec 2006 12:43:36 -0800, "Jana" <Ba********@gmail.comwrote:

You do realize that OnFormat may be called more often than you might
think - that's why it has a FormatCount property. Some code might be
tripped up by this.

DCount or a Totals query ain't bad.

-Tom.

>Using Access 97.
Background:
I have a main report called rptTrustHeader with a subreport
rptTrustDetails in the Details section of the main report. The main
report is grouped by MasterClientID. There are also several other
subreports in the main report's footer. The rptTrustDetails subreport
has two grouping levels MasterClientID and ClientID, with headers and
footers for each grouping level. In the ClientID footer, I have an
invisible running total unbound field called CountOfClientID, with its
control source set to = 1, with it set to total Over Group. In the
MasterClientID footer, I have some grand total fields that I want to
show up ONLY if there is more than one ClientID on the report. Then in
the OnFormat event of the MasterClientID footer, I make the grand total
fields show up if the CountOfClientID is greater than 1, and invisible
if CountOfClientID is equal to 1. This should prevent showing the same
totals in the ClientID footer down in the MasterClientID footer.

Problem:
When I open the subreport rptTrustDetails without viewing it through
the main report, the running total field accurately counts the number
of ClientID footers that it encountered and shows/hides the
MasterClientID footer totals exactly as expected. Great! The problem
occurs when I open the main report. Suddenly, my running total field
is randomly inaccurate. Sometimes the field accurately reflects 1,
then on the next MasterClientID's report with only 1 ClientID footer,
the running total shows 2! My only thought is that maybe something is
triggering the ClientID footer multiple times somehow during the
formatting of the main report, but I am at a total loss as to how I can
overcome this.

I have searched and found similar postings, but none with a resolution.
So, I throw myself upon the mercy of the Access gurus out there. As
such, I would welcome and solutions and/or workarounds for this
problem. Am I going to be reduced to doing a DCount in my OnFormat
event and writing a separate query, just to determine if there is more
than one ClientID reporting under the MasterClientID?

Thanks in advance for any suggestions!
Jana

[Jana]

Tom:
Thanks for your reply, I hadn't had a chance to check back until now.
I have since corrected the problem using a DCount in the MasterClientID
footer, but was just confused as to why the Running Sum feature was
inaccurate on a subreport. Any thoughts?

Jana

Tom van Stiphout wrote:

On 1 Dec 2006 12:43:36 -0800, "Jana" <Ba********@gmail.comwrote:

You do realize that OnFormat may be called more often than you might
think - that's why it has a FormatCount property. Some code might be
tripped up by this.

DCount or a Totals query ain't bad.

-Tom.

Using Access 97.
Background:
I have a main report called rptTrustHeader with a subreport
rptTrustDetails in the Details section of the main report. The main
report is grouped by MasterClientID. There are also several other
subreports in the main report's footer. The rptTrustDetails subreport
has two grouping levels MasterClientID and ClientID, with headers and
footers for each grouping level. In the ClientID footer, I have an
invisible running total unbound field called CountOfClientID, with its
control source set to = 1, with it set to total Over Group. In the
MasterClientID footer, I have some grand total fields that I want to
show up ONLY if there is more than one ClientID on the report. Then in
the OnFormat event of the MasterClientID footer, I make the grand total
fields show up if the CountOfClientID is greater than 1, and invisible
if CountOfClientID is equal to 1. This should prevent showing the same
totals in the ClientID footer down in the MasterClientID footer.

Problem:
When I open the subreport rptTrustDetails without viewing it through
the main report, the running total field accurately counts the number
of ClientID footers that it encountered and shows/hides the
MasterClientID footer totals exactly as expected. Great! The problem
occurs when I open the main report. Suddenly, my running total field
is randomly inaccurate. Sometimes the field accurately reflects 1,
then on the next MasterClientID's report with only 1 ClientID footer,
the running total shows 2! My only thought is that maybe something is
triggering the ClientID footer multiple times somehow during the
formatting of the main report, but I am at a total loss as to how I can
overcome this.

I have searched and found similar postings, but none with a resolution.
So, I throw myself upon the mercy of the Access gurus out there. As
such, I would welcome and solutions and/or workarounds for this
problem. Am I going to be reduced to doing a DCount in my OnFormat
event and writing a separate query, just to determine if there is more
than one ClientID reporting under the MasterClientID?

Thanks in advance for any suggestions!
Jana