ACC1x: Group Footer Controls Calculate Twice at Page Break

• Microsoft Access versions 1.0, 1.1

SYMPTOMS

A group footer control that references a calculated detail control returns incorrect results if the page break and group break occur at the same time. The group footer control is off by the value of the last detail record.

CAUSE

Reference to a control with the RunningSum property applied from a higher-level group footer causes the control's value to be added again for the last cached running sum and to return incorrect data. All cached field values are validated when you advance the page; this occurs before the record is advanced, determining the end of the group. Thus, the last record in a group is added twice.

RESOLUTION

From the OnPrint event of the Detail section, call a macro that uses the SetValue action to place the detail control's value in an unbound group footer control.

The Detail section has the following OnPrint properties and control:

   OnPrint: Macro1
   ControlName: DetailTotal
      RunningSum: OverGroup

   ControlName: FooterTotal
   ControlSource: <blank>

    SetValue
       Item=[FooterTotal]
       Expression=[DetailTotal]

STATUS

Microsoft has confirmed this to be a problem in Microsoft Access versions 1.0 and 1.1. This problem no longer occurs in Microsoft Access version 2.0.

MORE INFORMATION

Steps to Reproduce Problem

1. Create a grouping report.

2. Locate an unbound text box in the Detail section.

3. Set the ControlSource property of the text box to =1 and the RunningSum property to OverGroups.

4. Locate a second unbound text box in the Group Footer section.

5. Set the ControlSource property for the second text box to the ControlName of the first text box.

   When the last detail record for a group prints on page 1 and the group footer prints on page 2, the group footer control adds the value for the last record in the group twice.

   If the totals are derived from a user-defined function, you may be able to test this procedure (for instance, if the FormatCount or PrintCount properties of the report have been set to 2 and, if true, return 0 to the function). Another option may be to change the data type from Variant to Double.

REFERENCES