Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Jan 2002
    Location
    Washington State
    Posts
    69
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Show the report header of a sub report (Access 2000)

    Hi everyone. Maybe you can help with my problem.

    I have a report with several sub-reports. What I'm trying to do is print the Report Header of the subreport on the parent report when it (the subreport) is split into two separate pages.

    This is what I've tried: When the subreport's Report Header format section is run I fill a variable with a particular number. (Each sub-report has a unique number.)

    When the parent report's Page Header event is run I analyze the variable (filled by the current subreport) to determine what labels I should display. (For example if it's 1, then I know the labels for this subreport are "Customer Name", "Customer Address", and "Phone Number". If it's 2, then the lables should read "Salesman Name", "Current Sales", and "Sales Y-T-D", etc.)

    This technique works one some pages, and doesn't on others. What I'm finding is that if two or more subreports end up printing on the same page then the following page's Page Header is formatted for the wrong report.

    What does this mean? Are events of each subreport run multiple times per page? How do I know when to store the correct report number to the variable?

    Or is there a much easier way to do this?

    I would appreciate any help on this matter. I've spent too many hours researching this, and I've come to the conclusion that I need some help!

    Let me know if I can provide further information.

    Thanks,
    Sam

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Show the report header of a sub report (Access 2000)

    Microsoft has example code in ACC2000: How to Repeat Subreport Header at Top of Page, but it states explicitly that it is not easy for multiple subreports.

  3. #3
    Star Lounger
    Join Date
    Jan 2002
    Location
    Washington State
    Posts
    69
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Show the report header of a sub report (Access 2000)

    Thanks, Hans.

    Yes, I see that it says: "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."

    That's actually exactly what I'm doing. A flag for each subreport... But MS does warn me up front that the code is not easy to maintain. But I'm always up for a challenge. And 9 times out of 10 there's a way to work around the problem.

    Has anyone else tried to accomplish this? With success? [img]/forums/images/smilies/smile.gif[/img]
    Sam

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Show the report header of a sub report (Access 2000)

    I have whipped up an example; it is attached as a zipped Access 97 database.

    The database contains a main report with two subreports, sbrProducts and sbrCategories. We want the page header of the main report to mimic the report header of the sub report that is shown at the top of a page. To this end, put two check boxes in the page header of the main report: chkProduct and chkSupplier (if you prefer, you can use text boxes instead, or global variables of type Boolean).

    Set chkProduct to True in the OnPrint event of the report header section of sbrProducts, and to False in the OnFormat event of its report footer section:

    <font face="Georgia">Private Sub ReportHeader_Print(Cancel As Integer, PrintCount As Integer)
    Parent!chkProduct = True
    End Sub

    Private Sub ReportFooter_Format(Cancel As Integer, FormatCount As Integer)
    Parent!chkProduct = False
    End Sub</font face=georgia>

    Since chkProduct is a control on the main report, we have to refer to it as Parent!chkProduct in the code behind the subreport.

    It is important that you use exactly this combination of events; other combinations don't yield the desired results.

    Similarly, set chkSupplier to True in the OnPrint event of the report header section of sbrSuppliers, and to False in the OnFormat event of its report footer section:

    <font face="Georgia">Private Sub ReportHeader_Print(Cancel As Integer, PrintCount As Integer)
    Parent!chkSupplier = True
    End Sub

    Private Sub ReportFooter_Format(Cancel As Integer, FormatCount As Integer)
    Parent!chkSupplier = False
    End Sub</font face=georgia>

    Use the value of chkProduct and chkSupplier in the OnPrint event of the page header section of the main form to change properties if controls in this section. In the attached example, two labels and a line are made visible or invisible, and the captions of the labels is changed. Here is the code:

    <font face="Georgia">Private Sub PageHeader_Print(Cancel As Integer, PrintCount As Integer)
    Dim blnProductVisible As Boolean
    Dim blnSupplierVisible As Boolean
    Dim blnEitherVisible As Boolean

    blnProductVisible = Nz(chkProduct)
    blnSupplierVisible = Nz(chkSupplier)
    blnEitherVisible = blnProductVisible Or blnSupplierVisible

    lblID.Visible = blnEitherVisible
    lblName.Visible = blnEitherVisible
    linHeader.Visible = blnEitherVisible

    If blnProductVisible Then
    lblID.Caption = "Product ID"
    lblName.Caption = "Product Name"
    End If

    If blnSupplierVisible Then
    lblID.Caption = "Supplier ID"
    lblName.Caption = "Supplier Name"
    End If
    End Sub</font face=georgia>

    The Nz function is used to avoid an error when the report is opened (at first, chkProduct and chkSupplier are Null).

    To show clearly whether the header of the main report or of a subreport is displayed, the font of the labels in the main report header is black, while that of the labels in the subreport headers is red and blue, respectively. This is just for demonstration purposes.
    Attached Files Attached Files

  5. #5
    Star Lounger
    Join Date
    Jan 2002
    Location
    Washington State
    Posts
    69
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Show the report header of a sub report (Access 2000)

    Hans,

    Thank you so much!

    I really appreciate the time you took to help me with my problem.

    And it works for me now too!

    Using the Print event (instead of the Format event) is all it took!

    Many thanks,
    Sam

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •