Private Sub Report_Open(Cancel As Integer)
On Error GoTo Err_Handler
'===============================================
'Dynamic report designed to be used with Crosstab Queries
'This code is especially "tuned" for crosstab queries.
'I like to have control over the layout, so the lay-out is designed first with "coded" controls.
'Then the dynamic filling becomes very easy. The raw text I use to help with this is:
'Making the column header and detail data flexible is possible,
'but needs some VBA code in the OpenReport event.
'To start doing this you need to place the fields "coded" in the report.
'The column headings could be called "lblCol1", "lblCol2", "lblCol3", etc.
'The "detail" fields could be called "txtCol1", "txtCol2", "txtCol3", etc.
'The report query has two row header columns and a Total column,
'therefore the first field is effectively column 4 (count starts at 0 so I used intI=3)
'but this will vary depending on the report
'Make sure that the number of Columns is not bigger than the number placed.
'The program code has no protection against that.
'===============================================
Dim intI As Integer
Dim Rs As DAO.Recordset
'get data source
strSQL1 = "TRANSFORM First(qryClassLessonAttendance.Mark) AS FirstOfMark" & _
" SELECT qryClassLessonAttendance.PupilID, qryClassLessonAttendance.Surname, qryClassLessonAttendance.Forename," & _
" qryClassLessonAttendance.TG, qryClassLessonAttendance.ClassID" & _
" FROM qryMostRecentLessonsSelectedClassTeacher INNER JOIN (qryClassLessonAttendance INNER JOIN Classes" & _
" ON qryClassLessonAttendance.ClassID = Classes.ClassID)" & _
" ON (qryMostRecentLessonsSelectedClassTeacher.TeacherID = qryClassLessonAttendance.TeacherID)" & _
" AND (qryMostRecentLessonsSelectedClassTeacher.Date = qryClassLessonAttendance.Date)" & _
" AND (qryMostRecentLessonsSelectedClassTeacher.Period = qryClassLessonAttendance.Period)" & _
" AND (qryMostRecentLessonsSelectedClassTeacher.ClassID = qryClassLessonAttendance.ClassID)" & _
" WHERE(((qryClassLessonAttendance.ClassID) = GetClass()))" & _
" GROUP BY qryClassLessonAttendance.PupilID, qryClassLessonAttendance.Surname, qryClassLessonAttendance.Forename," & _
" qryClassLessonAttendance.TG, qryClassLessonAttendance.ClassID" & _
" ORDER BY qryClassLessonAttendance.Surname, qryClassLessonAttendance.Forename, qryClassLessonAttendance.LessonInfo" & _
" PIVOT qryClassLessonAttendance.LessonInfo;"
Me.LblDates.Caption = DMin("Date", "qryMostRecentLessonsSelectedClassTeacher") & " - " & DMax("Date", "qryMostRecentLessonsSelectedClassTeacher")
Me.LblFooter.Caption = "NOTE: Attendance marks are shown for the last 40 lessons ONLY. Attendance data is not available for guest pupils. See next page for attendance codes"
'Debug.Print Me.LblDates.Caption
'Debug.Print strSQL1
Set Rs = CurrentDb.OpenRecordset(strSQL1)
'Place headers - start at column 4
For intI = 5 To Rs.Fields.Count - 1
'Me("Lesson" & intI - 4).Caption = Rs.Fields(intI).Name
'Lesson caption based on Rs.Fields(intI).Name
'This starts with CDbl(Date) & Period (for correct sorting by date & period) then a "#"
'Display all text after the "#"
Me("Lesson" & intI - 4).Caption = Mid(Rs.Fields(intI).Name, InStr(Rs.Fields(intI).Name, "#") + 1)
Me("Lesson" & intI - 4).visible = True
Next intI
'Place controls
For intI = 5 To Rs.Fields.Count - 1
Me("Mark" & intI - 4).ControlSource = Rs.Fields(intI).Name
Me("Mark" & intI - 4).visible = True
Next intI
'Place Total field
'Me.ColTotal.ControlSource = "=SUM([" & rs.Fields(2).Name & "])"
Exit_Handler:
Exit Sub
Err_Handler:
MsgBox "Error " & err.Number & " in Report_Open procedure: " 7 err.Description
Resume Exit_Handler
End Sub