Results 1 to 7 of 7
  1. #1
    Lounger
    Join Date
    Jan 2005
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Page Suppression (Access 2003)

    I have a report that is grouped by Group1, then Group2. I want to limit the page for Group 1 to one page but show detail for Group 2 on that page, and if the detail goes to more than one page, show the total for Group 2 at the bottom of page 1 and go on to the next Group 1, etc etc. I am not having any success with this - any ideas from anyone who has had to do this? Thank you! Norma

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

    Re: Page Suppression (Access 2003)

    If the detail sections all have the same height, you can calculate how many you want to display on a page, and use the method described in MSKB article ACC2000: How to Print a Constant Number of Lines Per Group (applies to Access 2003 too).

  3. #3
    Lounger
    Join Date
    Jan 2005
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Page Suppression (Access 2003)

    I admit that I am not that great with VBA. Where can I find more information about the Function Printlines? I followed the steps, but the additional pages are not suppressing. Thanks, Norma

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

    Re: Page Suppression (Access 2003)

    The function PrintLines is given in the MSKB article; you should replace the single occurrence of the number 15 in the function with the number of detail records you want to display/print per group. For example, if there is room for 8 detail records on a page, change 15 to 8.

    If you wish, you can post a stripped down copy of your database. See <post#=401925>post 401925</post#> for instructions.

  5. #5
    Lounger
    Join Date
    Jan 2005
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Page Suppression (Access 2003)

    Thank you for your kind offer - I have attached the database. The first page ifor Administrative Law is fine since it only has 4 records, but the ADR and Banking and Lending exceed the 49 records I asked for and continues to print all records. Thanks again, Norma

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

    Re: Page Suppression (Access 2003)

    You hadn't followed the instructions completely. But looking at your report, I saw that it could be done in a much simpler way. I added a text box txtCount to the Detail section with the following properties:

    Visible: No
    Control Source: =1
    Running Sum: Over Groups

    And I added the following code to the On Format event of the Detail section:

    Private Sub LineControl_Format(Cancel As Integer, FormatCount As Integer)
    Cancel = (Me.txtCount > 33)
    End Sub

    The value of 33 was determined by trial and error, it lets me display the last group and the Grand Totals on one page in my setup, but it may be different for you. You can experiment with changing this value.

    See the attached version.

  7. #7
    Lounger
    Join Date
    Jan 2005
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Page Suppression (Access 2003)

    Thank you so much Hans. You are right about changing the value - as I change formats, etc I do need to modify. You have inspired me to sign up for a VBA class. I can see that I need to get to the next level if I am going to be able to do advanced reporting. Your help was really appreciated,

    Norma

Posting Permissions

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