Force paging in report based on crosstab query with dynamic number of columns

[sroseindy]

PLEASE HELP - I NEED THIS TO COMPLETE A CONTRACT!!!
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.

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

[jimatqsi]

I think you've chosen a really difficult design to this report. Paging after x number of rows makes sense; paging after x number of columns is a little harder to get a hold of. That being said, you've demonstrated a pretty good understanding of how to control the various objects in a report.

Are you aware of the difference between the Format event and the Print event? Perhaps you should prepare your columns in the format event of each section. Doing all this in the Report's Open event can't work because there is not such thing as a "page" at that point in the execution.

A better approach would be to modify the report so that every detail line has a text box for every possible column. Then, in the Format event, you make invisible the columns you don't want to show and make visible the columns you do want to show. On the odd pages you show text boxes 1-11, on the even pages all the text boxes greater than 11. Same idea for the labels in the heading and totals in the footer. But you have to make visibility changes in the Format event of the Detail, Header and Footer sections; it's too late in the Print event. And doing it in the Open event gives the result you already see.

You've made a good start. I bet you'll have no trouble with this once you understand Format and Print events.

Jim