Subreport Detail_Format being called twice

  • Thread starter Thread starter Jody
  • Start date Start date
J

Jody

I am executing code inside the Detail_Format() Subroutine
of a subreport. In this subroutine I am performing some
complicated calculations where I sum up values from
several different fields based on certain criteria, so I
cannot simply use the SUM() type functionality that Access
offers. Anyways, it is looping through each record in the
query that the subform is bound to twice. It will go
through every record in the recordset once, where
everything is working correctly, but it will then run
through it a second time, without re-firing the
report_open or report_close events. Therefore, my global
variables are not being reset, and my calculations are
being thrown off. So to sum it up, has anyone else had
the problem where a subreport's Detail_Format event is
being called twice instead of only once?

Any help would be greatly appreciated.

Jody
 
I would seriously consider NOT doing calculations in any event in a report.
It is much more reliable to do the calculations in the query or controls
sources (if possible).
 
Within the detail_format you could declare a static
variable and set it to false.

if staticVariable = false then

Then do a while recordset.eof =false loop OR

counter = counter +1
do while counter < recordset.count
loop

staticVariable = true

Anyway, you can set the static variable to true when the
first loop has finished and this will stop the second
loop. Then on the change of page you could set the static
back to false.....


or something...

If possible, always use a real recordset which is pre-
calculated.

Also, test to see that your calculations are printed the
same as they appear on screen.
I had to move the code to the onPrint of the detail
section, as the numbers added up on screen but did not
when printing.
 
I am actually controlling the displaying of controls,
etc... I have a query that the subreport is bound to. In
that recordset I have a TestGroup field, that TestGroup
field can be the same for several tests (Test Field).
Each of these has a price associated with it. So if the
Test is part of a testgroup, then I take the testgroup
price for all tests in that testgroup, and ignore the test
price. But if the test is not part of the testgroup, I
display it's price. Therefore, I really am not only
summing up values, but am actually changing the visiblity,
etc.. of multiple controls, so I think I have to do it in
the _Format event. Everything works fine, except that the
Subreport is Being called Twice for each Unique record in
the Parent Report, instead of only once as it should be.


For Example.

Parent Record: (Report Query)

OrderId
1
2

Child Record: (Sub Report Query for OrderId = 1)
OrderId TestGroup Test TestGroupPrice TestPrice
1 A T1 3.00 2.00
1 A T2 3.00 2.00
1 NULL T3 2.00 2.00


So the detail_format should be called 3 times, once
for each record, but it loops through the records
twice. The strange thing, is that it only loops through
once if I have the CanGrow property set to false for the
subreport. But I need the subreport to be able to grow.

Jody
 
In addition, the subreport works perfectly when not being
called from the Main report. The Detail_Format is only
executed once per record as it should be. It is only when
it is called from within the main report that it is called
twice per record. (Loops through in the proper order, only
once the last record is called, it start over at the first
record for another iteration).
 
Jody said:
In addition, the subreport works perfectly when not being
called from the Main report. The Detail_Format is only
executed once per record as it should be. It is only when
it is called from within the main report that it is called
twice per record. (Loops through in the proper order, only
once the last record is called, it start over at the first
record for another iteration).
Click to expand...


Let me echo Duane's comment in stronger terms.

There is NO WAY to prevent the Format (or Print) event from
executing as many time as it needs to to do its job. It
might execute once, twice or even more times.

Years ago, I went around and around with this issue and wish
someone had slapped me up side the head with a two-by-four
so I hadn't wasted so much time on an unsolvable problem.

If you can't find a way to use a calculated field in the
recordsource query, then use a text box with its RunningSum
property set to either Over Group or Over All as
appropriate.
 
The first display of either a group or individual price can be done with a
LEFT or RIGHT JOIN and an expression like:
UseThisPrice: Nz([TestGroupPrice], [IndividualPrice])
There are lots of other expressions and solutions that get around code in
reports.
 
I think you guys are missing his point.
It sounds to me like it is a formatting problem instead
of a summation problem.

Since you have to control the formatting of your detail
section in the format event, then I would advise you to
determine whether your parameters are correct. By the
looks of your example, it should only be calling it once.
So I would say to re-analyze your queries and make sure
that you have a one to many relationship defined between
the report and sub-report.

Don't go whacking yourself with any objects just yet.
 
I think you guys are missing his point.
It sounds to me like it is a formatting problem instead
of a summation problem.

Since you have to control the formatting of your detail
section in the format event, then I would advise you to
determine whether your parameters are correct. By the
looks of your example, it should only be calling it once.
So I would say to re-analyze your queries and make sure
that you have a one to many relationship defined between
the report and sub-report.

Don't go whacking yourself with any objects just yet.
 
Jason said:
I think you guys are missing his point.
It sounds to me like it is a formatting problem instead
of a summation problem.

Since you have to control the formatting of your detail
section in the format event, then I would advise you to
determine whether your parameters are correct. By the
looks of your example, it should only be calling it once.
So I would say to re-analyze your queries and make sure
that you have a one to many relationship defined between
the report and sub-report.
Click to expand...


There is a formatting issue here, but it's the summing
that's causing the problem. If the two issues are
separated, then they can be dealt with. The unsolvable part
is using VBA in an event procedure to calculate the sum.

For example, the record source query can have a calculated
field
Price: IIf(TestGroup Is Null, TestPrice, TestGroupPrice)

Then, instead of making one thing visible and the other
invisible, the Price field would already have the desired
value and a simple =Sum(Price) would have the total.

If there is some reason that the query can not be set up
that way, then the Format event can take care of the Visible
stuff:

Me.TestPrice.Visible = IsNull(TestGroup)
Me.TestGroupPrice.Visible = Not IsNull(TestGroup)

and the sum can be done with a group footer text box
expression:

=Sum(IIf(TestGroup Is Null, TestPrice, TestGroupPrice))

As an alternate to using Sum, there are fairly easy ways to
set up a RunningSum text box with an expression like:

=IIf(TestGroup Is Null, TestPrice, TestGroupPrice)

to calculate the total.
 
Back
Top