My report can't add properly! 1

[RYankowitz]

I have a report which generates a client statement, showing multiple records (one per invoice).&nbsp;&nbsp;Because I need to manipulate and make some decisions about the fields before displaying them, I must use some VB code in the Format event.<br><br>As usual with statments, I need totals for each field. Because the fields are now unbound controls, I can't use the SUM() function to add them all up at the end of the report.&nbsp;&nbsp;I am manually adding them within the Format event and displaying them in the report footer.<br><br>This all works fine as long at the report doesn't run to a second page.&nbsp;&nbsp;When it does, the sums are incorrect.&nbsp;&nbsp;The report is adding the first record of each page (past the first) twice.<br><br>Easy! you say.&nbsp;&nbsp;Access is Retreating upon finding that the next record on the page won't fit - so I should use the Retreat event to correct the miscount.&nbsp;&nbsp;But it's not!&nbsp;&nbsp;The Retreat event is not occurring.&nbsp;&nbsp;I put a breakpoint in the code of the Retreat event.&nbsp;&nbsp;The breakpoint never triggers.<br><br>I feel like retreating to the North Woods.<br><br>Any help would be greatly appreciated.

 

[Umbane]

Can you paste an example of your code here so we can see what you're trying.

Umbane

 

[RYankowitz]

Actually, thanks to you, I finally found it! In cutting and pasting my code I noticed the &quot;FormatCount&quot; argument of the Format event, and realized this was probably what I had overlooked.

My understanding of the Retreat event appears woefully inadequate.

What is really happening is that Access can take care of totalling just fine, thank you, but if I have to do it myself I need to take special precautions, thusly:

[tt]
Private Sub PurchaseOrder_Detail_Format(Cancel As Integer,_
FormatCount As Integer)

If FormatCount <= 1 Then
SumOrderTotal = SumOrderTotal + Order_Total
SumAmountDue = SumAmountDue + AmountDue
SumCurrent = SumCurrent + Current
SumOne2Thirty = SumOne2Thirty + One2Thirty
.
.
.
SumNinetyOnePlus = SumNinetyOnePlus + NinetyOnePlus
End If

End Sub
[/tt]

Since some records are formatted twice (when Access realizes they won't fit on the bottom of a page), they can get added to the totals twice. The check for the FormatCount prevents that. Seems simple, once you know you need to do it.

There's another post nearby (&quot;Display Calculation in Report Footer&quot which mentioned the same problem. I'll put my findings in that post as well.

Thanks for making me look reeeeeaally close at the code.