ACC2000: How to Print a Group Footer at a Specific Location

This article describes how to print a report's Group Footer information at a specific place on the page. This can be useful when you are printing reports to preprinted forms. Although you cannot do this using the Group Footer's property settings, you can use any of the following three methods:

• Place the information in the report's page footer, which is always printed at a fixed location.
• Set the report properties for the footer section.
• Set the report properties for the detail section.

Method 1: Using the Page Footer

The Page Footer is always printed at a fixed location on the report's page, just above the bottom margin. For example, if your report has a bottom margin of 1 inch and the height of the Page Footer is 1 inch, the Page Footer begins 2 inches from the bottom of the page (or 9 inches from the top of an 11-inch page). Placing the Group Footer controls in the Page Footer causes them to be printed at the Page Footer's fixed location on the page.

NOTE: You cannot use aggregate functions such as Sum() or Count() in a Page Footer. For additional information about how to display aggregate results in your footer, click the article number below to view the article in the Microsoft Knowledge Base:

Method 2: Setting Report Properties for the Footer Section

This method involves using the report's MoveLayout, PrintSection, and NextRecord properties to ensure that the Group Footer is not printed above a specific location on the page. With this method, you are not actually specifying the Group Footer's coordinates on the page, but advancing the Group Footer's print location until a specified vertical offset is reached.

To ensure that the Group Footer is printed where you want it, you must consider the height of each section of the report. Since you cannot specify the location of the Group Footer section (or any other section) with a property setting, you must use the MoveLayout property to move the section to a suitable printing location.

The report's MoveLayout property is False for every section by default, but it can be changed by an event procedure. For example, consider a Group Footer section with a Height of 0.5 inch which would print normally (that is, when the report's MoveLayout property is False) at a position 3.75 inches from the top of the report. If the section's OnFormat event sets the report's MoveLayout property to True, then as the Group Footer section is being formatted, its print location will be advanced by its own height, to a position 4.25 inches from the top of the report.

The following example shows you how to print the Group Footer on the lower portion of the page by adjusting the section heights, and by using a sample user-defined function called SetGrpFtrLoc() that tests whether the report's Top property is less than the GrpFtrLoc setting (the location where the Group Footer should be printed). If it is, the function sets the report's MoveLayout property to True and the PrintSection and NextRecord properties to False, which moves the Group Footer section to the next printing location without printing it or bringing in data from another record. When the correct position is reached, the MoveLayout and PrintSection properties are the default values and the Group Footer is printed.

You should be aware that this does not mean that the Group Footer will print precisely at the specified vertical offset (GrpFtrLoc); rather, the printing position will be advanced until that offset has been reached or exceeded. One consequence of this is that sections of the report, such as the Report Header section, which are printed on some pages but not on others, may cause small differences in Group Footer printing locations. You can avoid this by taking care in setting the heights of the various sections of your report, as the example illustrates.

CAUTION: If you follow the steps in this example, you modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file and follow these steps on a copy of the database.

1. Start Microsoft Access and open the sample database Northwind.mdb.
2. Create a new module and enter the following line in the Declarations section of the module if it is not already there:

   Option Explicit
   					

   Enter the following function in the module:

   Function SetGrpFtrLoc(Rpt as Report, GrpFtrLoc as Double)
   
       GrpFtrLoc=GrpFtrLoc*1440                     'Convert from inches to twips.
       If Rpt.top < GrpFtrLoc Then                  'Not at location yet, so
           Rpt.movelayout = True                    'move to next print location.
           Rpt.nextrecord = False                   'Do not go to next record.
           Rpt.printsection = False                 'Do not print the section.
       End If                                       'Until the required offset is reached
   
   End Function
   					

3. Save the module as Footer Location and then close it.
4. Use the Report Wizard to create a new report based on the Customers table, including the following fields:

       Field          Sort           Group
       -----          ----           -----
       Country        Ascending       Yes
       CompanyName    Ascending
       Address
       City
       PostalCode
   					

5. Select Stepped and Portrait layout and the default style.
6. Title the report Customers by Country 1, select Modify the report's design, and then click Finish.
7. In the report's Design view, on the View menu, click Sorting and Grouping.
8. Under Field/Expression, click Country, click Group Footer and select Yes, and then close the Sorting and Grouping window.
9. Click the Text Box tool in the Toolbox and then click in the center of the Country Footer section to create an unbound text box.
10. If the property sheet is not visible, right-click the new text box and click Properties on the shortcut menu to display it.
11. Click the Other tab, and name the new text box DetailCount.
12. Click the Data tab and in the Control Source box, type =Count([CompanyName]).
13. Set the Country Footer section's properties as follows:
    Height: 0.3 in.
    ForceNewPage: After Section
    OnFormat: =SetGrpFtrLoc([Report],7)
    Note: The parameter 7 in the SetGrpFtrLoc() function indicates that you want the group footer to begin at least 7 inches from the top of the page.
