Solved

OnFormat of report doubles values

Posted on 2008-10-15
10
275 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
  • 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
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…

837 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