Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


Insert Page break in Detail after each 5 records

Posted on 2010-01-04
Medium Priority
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
  • 2
LVL 27

Accepted Solution

MikeToole earned 2000 total points
ID: 26178617
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.

Author Comment

ID: 26184063
Sounds like a good thing to try. Thanks, Mike.  

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

Author Closing Comment

ID: 31672776
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

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
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…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Suggested Courses

578 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