Report totals double when hardcopy is printed!

[rypper]

Hi there,

I have a report with totals that are generated from code. The values within the total text boxes show up correctly when printed to screen, but when the report is printed, the total text boxes' values (which are in the report footer) double in value.

The values are not coming from the qry that the report is linked to, but rather from a table (via sql).

Any help would be greatly appreciated!

Thanks in advance!

Regards
rypper

 

[PaulBricker]

rypper, you will have to gt us a little more info to help. Try posting your sql and any calculations you are doing.

Paul

 

[jiqjaq]

Since your totals are correct on the screen, and are doubling, your calculations are probably fine. You are probably doing some (or all) of the calculations in the Format section, rather than the Print section. This is a very common problem in reports.

If you calculate in the Format section, you must put the calculations within an IF FormatCount = 1 ... END IF block. FormatCount is predefined as an input parameter by Access on the first line of the proc.

FormatCount starts at 1 but if a section needs to be reformatted (because data can't fit on the page), the entire section will be reprocessed and FormatCount will increment. If you calculate totals while this occurs, you will usually double the value of the result, since the calculation will be done twice (once for each Format).

You can also try to put the calculation in the Print section, since it will only be done once there - unless you specifically code it otherwise.

 

[rypper]

Thanks to Paul and Jigjag for their prompt replys!

I have a calculation that does a count of total vendors in the Detail_Format and the final number is correct on both the screen and hardcopy.

My other calcs are done in Functions:
++++++++++++++++++++++++++++++++++++++++++++++++
Private Function NumberOfODFs() As Integer
Dim dbs As Database
Dim rst As Recordset
Dim MySql As String
Dim count As Integer
Dim Operation As String

Set dbs = CurrentDb()
count = 0

Set rst = dbs.OpenRecordset("select Operation from ODFS where yearhunted=" & Me![CurrentYear] & _
"and Species='" & MyFilter.Species & "'&quot
rst.MoveFirst
While Not rst.EOF
If (Me!Operation = rst!Operation) Then
count = count + 1
End If
rst.MoveNext
Wend
NumberOfODFs = count

rst.Close
dbs.Close
End Function
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
=======================
Function CalcODF()
Dim tmpAmount As Double
tmpAmount = NumberOfODFs
' Remember the total for the report.
ODFGrandTotal = ODFGrandTotal + tmpAmount
CalcODF = tmpAmount
End Function
===============================
**********************
Function GetODFReportTotal()
GetODFReportTotal = ODFGrandTotal
End Function
**********************
On the Report Footer, my Grand Total text box simply has =GetODFReportTotal(), but as stated in my first posting, it doubles when sent to the printer.

The Function CalcODF works fine for both the screen and hard copy.


Thanks again!

rypper

 

[PaulBricker]

rypper, what is ODFGrandTotal? I see you use it in one of the functions but I don't see it Dimmed anywhere or that it's a function.
What you could do is call the Function from the Report Footer Format Event and then set the last line
GetODFReportTotal = ODFGrandTotal
to
Reports!ReportName!TextboxName = ODFGrandTotal

See if that helps.

Paul

 

[rypper]

Hi Paul,

I dim ODFGrandTotal at the very bigining of the module.

I will try your suggestion of calling it from Report Footer Format Event.....

I do appreciate your time!

I will keep you informed.

Regards

Ted

 

[Garridon]

There's an FAQ that might help:

http://www.tek-tips.com/faqs.cfm?spid=703&sfid=1424

 

[rypper]

YES! YES! YES!!!!!!BINGO!
The key was:
The solution is to provide correct initialization of your variables. They must be initialized first when the report is first opened, and then a second time when the report is sent to the printer. The names of these two events are Report_Open and Report_Page.
See faq above.

Thank you to all for your time and input!

rypper

 

[rypper]

no no no! No bingo.

The totals come off the printer without being doubled....that's great. However, if my report is longer than 1 page, my final total does not add up correctly unless I view each page in the report until I reach the final page.

For example: A 5 page report will only display a proper total if (when in print preview) I click page by page to the final page. If I click and go directly from the first page to the final page, totals are do not add up correclty.

What am I missing here?

Thanks in advance!

Regards

rypper

 

[PaulBricker]

rypper, what are you initializing in the Open and Page Events? Have you tried running the Function from the Format event for the Footer? Or the Print event for the Footer?
In the On_Page event you might try
If Page = Pages then
Dim....etc.
Else
EndIf

Let me know how it goes.

Paul

 

[rypper]

Thanks again gang!

Once again, my totals (one being GrandTotalODF, which gets it's source from a function GetODFReportTotal) come out correct, only if the report is one page. Otherwise.......wrong totals.

Paul, What would be the proper syntax to run my functions from the "Private Sub Report_Page()"

Thank you all again for your time!.....

regards

rypper