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
'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