Grand Total wrong when report is printed

[KentHuck]

A report's Grand Total (txtGrand below) displays correctly in Print Preview. However, it is incorrect when Printed.

The Detail field (txtMoney) is derived from a query (because the report is too complex for only one query). Therefore, the Grand Total is accumulated in code in the variable curTotal. The Grand Total on the report is set in the sub ReportFooter_Print.

Report layout:
Detail:
txtMoney (unbound)
Report Footer:
txtGrand (unbound)

Report Code:
Dim curTotal As Currency
Private Sub Detail_Print()
txtMoney = Nz(DLookup("Money", "QryName"), 0)
curTotal = curTotal + txtMoney 'Accumulate Grand Total.
End Sub
Private Sub ReportFooter_Print()
txtGrand = curTotal 'Move Grand Total to report.
End Sub

 

[snyperx3]

you could use
txtGrand.Value = DSum("Money","QryName")

and remove everything associated with your curTotal variable. that should do it for you if you wanted VBA code. otherwise you could use
=Sum([Money])
as the txtGrand's control source.

-Pete

 

[KentHuck]

Hey, Pete, thank you for your rapid response.

U wrote: you could use
txtGrand.Value = DSum("Money","QryName")

I can't do this because a project ID changes for each detail line.

U wrote: otherwise you could use
=Sum([Money])
as the txtGrand's control source.

I assume that you meant =Sum([txtMoney]). I can't do that because txtMoney is unbound.

Thanks,
Kent

 

[CosmoKramer]

Kent,

This is a typical downfall of using VBA to calculate totals. The problem is the code could be called multiple times, once for preview, once for printing, etc., explaining the incorrect printed totals.

Can you use the RunningSum textbox property to calculate your totals instead??

Si hoc legere scis, nimis eruditionis habes

 

[snyperx3]

KentHuck: So are you wanting a different grand total for each project id? If not why does txtGrand.Value = DSum("Money","QryName") not work?

I used =Sum([Money]) thinking that Money was part of your query the report was based off of...im sorry that was my fault on not paying attention as much when i read.

Also Kent, you should put your report VBA code on the OnFormat event, NOT the Print event.

CosmoKramer: You can use VBA to calculate the information if you do all the calculations on the event OnFormat as that is executed for both preview and print.

-Pete

 

[CosmoKramer]

snyperx3,

I never said you couldn't use VBA for calculations, I was just pointing out another method that does work with less considerations necessary for its use.


Si hoc legere scis, nimis eruditionis habes

 

[snyperx3]

Cosmo, i know you didnt say that. I was just pointing out that it will all work correctly an as wanted if you put it in the OnFormat section, and it will not be "called multiple times, once for preview, once for printing, etc., explaining the incorrect printed totals."

I wasnt trying to sound correcting or cocky. I apologize if it came across that way.

-Pete

 

[dhookom]

If you truly can't calculate the total without code, I would add another text box just like txtMoney but name it "txtMoneyRunningSum". Set its Running Sum property to Over All and visible = No. Then in the Report Footer section, add a text box with a control source of:
=txtMoneyRunningSum


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]