Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

OnFormat of report doubles values

Posted on 2008-10-15
10
Medium Priority
?
285 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
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 

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 500 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

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

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

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…
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

886 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