Microsoft KB Archive Search

Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

  1. Home
  2. ACC2000: How to Repeat Subreport Header at Top of Page

ACC2000: How to Repeat Subreport Header at Top of Page

View products that this article applies to.

This article was previously published under Q231808
Moderate: Requires basic macro, coding, and interoperability skills.

This article applies to a Microsoft Access database (.mdb) and to a Microsoft Access project (.adp).

↑ Back to the top

Summary

This article describes how to repeat a subreport's header when the subreport spans multiple pages.

NOTE: This method works best for one subreport. Attempting to repeat the subreport header for multiple subreports requires a set of controls in the page header and a flag for each subreport. Then the code must check each flag and hide or show the appropriate sets of controls. This code is not easy to maintain.

NOTE: This article explains a technique demonstrated in the sample file RptSmp00.exe. For information about how to obtain this sample file, please see the following article in the Microsoft Knowledge Base:
231851� ACC2000: Microsoft Access Sample Reports Available in Download Center

↑ Back to the top

More information

The method demonstrated in the following example uses a flag in the page header to indicate whether the subreport spans multiple pages. When the subreport's report header is printed, the flag is set to 1. When the subreport's report footer is printed, the flag is set to 0. When the page header is printed, if the flag is set to 1, then the subreport spans multiple pages and the subreport's header should be repeated.

The following example demonstrates how to cause a subreport's header to be repeated if the subreport spans multiple pages:
  1. Open either the Northwind.mdb or the NorthwindCS.adp sample database file.
  2. Create the following macro and then save it as RepeatSubHeader:
       Macro Name   Condition   Action
   ------------------------------------
   MainRH                   SetValue
   MainPH       [Flag]=0    CancelEvent
   SubRH                    SetValue
   SubRF                    SetValue

   MainRH Actions
   ------------------------------------------------
   SetValue
      Item: Reports![Categories Main Report]![Flag]
      Expression: 0

   SubRH Actions
   ------------------------------------------------
   SetValue
      Item: Reports![Categories Main Report]![Flag]
      Expression: 1

  SubRF Actions
  ------------------------------------------------
  SetValue
     Item: Reports![Categories Main Report]![Flag]
     Expression: 0
  3. Create a new report called Products Sub Report based on the Products table.
  4. Place the following three text box controls in the new report's detail section:
        Text Box:
             Name: ProductID
    ControlSource: ProductID

    Text Box:
             Name: CategoryID
    ControlSource: CategoryID

    Text Box:
             Name: ProductName
    ControlSource: ProductName
  5. On the View menu, click Report Header/Footer.
  6. Place the following three labels in the report's report header section:
        Label:
       Name: Text1
    Caption: ProductID

    Label:
       Name: Text2
    Caption: CategoryID

    Label:
       Name: Text3
    Caption: ProductName
  7. Set the OnPrint property of the report header section to the following macro:
        RepeatSubHeader.SubRH
  8. Set the OnFormat property of the report footer section to the following macro:
        RepeatSubHeader.SubRF
  9. Create a new report called Categories Main Report based on the Categories table.
  10. Copy the controls from the Product Sub Report report's report header section and paste them into the page header section of the Categories Main Report report.
  11. Place the following text box in the Categories Main Report report's page header section:
        Text Box:
       Name: Flag
    Visible: No
  12. Place the following three text boxes in the Categories Main Report report's detail section:
        Text Box:
             Name: CategoryID
    ControlSource: CategoryID

    Text Box:
             Name: CategoryName
    ControlSource: CategoryName

    Text Box:
             Name: Description
    ControlSource: Description
  13. Embed the Products Sub Report report into the Categories Main Report report by dragging the Products Sub Report report from the Database window to the detail section of the Categories Main Report report.
  14. On the View menu, click Report Header/Footer to add a report header and footer to the main report. If you do not want a main report header or footer, set the header or footer section's Height property to 0.
  15. Set the OnFormat property of the report header section of the Categories Main Report report to the following macro:
        RepeatSubHeader.MainRH
  16. Set the OnPrint property of the page header section of the Categories Main Report report to the following macro:
        RepeatSubHeader.MainPH
  17. Preview or print the report. Note that the subreport's header information is repeated when the subreport spans multiple pages.

Displaying Main Report Information on Every Page

The previous example assumes that you do not want anything except the subreport header to be printed in the main report's page header. If you do want to print other information, you will need to hide and unhide each subreport header control in the page header. Change the MainPH macro in step 2 of the previous example to: 
    Macro Name   Condition   Action
    ------------------------------------------------------
    MainPH       [Flag]=0    SetValue
                                     Item: [Text1].visible
                               Expression: False
                ...         SetValue
                                     Item: [Text2].visible
                               Expression: False
                ...         SetValue
                                     Item: [Text3].visible
                               Expression: False
                [Flag]=1    SetValue
                                     Item: [Text1].visible
                               Expression: True
                ...         SetValue
                                     Item: [Text2].visible
                               Expression: True
                ...         SetValue
                                     Item: [Text3].visible
                               Expression: True

↑ Back to the top

References

For more information about events during printing, click Microsoft Visual Basic Help on the Help menu, type Print Events in the Office Assistant or the Answer Wizard, and then click Search to view the topics returned.

For more information about report OnFormat and OnPrint properties, click Microsoft Visual Basic Help on the Help menu, type OnFormat and/or OnPrint in the Office Assistant or the Answer Wizard, and then click Search to view the topics returned.

↑ Back to the top

Keywords: KB231808, kbusage

↑ Back to the top

Article Info
Article ID : 231808
Revision : 2
Created on : 6/30/2004
Published on : 6/30/2004
Exists online : False
Views : 339