ACC: How to Print Group Footer at Specific Location

ID: Q119655

The information in this article applies to:

Microsoft Access versions 1.0, 1.1, 2.0, 7.0, 97

SUMMARY

Moderate: Requires basic macro, coding, and interoperability skills.

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.

This article assumes that you are familiar with Visual Basic for Applications and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information about Visual Basic for Applications, please refer to your version of the "Building Applications with Microsoft Access" manual.

NOTE: Visual Basic for Applications is called Access Basic in Microsoft Access versions 1.0 and 2.0. For more information about Access Basic, please refer to the "Building Applications" manual.

MORE INFORMATION

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 the page (for an 11 inch long 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 in a control in a page footer. If you need to use aggregate controls in your footer, please see the following Microsoft Knowledge Base article:

Q129096 ACC: How to Sum a Calculation in a Report

Method 2: Setting Report Properties for the Footer Section

This method involves using the MoveLayout, PrintSection, and NextRecord report properties to move the group footer to a specific printing location on the page. With this method, you are not actually specifying the group footer's coordinates on the page, but moving the group footer to various existing printing locations on the report page.

To ensure that the group footer is printed where you want it to be, 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 the next available printing location. For example, if the group footer section's height is 0.5 inches and the Top property (which specifies how far from the top of the report the section is printed) is set to 3.75 inches, then the MoveLayout property moves the group footer section to be printed in the area from 3.75 inches to 4.25 inches from the top of the report (a total of 0.5 inches).

The following example demonstrates how to print the group footer at a specific place on the page by adjusting the section heights, and by using a sample user-defined function called SetGrpFtrLoc() that tests to see if the Top property is set to less than the GrpFtrLoc setting (the location where the group footer will be printed). If it is, the function moves the group footer section to the next printing location. If not, it does not move the group footer.

CAUTION: Following the steps in this example will modify the sample database Northwind.mdb (or NWIND.MDB in versions 2.0 or earlier). You may want to back up the Northwind.MDB file, or perform these steps on a copy of the Northwind database.

Open the sample database Northwind.mdb.

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

      End Function

Save and then close the module.

Open the Catalog report in Design view.

Change the Category Name header section's Height property to:
```
      1.7 inches 
```

Change the detail section's Height property to:
```
      0.3 inches 
```

Set the Category Name footer section's properties as follows:
```
      Height: 0.3 inches
      ForceNewPage: After Section
      OnFormat: =SetGrpFtrLoc([Report],7) 
```
Note that the 7 in the SetGrpFtrLoc() function in the footer section's OnFormat property indicates that you want the group footer to begin 7 inches from the top of the page.

Create a new text box control with the following properties in the report's Category Name footer section:
```
      Name: Number of Products
      ControlSource: =Count([Product ID]) 
```
NOTE: In Microsoft Access version 1.x, the Name property is called the ControlName property.

Preview the report.

NOTE: If you do not adjust the section heights as specified, the location of the group footer will vary from page to page.

Method 3: Setting Report Properties for the Detail Section

A similar approach is to use the MoveLayout, PrintSection, and NextRecord properties to print 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.

The following example demonstrates how to print the group footer at a specific place on the page by using a sample, user-defined function called SetDetailLoc() that tests to see if this is the last detail section. For the last detail section, it then tests to see if the Top property is set to less than the GrpFtrLoc setting (the location where the group footer will be printed). If it is, the function moves the detail section to the next print location. If not, it does not move the detail section.

Open the sample database Northwind.mdb.

Create a new module and enter the following statements in the Declarations section:
```
       Option Explicit
       Dim RecCntr as Long 
```

Enter the following functions:


       Function ResetCounter()
          RecCntr = 0
       End Function

       Function SetDetailLoc(Rpt as Report, GrpFtrLoc as Double)
          RecCntr = RecCntr + 1
          If Rpt.top < GrpFtrLoc * 1440 Then
             If reccntr = Rpt![Number Of Products] Then
                'This is the last real record of the detail section.
                Rpt.nextrecord = False
                ElseIf reccntr > Rpt![Number Of Products] Then
                   'after last detail but not at location yet so move
                   Rpt.movelayout = True      'Move to next print location
                   Rpt.nextrecord = False     'Do not go to next record
                   Rpt.printsection = False   'Don't print the section
                End If
             ElseIf Rpt.top >= GrpFtrLoc * 1440 Then
                'Last record before the group footer section prints.
                Rpt.printsection = False
          End If
       End Function

Save the module.

Open the Catalog report in Design view.

Set the following property for the Category Name header section:
```
       OnFormat: =ResetCounter() 
```

Set the following property for the Category Name footer section:
```
       ForceNewPage: After Section 
```

Set the following property for the detail section:


       OnFormat: =SetDetailLoc([Report],7-0.2)

       Note that the 7 in the SetDetailLoc() function in the footer
       section's OnFormat property indicates that the group footer
       begins 7 inches from the top of the page, and the 0.2 is the
       height of the detail section. (You need to subtract the detail
       section's height because the footer section prints after the
       detail section.)

In the report's Category Name footer section, create a new text box and set the following properties:


       Name: Number of Products
       ControlSource: =Count([Product ID])

       NOTE: In Microsoft Access version 1.x, the Name property is called
       the ControlName property.

Preview the Catalog report.

REFERENCES

Microsoft Access "User's Guide," version 2.0, Chapter 26, "Using Macros to Print Reports or Transfer Data," pages 693-696

In Microsoft Access versions 2.0 and 7.0, for more information about the MoveLayout, NextRecord, and PrintSection report properties, press F1 and in

Help search for "MoveLayout" then "MoveLayout, NextRecord, and PrintSection Properties."

In Microsoft Access 97, for more information about the MoveLayout, NextRecord, and PrintSection report properties, press F1 and ask the Microsoft Office Assistant.


Keywords          : RptSort 
Version           : 1.0 1.1 2.0 7.0 97
Platform          : WINDOWS 
Issue type        : kbhowto

Last Reviewed: April 6, 1999