Running total doubles on first line of each new page

[Guest]

I've run into a odd issue with a running total field. I am calling a VBA
script from the reports Detail section in the On Format event. I need to
perform the running total here, because I am using the running total to
compare against estimated available hours after each iteration.

The variables [tmpSumEmpHours] and [tmpTotEmpHours] are defined as private:
-----------------------
Option Compare Database
Option Explicit

Private tmpTotEmpHours As Single
Private tmpSumEmpHours As Single
-----------------------

The code that increments [tmpSumEmpHours] is as follows:
-----------------------
If tmpRescName = Me.RescName Then
If tmpSumEmpHours > 0 Then
tmpSumEmpHours = tmpSumEmpHours + tmpTotEmpHours
Else
tmpSumEmpHours = tmpTotEmpHours
End If
Else
tmpSumEmpHours = tmpTotEmpHours
End If
-----------------------
There are two unique (but related) situations that occur when the report
starts on a new page. The problem does not occur on the first page:

1) If the RescName is the same, on a new page, on the first line ONLY of the
page, 2 x [tmpTotEmpHours] are added to [tmpSumEmpHours]. Why? On all
subsequent lines on the page the math works correctly.
2) If the RescName is not the same (i.e. a new RescName), 2 x
[tmpTotEmpHours] are added to [tmpSumEmpHours]. First line only again.
Again... why? On all subsequent lines on the page the math works correctly.

Could someone point me in the direction to look, or could this possibly be a
wierd bug with Access?

 

[Marshall Barton]

I've run into a odd issue with a running total field. I am calling a VBA
script from the reports Detail section in the On Format event. I need to
perform the running total here, because I am using the running total to
compare against estimated available hours after each iteration.

The variables [tmpSumEmpHours] and [tmpTotEmpHours] are defined as private:
-----------------------
Option Compare Database
Option Explicit

Private tmpTotEmpHours As Single
Private tmpSumEmpHours As Single
-----------------------

The code that increments [tmpSumEmpHours] is as follows:
-----------------------
If tmpRescName = Me.RescName Then
If tmpSumEmpHours > 0 Then
tmpSumEmpHours = tmpSumEmpHours + tmpTotEmpHours
Else
tmpSumEmpHours = tmpTotEmpHours
End If
Else
tmpSumEmpHours = tmpTotEmpHours
End If
-----------------------
There are two unique (but related) situations that occur when the report
starts on a new page. The problem does not occur on the first page:

1) If the RescName is the same, on a new page, on the first line ONLY of the
page, 2 x [tmpTotEmpHours] are added to [tmpSumEmpHours]. Why? On all
subsequent lines on the page the math works correctly.
2) If the RescName is not the same (i.e. a new RescName), 2 x
[tmpTotEmpHours] are added to [tmpSumEmpHours]. First line only again.
Again... why? On all subsequent lines on the page the math works correctly.

Could someone point me in the direction to look, or could this possibly be a
wierd bug with Access?

There is no direction to make that work reliably. Report
sections are not processed in sequential order for many
reasons. Your particular problem is just one of several
ways that kind of code can go wrong.

The "reliable" way to accumulate a total as you go is to use
a running sum text box.

 

[Guest]

I've run into a odd issue with a running total field. I am calling a VBA
script from the reports Detail section in the On Format event. I need to
perform the running total here, because I am using the running total to
compare against estimated available hours after each iteration.

The variables [tmpSumEmpHours] and [tmpTotEmpHours] are defined as private:
-----------------------
Option Compare Database
Option Explicit

Private tmpTotEmpHours As Single
Private tmpSumEmpHours As Single
-----------------------

The code that increments [tmpSumEmpHours] is as follows:
-----------------------
If tmpRescName = Me.RescName Then
If tmpSumEmpHours > 0 Then
tmpSumEmpHours = tmpSumEmpHours + tmpTotEmpHours
Else
tmpSumEmpHours = tmpTotEmpHours
End If
Else
tmpSumEmpHours = tmpTotEmpHours
End If
-----------------------
There are two unique (but related) situations that occur when the report
starts on a new page. The problem does not occur on the first page:

1) If the RescName is the same, on a new page, on the first line ONLY of the
page, 2 x [tmpTotEmpHours] are added to [tmpSumEmpHours]. Why? On all
subsequent lines on the page the math works correctly.
2) If the RescName is not the same (i.e. a new RescName), 2 x
[tmpTotEmpHours] are added to [tmpSumEmpHours]. First line only again.
Again... why? On all subsequent lines on the page the math works correctly.

