How do I reset variables that accumulate in section footers?

[Guest]

I have a report that I have been struggling with where I compute regular and
overtime hours for my employees. I have worked out most of the kinks with
the exception of getting totals at the employee level.

In my report, in the Detail section I track regular and overtime hours using
the following VBA code:


Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)

If (TotalHrs > 0 And TotalHrs < 10) Then OT = 0 Else OT = (TotalHrs - 10)
WeekOT = WeekOT + OT
WeekReg = WeekHours - WeekOT

End Sub

I've tried doing this several ways, but this seemed to work best in light of
other things I'm doing.

The shifts that an employee works are grouped by Day and then by Work Week.

At the end of each day's records for an employee, I compute subtotals that
total their regular and overtime hours 4. In addition, I add up the
employee's hours for the week and at the end of the week reassign hours to OT
if the employee has worked more than 40 hours in that week. I used the
following code to do this:

Private Sub WorkWeekFooter_Print(Cancel As Integer, PrintCount As Integer)

If WeekReg > 40 Then WeekOT = WeekOT + (WeekReg - 40): WeekReg = 40 Else
WeekReg = WeekReg
EmployeeReg = EmployeeReg + WeekReg
EmployeeOT = EmployeeOT + WeekOT

End Sub

That all works great. I also have the following code to reinitialize the
variables:

Private Sub NickNameHeader_Print(Cancel As Integer, PrintCount As Integer)
WeekOT = 0
WeekReg = 0
EmployeeReg = 0
EmployeeOT = 0

End Sub

Everything works EXCEPT when an employees records extend over more than one
page. In that case, the Employee totals are only for the days on the current
page. I figure I must be reinitializing my EmployeeReg and EmployeeOT
variables in the wrong place, but everything else I've tried screws up my
totals even more.

What I want to do is reset the EmployeeReg and EmployeeOT variables to 0
whenever I start processing the next employee's records.

Any suggestions of what to do to fix this?

Kim

 

[Marshall Barton]

I have a report that I have been struggling with where I compute regular and
overtime hours for my employees. I have worked out most of the kinks with
the exception of getting totals at the employee level.

In my report, in the Detail section I track regular and overtime hours using
the following VBA code:


Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)

If (TotalHrs > 0 And TotalHrs < 10) Then OT = 0 Else OT = (TotalHrs - 10)
WeekOT = WeekOT + OT
WeekReg = WeekHours - WeekOT

End Sub

I've tried doing this several ways, but this seemed to work best in light of
other things I'm doing.

The shifts that an employee works are grouped by Day and then by Work Week.

At the end of each day's records for an employee, I compute subtotals that
total their regular and overtime hours 4. In addition, I add up the
employee's hours for the week and at the end of the week reassign hours to OT
if the employee has worked more than 40 hours in that week. I used the
following code to do this:

Private Sub WorkWeekFooter_Print(Cancel As Integer, PrintCount As Integer)

If WeekReg > 40 Then WeekOT = WeekOT + (WeekReg - 40): WeekReg = 40 Else
WeekReg = WeekReg
EmployeeReg = EmployeeReg + WeekReg
EmployeeOT = EmployeeOT + WeekOT

End Sub

That all works great. I also have the following code to reinitialize the
variables:

Private Sub NickNameHeader_Print(Cancel As Integer, PrintCount As Integer)
WeekOT = 0
WeekReg = 0
EmployeeReg = 0
EmployeeOT = 0

End Sub

Everything works EXCEPT when an employees records extend over more than one
page. In that case, the Employee totals are only for the days on the current
page. I figure I must be reinitializing my EmployeeReg and EmployeeOT
variables in the wrong place, but everything else I've tried screws up my
totals even more.

What I want to do is reset the EmployeeReg and EmployeeOT variables to 0
whenever I start processing the next employee's records.

The right thing to do with this approach is to scrap it and
use the technique I suggested in your earlier thread.

Since Access processes each section in whatever order and
however many time it needs to generate a report, there is no
way to use VBA in event procedures to accumulate a total
across multiple details.