Solved

OnFormat of report doubles values

Posted on 2008-10-15
10
276 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
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!

 

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

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
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…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
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…

726 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