best practices?

I have the code below in the format section of a report
 Me.txtTotalEmp is an unbound control should display the total of a specific 
employee.  My problem is that if the report for a single employee spills over 
to a second sheet, me.txtTotalEmp printed is 0 while the single sheets totals 
are correct for the employee, but if I put the code in the _print section of 
the employee footer, then sometimes the total amount is outrageously 
incorrect on some employees and correct on others.  How can I correct the 
problem? What is the best practice for determining when to use "on format" or 
"on print" events.


Private Sub GroupFooter1_format(Cancel As Integer, FormatCount As Integer)
On Error GoTo ErrorRoutine

    Me.txtTotalEmp = dblEmpHours
    dblTotHours = dblTotHours + dblEmpHours
    dblEmpHours = 0

Exit_Sub:
    Exit Sub
ErrorRoutine:
    Call LogError(Err.Number, Err.Description, "Report: rptPayrollDetail; 
Sub: GroupFooter1_Format", , True)
    Resume Exit_Sub
End Sub
0
Utf
4/27/2010 4:04:01 PM
access 16762 articles. 3 followers. Follow

3 Replies
752 Views

Similar Articles

[PageSpeed] 27

The problem with computations in the Format event procedures is that the
event can fire multiple times, and the FormatCount property can be 1 each
time, so that cannot be relied on.  When a group runs over onto a second page
the Retreat event procedure can be used to undo extra computations, but it
can be tricky.

The print event procedures are normally more reliable, but should be used
consistently, i.e. the incrementing of the variable should be undertaken in
the Print event procedure, usually of the detail section, as well as
assigning the value to a control in the footer's print event procedure.  When
incrementing the variable in the details section's event procedure the
PrintCount property should be examines to avoid inadvertent double counting,
e.g.

If PrintCount = 1 Then
    dblEmpHours = dblEmpHours + Me.EmpHours
End If

However, its frequently possible to avoid using code by including a hidden
text box in the detail section bound to the field being totalled, EmpHours in
the above example, with its RunningSum property set to 'Over Group'.  Then in
the footer include an unbound text box whose ControlSource references the
hidden control in the detail section e.g.

=[txtEmpHoursHidden]

The control in the footer will take the value from the last instance of the
txtEmpHoursHidden control, i.e. the total value for the group.  The same
principle can be applied to get a grand total by having another hidden
control in the detail section with its RuningSum 'Over All' and referncing
this in the report footer.

Using a running sum like this is generally unnecessary, however, as you could
simply have a control in the group or report footer with a ControlSource of:

=Sum([EmpHours])

If the employee hours per detail are the result of an expression then the
expression should be used as the Sum function's argument.

Ken Sheridan
Stafford, England

SuzyQ wrote:
>I have the code below in the format section of a report
> Me.txtTotalEmp is an unbound control should display the total of a specific 
>employee.  My problem is that if the report for a single employee spills over 
>to a second sheet, me.txtTotalEmp printed is 0 while the single sheets totals 
>are correct for the employee, but if I put the code in the _print section of 
>the employee footer, then sometimes the total amount is outrageously 
>incorrect on some employees and correct on others.  How can I correct the 
>problem? What is the best practice for determining when to use "on format" or 
>"on print" events.
>
>Private Sub GroupFooter1_format(Cancel As Integer, FormatCount As Integer)
>On Error GoTo ErrorRoutine
>
>    Me.txtTotalEmp = dblEmpHours
>    dblTotHours = dblTotHours + dblEmpHours
>    dblEmpHours = 0
>
>Exit_Sub:
>    Exit Sub
>ErrorRoutine:
>    Call LogError(Err.Number, Err.Description, "Report: rptPayrollDetail; 
>Sub: GroupFooter1_Format", , True)
>    Resume Exit_Sub
>End Sub

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access/201004/1

0
KenSheridan
4/27/2010 6:20:00 PM
Best practice is to not perform calculations in Report Events... as Ken 
said, they may be fired multiple times, and it can be difficult, at best, to 
make them accurate. This has been the case since the very first version of 
Access.  If you want to see some outlandish results, accumulate a value in 
either the Format or Print event, display the report on-screen in Preview, 
then move back and forth in the pages.

To allow you to avoid this, there are calculation functions (Sum, Count, 
etc.) provided, the Running Sum property, and Domain Aggregate functions 
(DSum, DCount) that you can use... but not in event code.

 Larry Linson
 Microsoft Office Access MVP


"SuzyQ" <SuzyQ@discussions.microsoft.com> wrote in message 
news:3E1E9FE3-F893-4BBA-86D5-C337B5E8CA0A@microsoft.com...
>I have the code below in the format section of a report
> Me.txtTotalEmp is an unbound control should display the total of a 
> specific
> employee.  My problem is that if the report for a single employee spills 
> over
> to a second sheet, me.txtTotalEmp printed is 0 while the single sheets 
> totals
> are correct for the employee, but if I put the code in the _print section 
> of
> the employee footer, then sometimes the total amount is outrageously
> incorrect on some employees and correct on others.  How can I correct the
> problem? What is the best practice for determining when to use "on format" 
> or
> "on print" events.
>
>
> Private Sub GroupFooter1_format(Cancel As Integer, FormatCount As Integer)
> On Error GoTo ErrorRoutine
>
>    Me.txtTotalEmp = dblEmpHours
>    dblTotHours = dblTotHours + dblEmpHours
>    dblEmpHours = 0
>
> Exit_Sub:
>    Exit Sub
> ErrorRoutine:
>    Call LogError(Err.Number, Err.Description, "Report: rptPayrollDetail;
> Sub: GroupFooter1_Format", , True)
>    Resume Exit_Sub
> End Sub 


0
Larry
4/27/2010 11:42:47 PM
I will revisit my code, it has been a while since this piece was originally 
set, but if I remember right, there was some reason why using a box in the 
detail group was not sufficient for my purposes.  Generally I do use a 
control in the detail to get an accurate value in the footer.  I will post 
back if I continue to have issues.  Thanks.

"KenSheridan via AccessMonster.com" wrote:

> The problem with computations in the Format event procedures is that the
> event can fire multiple times, and the FormatCount property can be 1 each
> time, so that cannot be relied on.  When a group runs over onto a second page
> the Retreat event procedure can be used to undo extra computations, but it
> can be tricky.
> 
> The print event procedures are normally more reliable, but should be used
> consistently, i.e. the incrementing of the variable should be undertaken in
> the Print event procedure, usually of the detail section, as well as
> assigning the value to a control in the footer's print event procedure.  When
> incrementing the variable in the details section's event procedure the
> PrintCount property should be examines to avoid inadvertent double counting,
> e.g.
> 
> If PrintCount = 1 Then
>     dblEmpHours = dblEmpHours + Me.EmpHours
> End If
> 
> However, its frequently possible to avoid using code by including a hidden
> text box in the detail section bound to the field being totalled, EmpHours in
> the above example, with its RunningSum property set to 'Over Group'.  Then in
> the footer include an unbound text box whose ControlSource references the
> hidden control in the detail section e.g.
> 
> =[txtEmpHoursHidden]
> 
> The control in the footer will take the value from the last instance of the
> txtEmpHoursHidden control, i.e. the total value for the group.  The same
> principle can be applied to get a grand total by having another hidden
> control in the detail section with its RuningSum 'Over All' and referncing
> this in the report footer.
> 
> Using a running sum like this is generally unnecessary, however, as you could
> simply have a control in the group or report footer with a ControlSource of:
> 
> =Sum([EmpHours])
> 
> If the employee hours per detail are the result of an expression then the
> expression should be used as the Sum function's argument.
> 
> Ken Sheridan
> Stafford, England
> 
> SuzyQ wrote:
> >I have the code below in the format section of a report
> > Me.txtTotalEmp is an unbound control should display the total of a specific 
> >employee.  My problem is that if the report for a single employee spills over 
> >to a second sheet, me.txtTotalEmp printed is 0 while the single sheets totals 
> >are correct for the employee, but if I put the code in the _print section of 
> >the employee footer, then sometimes the total amount is outrageously 
> >incorrect on some employees and correct on others.  How can I correct the 
> >problem? What is the best practice for determining when to use "on format" or 
> >"on print" events.
> >
> >Private Sub GroupFooter1_format(Cancel As Integer, FormatCount As Integer)
> >On Error GoTo ErrorRoutine
> >
> >    Me.txtTotalEmp = dblEmpHours
> >    dblTotHours = dblTotHours + dblEmpHours
> >    dblEmpHours = 0
> >
> >Exit_Sub:
> >    Exit Sub
> >ErrorRoutine:
> >    Call LogError(Err.Number, Err.Description, "Report: rptPayrollDetail; 
> >Sub: GroupFooter1_Format", , True)
> >    Resume Exit_Sub
> >End Sub
> 
> -- 
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/Forums.aspx/access/201004/1
> 
> .
> 
0
Utf
4/28/2010 4:22:01 PM
Reply:

Similar Artilces:

Best settings for Recipient Filtering?
Hello! A friend has an Exchange 2003 Enterprise server that receives thousands of spam messages a day. He has Symantec Mail Security on it to filter spam. I want to know if there is a way to kill most of the spam before Symantec even sees it, perhaps with a combination of Recipient filtering and Connection Filtering. Per http://support.microsoft.com/kb/886208/en-us, Exchange 2003 on SBS 2003 is susceptible to reverse NDR attacks. I want to prevent Exchange 2003 Enterprise from reverse NDR attacks if it is also vulnerable when running on Windows Server 2003 Standard. Is Exchange 2003 E...

best way to make linked database
presently I am using a database where I am linked into tables from Other databases on the server - I need to be able to just update daily or everyother day with the newest records added by the other people on the other databases - and to NOT be Linked in on a daily basis - I have a laptop and need to use it on the road and our remote connection is still a nightmare - Bottom line - need My database to be standalone and just UPDATE- Append?? newest records what do I need to do with the LInked tables??? Also I have ton of table presently in my database that other people Input - ...

Baume and Mercier Hamptons Classic Square Black Mens Watch 8678, Best Wristwatch World
Baume and Mercier Hamptons Classic Square Black Mens Watch 8678, Best Wristwatch World Click Here To Website : http://www.watchebay.net/Baume-and-Mercier-Hamptons-Classic-Square-Black-Mens-Watch-8678.html Wristwatch World: http://www.watchebay.net/ Baume and Mercier Hamptons Classic Square Black Mens Watch 8678 Information : Brand : Baume & Mercier Watches ( http://www.watchebay.net/Baume-Mercier-Watches.html ) Gender : Mens Code : Baume-8678 Also Called : MOA8678, 8678, Baume-Mercier-8678, Baume-et- Mercier-8678 Case Material : Stainless Steel Case...

NetApp v3140 and Transaction Log placement Best Pratice
Given the unique nature of how NetApps manage the data in their SAN arrays, what is the recommended Best Practices for Transaction Log placement in this scenario? The old school says to place the Transaction Logs on separate disks, preferably Raid1. But, in this big pool of disks where writes happen all over the place, I argued with the tech guys referencing this way of thinking but the original placement of the logs still ended up on the same big set of disks as the datafiles... I am forcing them to change the log file placement for safety reasons but they are planning on s...

Best book for Microsoft Publisher 2003
There aren't many (any?) books on Microsoft Publisher 2003. What's up with that? It looks like I'll have to settle for an all-inclusive Office book that happens to include Microsoft Publisher 2003. From what I've read so far, the most recommended book is "MS Office Systems 2003 Inside and Out." Unless I hear otherwise, that's the one I'll buy. Or perhaps I should just buy a different publishing software. So, suggestion solicited, good book or new software? Anyone please! Thanks. In news:986b88e6.0404121632.6397861d@posting.google.com, Joe Wasik <joew...

Practice Database
I am a sys admin not a DBA. But I have come to the conclusion that I need to learn more about SQL 2005 that supports some of our critical info. I don't think we are getting the straight story from our vendor who is managing the database. For example our SQL Server is running an average of over 3000 pages/sec and when I point out that this is awfully high I just get a "so what" shrug. I have a test domain with SQL 2005 installed on one of the servers. Is there a sample or test database that I could import in to use in learning more about SQL 2005? Specifically the...

what type of virus protection is best for exchange
i have a newly installed exchange server and i need some sugestions on virus protection for the server. we currently run nav enterprise on our lan, but i have not install the client on the exchage server yet. Do i need to download some special patched from norton and MS or you i just install the nav client on my exchange I've found Trend-Micro has a nice package. "Set it, and forget it". About half the price of the others, and way easier to manage. www.trendmicro.com NeatSuite package is the best, Or for just Exchange get their ScanMailo for Exchange package. "James&qu...

DRP
Hi all, I'm using Ex2003 standard, which is only supporting 25 users. I intend to set up a branch office as a disaster recovery site with some fast comms links and duplicate hardware. At present I have dedicated servers for DC's (2), SQL (1), Ex2003(1), Data (1), Terminal Services (1). Because the DR site only needs to support 3-5 users and it is likely we would relocate within 3 days of any disaster I am going to install a single server of very decent spec. i.e. dual xeon / terrabyte, 4gb ram etc. etc. My question is this. Is there an relatively straight forward way of ...

Best Practice?
Hello, In another thread I found the following quotation: "You should only use try/catch. File.Exists() is a waste of time, because you always _have_ to have the try/catch block anyway. Even if File.Exists() returns true, the file could disappear before you can actually open it and load it as an XDocument, and you could experience other errors as well." I want to know in common if it is better to _only_ catch exceptions instead of verifying dependencies like existing files before an operation. I would go a run through (and bookmark) this article: http://blogs....

Which credit card gives the best rewards ?
Which credit card gives the best rewards ? I have had a Capital One (US) visa card for many years. I always pay my visa bill on time. I am fed up of them. In the last 2 months they have stopped 6 transactions. When I call them they claim they suspected a fraudulent person was using the card. Even though I have always used my card to pay companies like State Farm...their computers flagged it as fraud. I cannot depend on them. They will probably not be there when I really need them. They are too unreliable for me. Anyway, I want a new credit card ? Can any one recommend a credit card that...

Best C# Book for VBA Programmers
I have been developing in Excel/VBA/SQL for 10+ years and have a little VB.NET. I have a pretty good - but certainly not great - understanding of OOD & OOP. I want to get into C# as more and more Jobs that I'm interested in are looking for +/or expecting it but I have zero knowledge of C of any flavour. Given my background I would value all your thoughts on which is the best book(s) to buy. Or are there some ace WebSites / Parts of MSDN that I could use to achieve the same end? Thanks in advance Chris I would reccomend learning a non-microsoft language. Microsoft has proven time...

Best practices / approach for Windows apps using Access?
Hello experts, We are normally used to coding to SQL Server, but have a requirement to write a Win32 app that will store upwards of several hundred thousand rows in an Access database per run. (Initial run could be hundreds of thousands of records, subsequent runs would update/add/delete columns and rows). This app would make use of SQL TRANSACTIONS to be able to commit or rollback entire runs. We expect that we would need to use an ODBC Jet engine for I/O in order to be the most compatible with the most client machines. Can you offer any advise, warnings or other potential issues we ...

So is Outlook Express no longer best way to read/use NG?
If you use the MS provided web interface, you can filter & create by type; Question, Suggestion or Comment. Is everything posted from Outlook Express going to show up as a Comment instead of a Question? Seems like this should be the exact opposite; as I've seen more Questions than Comments! ...

Best way to get order history with detail line status
Thanks to Victoria [MVP] for some good reading material on getting sales history in general. I need to extract the latest status for a given detail line for web orders from great plains. I think I can join from the SOP header (SOP10100 or SOP30200) to itself using the master number to get all related orders and then join to the detail lines, getting status, etc by picking the matching detail row with the latest dexterity time stamp. This is one of the selects I came up with unfortunately I am not seeing any backordered items on here. Can anyone tell me how to fix this or a better way to ge...

Re: Exchange Server Best Practices Analyzer Tool ExBPAv2.5
....and it would be helpful if I noted - I have not seen a problem with the application on XP SP2. Bob -- Bob Christian II MVP - LCS http://bobchristian.blogspot.com - Blog "Bob Christian" <BobChristian@removethis.gmail.com> wrote in message news:... > You may want to check your post in exchange.tools. Paul responded in > there. > > -- > Bob Christian II > MVP - LCS > http://bobchristian.blogspot.com - Blog > > > > "Nawar75" <Nawar75@discussions.microsoft.com> wrote in message > news:0886D939-6E16-406D-95F6-B6F4A...

Best accounting pkg for integration with RMS HQ for Canadians?
Can anyone recommend what accounting package integrates best in an RMS headquarter environment for the Canadian market?' thanks. may be Microsoft Dynamics Great Plains can help you but its not only an accounting package its a complete ERP. There are two ways that Microsoft recomends to integrate this solutions with HQ and within. -- Retaileando "kirk" wrote: > Can anyone recommend what accounting package integrates best in an RMS > headquarter environment for the Canadian market?' > thanks. ...

Exchange Best Practice Util
I ran the Exchange Best Practice Util and I received the following error. I'm unsure where or why to make this change: Missing FQDN in 'Default SMTP Virtual Server' service principal name Server: FE1 The computer account for Exchange server FE1.NA.OSIENT.COM does not appear to contain the fully-qualified domain name of Exchange SMTP virtual server 'Default SMTP Virtual Server'. This may cause Kerberos authentication to fail when sending messages between servers. The tool expected to find 'SMTPSVC/mail1.osi-systems.com' in the servicePrincipalName. i'...

Chopard Happy Sport Diamond 18kt Yellow Gold And Steel Ladies Watch 27/8249-23, Best Wristwatch World
Chopard Happy Sport Diamond 18kt Yellow Gold And Steel Ladies Watch 27/8249-23, Best Wristwatch World Click Here To Website : http://www.watchebay.net/Chopard-Happy-Sport-Diamond-18kt-Yellow-Gold-And-Steel-Ladies-Watch-27-8249-23.html Wristwatch World: http://www.watchebay.net/ Chopard Happy Sport Diamond 18kt Yellow Gold And Steel Ladies Watch 27/8249-23 Information : Brand : Chopard Watches ( http://www.watchebay.net/Chopard-Watches.html ) Gender : Ladies Code : Chopard-27-8249-23-WH Also Called : 27/8249-23 Case Material : 18kt Yellow Gold And Stainl...

Best Way to Create Log File?
Basically I want to take information from a bunch of PO files in Excel and extract the information I need into another file, to create a general list of all the information. Right now the only way I can think to do this is to use Microsoft Query. And the only reasonable way i can think to do it is actually the opposite to create a list and then put it into the PO form. (We just want to make it so that people don't have to enter the data twice.) Is there an easier way to do this? One way to build your log of existing po's is to open each of the workbooks and extract the data. ...

What is the best way to get query objects updated
Hi all. I have a created some workbooks containing query objects which will retrieve data from a SQL database. What is the best way to refresh data from the database *without* opening the workbooks manually in Excel? The obvious solution to me is to write a small VB program (not VBA) that will open, refresh queried data, and save the workbook periodically. Any info would be appreciated. Thanks. tempest@ucla.edu wrote: > I have a created some workbooks containing query objects which will > retrieve data from a SQL database. What is the best way to refresh > data from the datab...

How practical is it to use Money program? How do you do it?
I am interested to see how different people use this program as I have a copy and have been only dabbling in it and never seriously using it. Do you only download your statements, sort them into catagories and just monitor your finances? Do you manually enter every bill into the program, then reconcile every week? Do you use it weekly or monthly? Do you print cheques from it? I would be interested to see just how practical it is to do all this in everyday life. Many thanks to all who answer. KJTV Answers inline. "KJTV" <KJTV@discussions.microsoft.com> wrote in messa...

Best Opperating System for CRM 3.0?
My company has fully adopted CRM 3.0, we are a manufacturer and equipment sales company. We have customized CRM to meet our needs and are now looking into updating our opperating systems from our current one 'Minitrac' We are looking into GP for Accounting & Manufacturing and a product called Wennsoft for the equipment Management. Conversly we are contemplating Nav for Accounting and Manufacturing and a product called Equipsoft for the equipment Management. Can anyone help point us in the best direction as per your experience with any of these products? Well you really...

Best way to archive
Hi, I'm using Outlook 2003 and connecting to around 10 different email accounts on my mail server using IMAP. Each accounts is over 1GB in size and I have quite a few message rules running on Outlook to direct email to specified folders. My problem is that Outlook is really starting to slow down now and often hangs for a while while processing is done. I think the next stage may be to archive come of my older my email (i'm holding around 3 years worth). Can anyone recommend the best way of archiving the email of any other ways to improve performance? Thanks - joe. Hi Joe, ...

Resource Best Practice
I am looking for some information about Resorce Scheduling Best Practice. Simple things like, do you use names like _Conference Room or ~Conference Room to help sort Resources seperately. Any chance there is a web site or white paper somewhere on the topic? Thanks, Gordon ...

Best way to copy data across ?
End users are using an Access 2003 Database with both front end and back end. There is a requirement to add fields in a particular table and change the forms and reports accordingly. I have made a new copy of both front end and back end while end users are still using the database. I would like to know what is the best way to copy all data from production to the newly developed backend ? (How to import data to table) ? Thanks Why not just alter the existing backend? Kick everyone off the system briefly and make the changes. Alternatively, write VBA code to make the changes for y...