Solved

OnFormat of report doubles values

Posted on 2008-10-15
10
277 Views
Last Modified: 2013-11-28
I have heard that in some cases you need to clear fields in the OnFormat of a report because it runs twice.  Once when you preview and again on print.  
I've attached the code I use in my report because it only works if I go to the last page of the report and then print.  If I just preview and print it still doubles the field in the report footer.  Is there something more I can do with my code?
Thanks

Private Sub GroupHeaderCounty_Print(Cancel As Integer, PrintCount As Integer)
    curTotal = Nz(curTotal + Me.txtTarget)
End Sub
 
Private Sub ReportFooter_Format(Cancel As Integer, FormatCount As Integer)
    Me.txtTotTarget = curTotal
End Sub
 
Private Sub ReportFooter_Print(Cancel As Integer, PrintCount As Integer)
       curTotal = 0
End Sub

Open in new window

0
Comment
Question by:BobRosas
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
10 Comments
 
LVL 4

Expert Comment

by:Rodger
ID: 22724943
try using the onRetreat see if that helps.
0
 

Author Comment

by:BobRosas
ID: 22725003
Would you please explain how I use OnRetreat?
I tried clearing there (curTotal = 0) but it still doubles everything.
Thanks for your quick response.
0
 
LVL 4

Expert Comment

by:Rodger
ID: 22725368
Instead of putting the code in the onFormat for your footer try putting it in the onRetreat in the Detail section.  Does that help?
0
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

Author Comment

by:BobRosas
ID: 22725523
When I tried that the field came up blank.
If you have any other suggestions I'd be glad to try.
Thanks again!
0
 
LVL 4

Expert Comment

by:Rodger
ID: 22725988
OK let's start again.  What is it that you are counting?  What happens if you place this as the control for the unbound field in the footer =Count(txtTarget) .  What is txtTarget?
0
 

Author Comment

by:BobRosas
ID: 22726441
I appreciate your help.  I hope I can explain this.  
My report shows (by County) how many Clients took advantage of our Services.  We have projected targets for each County.  When I create the record source for the report I just repeat the Target for each record.  That works fine for County Total because I don't sum the value.  But now I want a total at the end of the report and a sum adds all the records not each County.  Having the info in the Format and print section (as shown above) I thought was working but it only works if I'm on the last page.  I hope that makes sense.  
Thanks again!

County 1
   ServiceA    5
   ServiceB     8
   Total County1 =13      Projected=15   Dif = 3
County2
   ServiceC    3
   ServiceD    9
   Total Count2=12      Projected =10  Dif = -2
County3
   ServiceB    7
   Total County3 = 7    Projected = 10   Dif = 3

Total all Counties  = 32          Projected = 35
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 22727984
BobRosas,

Let's go back to the beginning.
"I have heard that in some cases you need to clear fields in the OnFormat of a report because it runs twice. "

1. In 10 years in working with Access, I have never heard this.
Can you please elaborate?
I wouldn't change my report based on something I "heard", unless I could verify it.

2. Besides, what are you trying to accomplish with you report that you need to "Reset" anything?

3. Since since you posted absolutely no info on how your report is structured,
it is impossible to determine if your Report is even designed properly.

Perhaps RodgerDjr, can get you on the right track, ... I would like to see a sample db so we don't waste time "guessing" what you have.

Make sense?
;-)

JeffCoachman
0
 

Author Comment

by:BobRosas
ID: 22733343
If you refer to my original post maybe that will clarify what I'm trying accomplish.  I thought the code from this post solved the problem because I was always previewing the totals before I printed.  But if I don't preview the last page then the code doesn't work and the amounts double.

I agree I shouldn't believe everything I read but I really did think it was working and besides you EE guys are very good!

Got code from my original post below...
http://www.experts-exchange.com/Microsoft/Development/MS_Access/Access_Reports/Q_23370284.html

Where I got the idea it runs twice I got from here...
http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_11932078.html?sfQueryTermInfo=1+doubl+format+print+report
0
 
LVL 4

Accepted Solution

by:
Rodger earned 125 total points
ID: 22733489
Bob,

OK lets try this.

Make County a Group with a header and footer.
Make Service this a Group with a header and footer.
In the Footer for Service Sum(Service) then you can place the difference Sum(Service) - Projected
And then place these in the Report Footer of the report.
However like Jeff said it could help if you post your DB then we could figure it out instead of  "guessing".
Rodger
0
 

Author Closing Comment

by:BobRosas
ID: 31506472
I'll give this a shot and repost as needed.
Thanks for your help.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question