14. Save the report, and then preview it, using the navigation buttons to view successive pages.

Notice that while the Group Footer appears at approximately the correct position, it does not appear in exactly the same position on each page. This is because the heights of the Report Header and Detail sections have not been set to integral multiples of the height of the section being moved. To accomplish this, follow these steps:

1. Open the report again in Design view.
2. Set the Detail section's Height property to 0.3 inches.
3. Set the Report Header section's Height property to 0.6 inches
4. Save and preview the report.

Notice that the Group Footer now appears at a consistent position on the page, as long as you have a modest number of detail records.

NOTE: The Page Header and Country Header sections do not need to be adjusted because they occupy the same amount of space on every page. If they appeared on some pages and not on others, their heights would also have to be adjusted.

This method has some limitations. If you have a large number of detail records, or require the Group Footer to appear at a relatively high position on the page, you will find that the Group Footer is displaced toward the bottom of the page as the Detail sections print beyond the offset specified for their footer.

Method 3: Setting Report Properties for the Detail Section

You can use a similar approach with the MoveLayout, PrintSection, and NextRecord properties to advance a blank Detail section until the location for printing the Group Footer section is reached. This method is preferable to moving the Group Footer section if the Detail section is smaller than the Group Footer section, and particularly when the Detail sections must not intrude into an area reserved for the Group Footer, as with pre-printed forms.

The following example shows you how to print the Group Footer at a specific place on the page by using a sample user-defined function called SetDetailLoc() and controlling a page break in the Detail section of the report.

1. Create a new module and enter the following statements in the Declarations section:

   Option Explicit
   Dim lngRecCntr as Long                               ' number of detail records printed
   					

2. Enter the following functions:

   Function HidePageBreak(Rpt As Report)
       
       Rpt![HiddenPageBreak].Visible = False
       lngRecCntr = lngRecCntr - 1
       ' the detail is formatted for a second time, but not printed,
       ' on setting new page so adjust the count of printed records
   
   End Function
   
   Function ResetCounter()
   
       lngRecCntr = 0
   
   End Function
   
   Function SetDetailLoc(Rpt As Report, GrpFtrLoc As Double)
     
       GrpFtrLoc = GrpFtrLoc * 1440                 ' convert from inches to TWIPS
       lngRecCntr = lngRecCntr + 1
       
       If lngRecCntr < Rpt![DetailCount] Then       ' some details yet to print
           If Rpt.Top >= GrpFtrLoc Then             ' printing has moved into footer area
               Rpt![HiddenPageBreak].Visible = True ' go to new page
           End If                                   ' otherwise print normally
       ElseIf lngRecCntr = Rpt![DetailCount] Then
           If Rpt.Top < GrpFtrLoc Then              ' not yet at footer area
               Rpt.NextRecord = False               ' don't go to next group
           End If                                   ' until advanced to footer position
       Else                                         ' advancing to footer position
           Rpt.PrintSection = False                 ' don't print anything
           If Rpt.Top < GrpFtrLoc Then              ' not yet down to footer location
               Rpt.NextRecord = False               ' so don't go to next detail
               Rpt.MoveLayout = True                ' but do move down the page
           End If
       End If
   
   End Function
   					

3. Save the module as Detail Location and close it.
4. Open the Customers by Country report in Design view and save it as Customers by Country 2.
5. Click the Page Break tool in the Toolbox and then click at the bottom of the Detail section to insert a page break. This will be made invisible, thus ineffective, by the HidePageBreak() function unless there are enough detail records to require an additional page.
6. If the property sheet is not visible, right-click the page break to display it, then click the Other tab and type the name HiddenPageBreak.
7. Set the following properties for the Page Header section:
   OnFormat: =HidePageBreak([Report])
   Height: 0.6 in.
8. Select the Country text box in the detail section, cut it and paste it into the page header section, and position it at the bottom of the section.
9. Set the following property for the Country Header section:
   OnFormat: =ResetCounter()
   Height: 0 in.
   NOTE: The height of this section can be used to compensate for a small offset that may be produced when additional pages of detail are printed, depending on the heights of other sections of the report.
10. Set the following property for the Detail section:
    OnFormat: =SetDetailLoc([Report],7)
11. Delete the OnFormat event property for the Country Footer section.
12. Save and preview the report.

To observe the effect of multiple pages of detail records, follow these steps:

1. Open the report Customers by Country 2 in Design view.
2. Change the Height property of the Country Header section to 3.0 inches.
3. Change the OnFormat property of the Detail section to =SetDetailLoc([Report],5)
4. Save and preview the report.

For more information about the MoveLayout,NextRecord, and PrintSection report properties, click Microsoft Help on the Help menu, type MoveLayout, NextRecord, PrintSection Properties in the Office Assistant or the Answer Wizard, and then click Search to view the topic.