If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
best practices?
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/For...ccess/201004/1 |
#3
|
|||
|
|||
best practices?
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" wrote in message ... 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 |
#4
|
|||
|
|||
best practices?
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/For...ccess/201004/1 . |
Thread Tools | |
Display Modes | |
|
|