Hi all,
I have a report based on a crosstab query. The number of columns is dynamic based on the selection criteria the user chooses. I’ve set up my report to hold 11 columns on a page. The first column is a student name and the rest are statuses of various activities for the student. I want the report to page after the first 11 columns, but repeat the student name on each page. Currently the report just shows the last pages worth of data. It appears that it is going through the code rewriting each page as the first page. How do I force it to page at the proper time?
I open the report from a form where the user selects the program to be reported on. I’ve been banging my head against this for awhile and appreciate any help you can give.
Expand|Select|Wrap|Line Numbers
- Private Sub Report_Open(Cancel As Integer)
- Dim conNumColumns As Integer
- Dim qdf As DAO.QueryDef
- Dim rst As DAO.Recordset
- Dim intColumnCount As Integer
- Dim pageColumnAdd As Integer
- Dim pageNumColumns As Integer
- Dim intX As Integer
- ' Don't open report if frmEmployeeSales is not loaded.
- If Not IsLoaded("frmStudentParticipation") Then
- Cancel = True
- MsgBox "Please open this report from frmStudentParticipation.", vbExclamation
- Exit Sub
- End If
- On Error GoTo Handle_Err
- ' Set record source.
- RecordSource = "qryStudentParticipation"
- ' Open QueryDef object.
- Set qdf = CurrentDb.QueryDefs("qryStudentParticipation")
- ' Set query parameters based on values in form.
- qdf.Parameters("Forms![FrmStudentParticipation]!cmbProgramID") = Forms![FrmStudentParticipation]!cmbProgramID
- ' Open recordset.
- Set rst = qdf.OpenRecordset
- ' Don't open report if there are no data.
- If rst.RecordCount = 0 Then
- MsgBox "No records found.", vbInformation
- Cancel = True
- GoTo Handle_Exit
- End If
- ' Fix number of columns in crosstab query.
- conNumColumns = 11
- pageNumColumns = 11
- intColumnCount = rst.Fields.Count - 1
- pageColumnAdd = 0
- If intColumnCount - pageColumnAdd >= conNumColumns Then
- pageNumColumns = conNumColumns
- Else
- pageNumColumns = intColumnCount - pageColumnAdd
- End If
- Do While pageNumColumns > 0
- rst.Close
- Set rst = Nothing
- Set rst = qdf.OpenRecordset
- Me("txtHeading1").Caption = rst(0).Name
- For intX = 2 To pageNumColumns
- ' Set caption of label in page header to field name.
- Me("txtHeading" & intX).Caption = rst(intX + pageColumnAdd - 1).Name
- Next intX
- If pageNumColumns < conNumColumns Then
- For intX = pageNumColumns + 1 To conNumColumns
- Me("txtHeading" & intX).Caption = ""
- Next intX
- End If
- ' Set control source of first text box in detail section to row header.
- Me("txtColumn1").ControlSource = "[" & rst(0).Name & "]"
- ' Start descriptions in column 2 (the first column with a crosstab value).
- For intX = 2 To pageNumColumns
- ' Set control source of text box in detail section.
- Me("txtColumn" & intX).ControlSource = "[" & rst(intX + pageColumnAdd - 1).Name & "]"
- Next intX
- If pageNumColumns < conNumColumns Then
- For intX = pageNumColumns + 1 To conNumColumns
- Me("txtColumn" & intX).ControlSource = ""
- Next intX
- End If
- pageColumnAdd = pageColumnAdd + conNumColumns - 1
- If intColumnCount - pageColumnAdd >= conNumColumns Then
- pageNumColumns = conNumColumns
- Else
- pageNumColumns = intColumnCount - pageColumnAdd
- End If
- Loop
- DoCmd.Maximize
- Handle_Exit:
- On Error Resume Next
- rst.Close
- Set rst = Nothing
- Set qdf = Nothing
- Exit Sub
- Handle_Err:
- MsgBox Err.Description, vbExclamation
- Resume Handle_Exit
- End Sub