Results 1 to 14 of 14
  1. #1
    New Lounger
    Join Date
    Jul 2006
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Page Breaks (Access 2003)

    Here is my scenario . . . I created a report for billing purposes and am using a pre-made billing form that allows 5 lines of billing at the bottom of the page. If I have a client with more than 5 lines of billing information, how can I have the first page only print the first 5 lines of billing information, WITH A TOTAL, and then go to a second page and print 5 more lines, WITH A TOTAL, etc.?

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

    Re: Page Breaks (Access 2003)

    The attached database, using reduced versions of some tables from the Northwind database, demonstrates a possible approach.

    There are two hidden text boxes in the Detail section, one provides a record number, the other a running sum of ExtendedPrice.
    Code in the On Format procedure of the Detail section makes a page break control visible every 5 records, using the record number.
    Code in the On Format procedure of the Page Footer section displays the page total in a text box. It uses a module level variable to subtract the previous page total from the running sum.

  3. #3
    New Lounger
    Join Date
    Jul 2006
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Page Breaks (Access 2003)

    Hans,

    Thank you for your response! However, I am unable to open this database. I tried unzipping it, but where I work, it's not an option. Any other suggestions?

    Marlena

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

    Re: Page Breaks (Access 2003)

    Here is a description:

    The detail section of the report contains:
    - A text box txtCount with Control Source =1 and Running Sum set to Over All.
    - A text box txtRunSum bound to the field I want to keep track of, with Running Sum set to Over All.
    - A page break control brkPage below the other controls in the section.

    The page footer section contains an unbound text box txtTotal.

    The code behind the report is as follows:
    <code>
    Option Compare Database
    Option Explicit

    Private dblSum As Double

    Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    Me.brkPage.Visible = (Me.txtCount Mod 5 = 0)
    End Sub

    Private Sub PageFooter_Format(Cancel As Integer, FormatCount As Integer)
    Me.txtTotal = Me.txtRunSum - dblSum
    dblSum = Me.txtRunSum
    End Sub
    </code>
    HTH

  5. #5
    New Lounger
    Join Date
    Jul 2006
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Page Breaks (Access 2003)

    Hans,

    I am so grateful for your help with this! I really like to use Access, but am very limited in using code. I've gone ahead and entered the sample code you gave me. But, it gives me an error in the (Me.txtCount Mod 5 = 0) line. Should I be replacing any part of that with some verbiage in my own query?

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

    Re: Page Breaks (Access 2003)

    Have you created text boxes txtCount and txtRunSum and a page break control brkPage as described in my previous reply?

  7. #7
    New Lounger
    Join Date
    Jul 2006
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Page Breaks (Access 2003)

    Yes. Once again, I'm new at this. I created them in my Detail section, that didn't work. Then I created them in my Footer section, that didn't work either.

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

    Re: Page Breaks (Access 2003)

    If you wish, you can attach a stripped down copy of your database, so that Loungers can take a look at it. See <post#=401925>post 401925</post#> for instructions.

    If that isn't possible, and you can't look at the database I attached higher up in this thread either, it becomes difficult to help you.

  9. #9
    New Lounger
    Join Date
    Jul 2006
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Page Breaks (Access 2003)

    Ok, here is an attached stripped down copy of the database.

    Once again, what I'm trying to do with these reports:
    Report - Per client: only have 4 lines of data on each page of the individual's report, having a total at the bottom of each page.

    Report - All Clients: The same thing as per client, but also breaking the page between clients to begin a new one. When this happens with a page break, how do you get the report to not print a blank page first before all the rest of the report?

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

    Re: Page Breaks (Access 2003)

    What would you like to total?

  11. #11
    New Lounger
    Join Date
    Jul 2006
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Page Breaks (Access 2003)

    I'd like to total just the amounts from the lines of data on each page. SO, the first page should have the total for those four lines. The second page should have the total of THOSE four lines, etc.

    Thanks for your quick response!!! Your name is very commonly used around these parts. I just took two Access classes and everone knows HansV!

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

    Re: Page Breaks (Access 2003)

    Please indicate exactly which field or expression you want to total. There is no field named or labeled Amount.

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

    Re: Page Breaks (Access 2003)

    OK, I have assumed that you want to total the Text109 text box (note: it is easier to maintain and modify a database if you use meaningful names). In the attached database, I have added the text boxes and the code from one of my previous replies.
    In the 'all clients' report, I have added a grouping level on the client name, and set the Force New Page property of the (empty) group header to Before Section.

  14. #14
    New Lounger
    Join Date
    Jul 2006
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Page Breaks (Access 2003)

    Hans,

    Please excuse my LOOONNNGGG delay in responding to the database solutions you sent me! I took a quick peak and they look just like what I've been needing! I'll put the changes into production and see what happens!

    Thanks again!
    Marlena

Posting Permissions

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