Could someone point me in the direction to look, or could this possibly be a
wierd bug with Access?

There is no direction to make that work reliably. Report
sections are not processed in sequential order for many
reasons. Your particular problem is just one of several
ways that kind of code can go wrong.

The "reliable" way to accumulate a total as you go is to use
a running sum text box.

That was a good suggestion, but the same result occurs.
Is there a property setting that I might need to worry about?
Your comment that the records are not processed sequentially concerns me.
However, the report is fed by a SQL query that groups the records by
department first, then employee. So it appears the records are processed in
the correct order (except for my new page bug).

Might you have another suggestion?
Thank you for your time and help.

 

[Marshall Barton]

I've run into a odd issue with a running total field. I am calling a VBA
script from the reports Detail section in the On Format event. I need to
perform the running total here, because I am using the running total to
compare against estimated available hours after each iteration.

The variables [tmpSumEmpHours] and [tmpTotEmpHours] are defined as private:
-----------------------
Option Compare Database
Option Explicit

Private tmpTotEmpHours As Single
Private tmpSumEmpHours As Single
-----------------------

The code that increments [tmpSumEmpHours] is as follows:
-----------------------
If tmpRescName = Me.RescName Then
If tmpSumEmpHours > 0 Then
tmpSumEmpHours = tmpSumEmpHours + tmpTotEmpHours
Else
tmpSumEmpHours = tmpTotEmpHours
End If
Else
tmpSumEmpHours = tmpTotEmpHours
End If
-----------------------
There are two unique (but related) situations that occur when the report
starts on a new page. The problem does not occur on the first page:

1) If the RescName is the same, on a new page, on the first line ONLY of the
page, 2 x [tmpTotEmpHours] are added to [tmpSumEmpHours]. Why? On all
subsequent lines on the page the math works correctly.
2) If the RescName is not the same (i.e. a new RescName), 2 x
[tmpTotEmpHours] are added to [tmpSumEmpHours]. First line only again.
Again... why? On all subsequent lines on the page the math works correctly.

Could someone point me in the direction to look, or could this possibly be a
wierd bug with Access?

There is no direction to make that work reliably. Report
sections are not processed in sequential order for many
reasons. Your particular problem is just one of several
ways that kind of code can go wrong.

The "reliable" way to accumulate a total as you go is to use
a running sum text box.

That was a good suggestion, but the same result occurs.
Is there a property setting that I might need to worry about?
Your comment that the records are not processed sequentially concerns me.
However, the report is fed by a SQL query that groups the records by
department first, then employee. So it appears the records are processed in
the correct order (except for my new page bug).

It's fairly difficult to mess up a running sum text box, so
I have to suspect that you still have your code in there or
the text box is setup in an unusual way. If you'll provide
a detailed description of what you are using for the text
box's properties:
Name
ControlSource
RunningSum

If the ControlSource is an expression that refers to another
control in the report, where is that control and what is its
ControlSource? Note that if a report control and a record
source field have the same name, the control is what's used.

Where a control that references a running sum text box is
located is also important (depending on the version of
Access).

If I get enough specific information instead of hand waving
"same results", I might be able to figure out what might be
going wrong.

It doesn't matter what order the records are in the query,
Access has to process them according to your property/group
settings and user interactions to present an efficient and
correctly formatted result. If you can't take my word for
it, think about what happens when a CanGrow section crosses
a page boundary and the group's KeepTogether property
requires the entire group be reformatted to a new page.
Even after the report preview is started, a user might
navigate to the last page and then back to the beginning so
the order of processing can be up to a user's whims.

 

[Guest]

AlmostAdjusted wrote:

I've run into a odd issue with a running total field. I am calling a VBA
script from the reports Detail section in the On Format event. I need to
perform the running total here, because I am using the running total to
compare against estimated available hours after each iteration.

The variables [tmpSumEmpHours] and [tmpTotEmpHours] are defined as private:
-----------------------
Option Compare Database
Option Explicit

Private tmpTotEmpHours As Single
Private tmpSumEmpHours As Single
-----------------------

The code that increments [tmpSumEmpHours] is as follows:
-----------------------
If tmpRescName = Me.RescName Then
If tmpSumEmpHours > 0 Then
tmpSumEmpHours = tmpSumEmpHours + tmpTotEmpHours
Else
tmpSumEmpHours = tmpTotEmpHours
End If
Else
tmpSumEmpHours = tmpTotEmpHours
End If
-----------------------
There are two unique (but related) situations that occur when the report
starts on a new page. The problem does not occur on the first page:

