Insert Page break in Detail after each 5 records

Posted on 2010-01-04
Last Modified: 2013-11-28
I have attached a database with a report named rptAttendForm that will be used to print an attendance sheet for a training class that can span any number of days.  The class info (name, classID, Company who we're teaching, etc.) are in the table tblClasses which is what the report is bound to.  There is another table named tblClassSchedule that holds the specific dates and times that the sessions will be held.

On the attendance sheet I need to print the class dates into the Date text boxes that are named txtDate1, txtDate2, txtDate3, txtDate4, and txtDate5 (if you look in DesignView I colored them Red).  

Since the number of dates in a given schedule vary, I will need to be able to insert a page break after the first 5, next 5, etc.    So, for example, if the class is schedule from 1-5  days we would have 1 attendance page, if there are between 6-10 days 2 pages, etc.  In the sample database there is one class that meets on 9 different dates, so there would be two pages of the attendance sheet, the first with the first 5 dates, the 2nd with the next 4, then the report footer would need to print on a 3rd page.

I've attached code that I put in the form's Detail's Paint event (might need some direction on this if that would not be the appropriate place) .

Here's where I need help - figuring out how to ge the loop to work and how to make it do a page break and repeat the detail section with the next series of dates.  Also I'm wondering if there's a way to set an object variable that could be used to increment the text box names (txtDate1, txtDate2, etc.) and then reference them when filling the text box with the classDate.  

Hope I'm making sense:)  Thanks for all your help in advance.

Dim D As Database

    Dim R As Recordset

    Dim X As Long

    Dim Y As Long

    Dim txtBox As Access.Control


    Dim numRecords As Long

    Dim numPages As Double

    Dim totPages As Long

    Dim intNumPages As Long


    Set D = CurrentDb

    Set R = D.OpenRecordset("Select * from tblClassSchedule WHERE ClassID = " & Me.ClassID & " ORDER by ClassDate", dbOpenDynaset)


    numRecords = R.RecordCount

    numPages = numRecords / 5


    intNumPages = Int(numPages)


    If numPages > intNumPages Then

      totPages = intNumPages + 1


      totPages = intNumPages

    End If

    'MsgBox "TotPages is " & totPages



    For X = 1 To numRecords 'loop through all the records in tblClassSchedule for a specific ClassID


        For Y = 1 To 5 'place the class dates in txtDate1 through txtDate5


        Next Y


    Next X

Open in new window

Question by:jrogersok
    LVL 27

    Accepted Solution

    This would be easier if only one 'detail' row existed per page and it contained five columns for the five dates. That way the textboxes on the report could be bound and there's no need to control the paging.
    Why not write code to repopulate a temporary table each time the report is run:
               tblDates(ClassID, D1,D2,D3,D4,D5)
    The code loops through tblClassSchedule to add one row for each collection of five dates for a class.

    Link this table to the other tables in the record source for your form and you should be there.
    LVL 2

    Author Comment

    Sounds like a good thing to try. Thanks, Mike.  

    Anyone who still has suggestions I'm open to them. Thanks.
    LVL 2

    Author Closing Comment

    Excellent suggestion!  I did it! Thanks for your input.  You have been awarded the points.  

    It's so great to have this site to consult with the experts. You saved me hours of work.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    This article is a continuation or rather an extension from Cascading Combos ( and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
    A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
    In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now