Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Sep 2002
    Location
    London, England
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Report subtotals every 20 lines (2000)

    Is there a way of making a report print subtotals every n lines (in this case n = 20)?

    Thanks as usual

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

    Re: Report subtotals every 20 lines (2000)

    It is done with smoke and mirrors.
    <UL><LI>Increase the height of the detail section of the report by a little bit more than the height of a standard text box.
    <LI>In the newly created space, add three text boxes:
    <UL><LI>A text box named txtCount with Control Source =1, and with Running Sum set to Over All, and Visible set to No.
    <LI>A text box named txtSubTotal with Control Source set to the field you want to sum, and with Running Sum set to Over All, and Visible set to No.
    <LI>An unbound text box named txtPreviousTotal, with Visible set to No.
    <LI>An unbound text box named txtDisplay.[/list]The last one is the one that will display the subtotal, so you need to place it correctly. Make sure that the text boxes don't overlap each other and the other controls.
    <LI>Set the Can Shrink property of the detail section and of the four text boxes you just created to Yes.
    <LI>Put the following code in the On Format event of the report header section:

    Private Sub ReportHeader_Format(Cancel As Integer, FormatCount As Integer)
    Me.txtSubTotal = 0
    End Sub

    This code initializes the text box.
    <LI>Put the following code in the On Format event of the detail section:

    Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    ' Change the 20 to whatever the grouping interval should be.
    If Me.txtCount Mod 20 = 0 Then
    Me.txtDisplay = Me.txtSubTotal - Me.txtPreviousTotal
    Me.txtPreviousTotal = Me.txtSubTotal
    Me.txtDisplay.Visible = True
    Else
    Me.txtDisplay.Visible = False
    End If
    End Sub

    This is the "smoke and mirrors" routine.[/list]Warning: this causes a noticeable delay when opening the report.

  3. #3
    3 Star Lounger
    Join Date
    Sep 2002
    Location
    London, England
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report subtotals every 20 lines (2000)

    Thanks for that. It seems to work although I got a run time error on the initialising routine, telling me that I could not assign a value to this object. I commented the line Me.txtSubTotalAmt = 0 (see below) out and it worked.

    Can I ask a subsidiary question? Can I force a page break from the same section of code?


    Private Sub ReportHeader_Format(Cancel As Integer, FormatCount As Integer)
    Me.txtSubTotalAmt = 0
    End Sub

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

    Re: Report subtotals every 20 lines (2000)

    You can put a Page Break control at the bottom of the detail section, slightly below the text boxes, and set its Visible property in the code. Making a page break control invisible effectively disables it, while making it visible will cause a "real" page break.

  5. #5
    3 Star Lounger
    Join Date
    Sep 2002
    Location
    London, England
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report subtotals every 20 lines (2000)

    My page breaks don't have a visible property.

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

    Re: Report subtotals every 20 lines (2000)

    >> My page breaks don't have a visible property.

    Not in the Properties window, but you can set it in code, even though IntelliSense doesn't show a Visible property. Just type it, it'll work.

Posting Permissions

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