best practices?

[SuzyQ]

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

 

[Larry Linson]

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]

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.

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

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

--



.