ACC: How to Print Group Footer at Specific Location

• Microsoft Access versions 1.0, 1.1, 2.0

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. While you cannot do this using the group footer's property settings, you can use any of the following three methods described in this article:

• 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

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 form 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, you may want to use the next method in this article, or refer to the "Using Macros to Print Reports or Transfer Data" chapter of the Microsoft Access "User's Guide" for an example of calculating totals in a page footer.

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 NWIND.MDB. You may want to back up the NWIND.MDB file, or perform these steps on a copy of the NWIND database.

1. Open the sample database NWIND.MDB.

2. Create a new module and enter the following line in the Declarations section of the module:

         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
   
   

3. Save and then close the module.

4. Open the Catalog report in Design view.

5. Change the Category Name header section's Height property to:

         1.7 inches
   

6. Change the detail section's Height property to:

         0.3 inches
   

7. 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.

8. 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.

9. Preview the report.

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.

1. Open the sample database NWIND.MDB.

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

   Option Explicit Dim RecCntr as Long

3. 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
   
   

4. Save the module.

5. Open the Catalog report in Design view.

6. Set the following property for the Category Name header section:

   OnFormat: =ResetCounter()

7. Set the following property for the Category Name footer section:

   ForceNewPage: After Section

8. 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.)

9. 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.

10. Preview the Catelog report.

REFERENCES

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

For more information about the MoveLayout, NextRecord, and PrintSection report properties, search for "MoveLayout" then "MoveLayout, NextRecord, and PrintSection Properties" using the Microsoft Access version 2.0 Help menu.

Keywords          : RptSort kbusage
Version           : 1.0 1.1 2.0
Platform          : WINDOWS
Hardware          : x86
Issue type        : kbhowto