1) If the RescName is the same, on a new page, on the first line ONLY of the
page, 2 x [tmpTotEmpHours] are added to [tmpSumEmpHours]. Why? On all
subsequent lines on the page the math works correctly.
2) If the RescName is not the same (i.e. a new RescName), 2 x
[tmpTotEmpHours] are added to [tmpSumEmpHours]. First line only again.
Again... why? On all subsequent lines on the page the math works correctly.

Could someone point me in the direction to look, or could this possibly be a
wierd bug with Access?


There is no direction to make that work reliably. Report
sections are not processed in sequential order for many
reasons. Your particular problem is just one of several
ways that kind of code can go wrong.

The "reliable" way to accumulate a total as you go is to use
a running sum text box.

That was a good suggestion, but the same result occurs.
Is there a property setting that I might need to worry about?
Your comment that the records are not processed sequentially concerns me.
However, the report is fed by a SQL query that groups the records by
department first, then employee. So it appears the records are processed in
the correct order (except for my new page bug).

It's fairly difficult to mess up a running sum text box, so
I have to suspect that you still have your code in there or
the text box is setup in an unusual way. If you'll provide
a detailed description of what you are using for the text
box's properties:
Name
ControlSource
RunningSum

If the ControlSource is an expression that refers to another
control in the report, where is that control and what is its
ControlSource? Note that if a report control and a record
source field have the same name, the control is what's used.

Where a control that references a running sum text box is
located is also important (depending on the version of
Access).

If I get enough specific information instead of hand waving
"same results", I might be able to figure out what might be
going wrong.

It doesn't matter what order the records are in the query,
Access has to process them according to your property/group
settings and user interactions to present an efficient and
correctly formatted result. If you can't take my word for
it, think about what happens when a CanGrow section crosses
a page boundary and the group's KeepTogether property
requires the entire group be reformatted to a new page.
Even after the report preview is started, a user might
navigate to the last page and then back to the beginning so
the order of processing can be up to a user's whims.

Ah, thanks for diving into a bit more detailed explanation. I now see what
you intended by using the RunningSum. I've looked at my code, and it is
quite complicated, and to try to explain it via this furom would take too
long I fear.

Here's an attempt.

The purpose for executing the VBA code in the report's Detail_Format section
is I need to perform numerous calculations on specific date fields per
record.
- The report is a graphical display of project tasks by department by
employee and a growing graphical display of accumulating work days as
successive project tasks are completed.
- The output looks something like a basic Gantt Chart.
- The projects are selected via an entered/selected date range.
- I calculate the actual work days in the selected date range.
- Then I calculate the actual work days in the project that falls within the
date range.
- I also calculate two fixed fields (operational hours and Corporate
Projects) and create a base hours allocated by adding these two together.
- Then I calculate the hours budgeted for this employee for this project and
add them to the base (this starts the running totat I am having trouble with).
- Each project's budgeted hours are calculated and accumulated until all
projects are listed and the hours for the employee are listed and summed.
- All of this is graphically displayed just above each project's Gantt bar.
- When the employee's cummulative hours exceed the total available work
hours within the selected date range, those projects are flagged graphically.
- The issue I have with a RunningSum report field is that I perform way to
many calculations in VBA before I even arrive to the hours I need to
accumulate, I am not sure how it would work.
- The report itself actually works quite well and it is very informative
(except for the bug)
- To get an actual understanding, you would need to see the code and the
report.

I could zip up the DB and email it and take the discussion offline.

I would also understand, if you said this is just too much.
Please let me know.
Thank you very much.

 

[Marshall Barton]

:

AlmostAdjusted wrote:

I've run into a odd issue with a running total field. I am calling a VBA
script from the reports Detail section in the On Format event. I need to
perform the running total here, because I am using the running total to
compare against estimated available hours after each iteration.

The variables [tmpSumEmpHours] and [tmpTotEmpHours] are defined as private:
-----------------------
Option Compare Database
Option Explicit

Private tmpTotEmpHours As Single
Private tmpSumEmpHours As Single
-----------------------

The code that increments [tmpSumEmpHours] is as follows:
-----------------------
If tmpRescName = Me.RescName Then
If tmpSumEmpHours > 0 Then
tmpSumEmpHours = tmpSumEmpHours + tmpTotEmpHours
Else
tmpSumEmpHours = tmpTotEmpHours
End If
Else
tmpSumEmpHours = tmpTotEmpHours
End If
-----------------------
There are two unique (but related) situations that occur when the report
starts on a new page. The problem does not occur on the first page:

