Solved

OnFormat of report doubles values

Posted on 2008-10-15
10
278 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
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

623 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