1) If the RescName is the same, on a new page, on the first line ONLY of the
page, 2 x [tmpTotEmpHours] are added to [tmpSumEmpHours]. Why? On all
subsequent lines on the page the math works correctly.
2) If the RescName is not the same (i.e. a new RescName), 2 x
[tmpTotEmpHours] are added to [tmpSumEmpHours]. First line only again.
Again... why? On all subsequent lines on the page the math works correctly.

Could someone point me in the direction to look, or could this possibly be a
wierd bug with Access?


There is no direction to make that work reliably. Report
sections are not processed in sequential order for many
reasons. Your particular problem is just one of several
ways that kind of code can go wrong.

The "reliable" way to accumulate a total as you go is to use
a running sum text box.


That was a good suggestion, but the same result occurs.
Is there a property setting that I might need to worry about?
Your comment that the records are not processed sequentially concerns me.
However, the report is fed by a SQL query that groups the records by
department first, then employee. So it appears the records are processed in
the correct order (except for my new page bug).

It's fairly difficult to mess up a running sum text box, so
I have to suspect that you still have your code in there or
the text box is setup in an unusual way. If you'll provide
a detailed description of what you are using for the text
box's properties:
Name
ControlSource
RunningSum

If the ControlSource is an expression that refers to another
control in the report, where is that control and what is its
ControlSource? Note that if a report control and a record
source field have the same name, the control is what's used.

Where a control that references a running sum text box is
located is also important (depending on the version of
Access).

If I get enough specific information instead of hand waving
"same results", I might be able to figure out what might be
going wrong.

It doesn't matter what order the records are in the query,
Access has to process them according to your property/group
settings and user interactions to present an efficient and
correctly formatted result. If you can't take my word for
it, think about what happens when a CanGrow section crosses
a page boundary and the group's KeepTogether property
requires the entire group be reformatted to a new page.
Even after the report preview is started, a user might
navigate to the last page and then back to the beginning so
the order of processing can be up to a user's whims.

Ah, thanks for diving into a bit more detailed explanation. I now see what
you intended by using the RunningSum. I've looked at my code, and it is
quite complicated, and to try to explain it via this furom would take too
long I fear.

Here's an attempt.

The purpose for executing the VBA code in the report's Detail_Format section
is I need to perform numerous calculations on specific date fields per
record.
- The report is a graphical display of project tasks by department by
employee and a growing graphical display of accumulating work days as
successive project tasks are completed.
- The output looks something like a basic Gantt Chart.
- The projects are selected via an entered/selected date range.
- I calculate the actual work days in the selected date range.
- Then I calculate the actual work days in the project that falls within the
date range.
- I also calculate two fixed fields (operational hours and Corporate
Projects) and create a base hours allocated by adding these two together.
- Then I calculate the hours budgeted for this employee for this project and
add them to the base (this starts the running totat I am having trouble with).
- Each project's budgeted hours are calculated and accumulated until all
projects are listed and the hours for the employee are listed and summed.
- All of this is graphically displayed just above each project's Gantt bar.
- When the employee's cummulative hours exceed the total available work
hours within the selected date range, those projects are flagged graphically.
- The issue I have with a RunningSum report field is that I perform way to
many calculations in VBA before I even arrive to the hours I need to
accumulate, I am not sure how it would work.
- The report itself actually works quite well and it is very informative
(except for the bug)
- To get an actual understanding, you would need to see the code and the
report.

I could zip up the DB and email it and take the discussion offline.

I would also understand, if you said this is just too much.

I might have time later, but I am too busy now to analyze
someone else's db, especially when the requirements are this
complex. Let's try a few things first.

I don't know what kind of grouping you have in your report,
but consider these concepts:

Group header text boxes can be used to calculate any
aggregate values for the entire group. E.g. Count the
details, Sum a field (e,g, total hours/days), etc. These
can then be used in the header section's format event
procedure to calculate other group related values. Don't
forget that the report header can be used to aggregate
values for the entire report.

VBA code in detail section's Format event can be used to
calculate results based on values in the current record.
Since the values in the header text boxes are available to
all the sections in the group, you can calculate prorated
values and other group/detail interrelated values.

A running sum text box can be used to provide a running
total of a value for all the records in a group up to the
current record. You can even use VBA code in the detail's
Format event to set a running sum textbox's value so you can
accumulate values that far more complex then just summing a
field.

Combining all that in "clever" ways can accomplish most
complex multi-record calculations. Try to break your
calculations down into units of calculations using only
those "operators" and lt's see where you run into detailed,
specific problems.