Dynamic Crosstab Report does not print as shown in Print Preview

[errolf]

I am using a report that uses code to generate Dynamic Column Headings. The report appears correctly in the print preview mode but when printed the report only prints multiple records of the last record on the report.

Any help appreciated
Errol

 

[dhookom]

You are probably using a flawed method to create your dynamic column headings. Since we don't have a clue which method you are using, it is impossible to determine your cause.

Duane
MS Access MVP
[green]Ask a great question, get a great answer.[/green]
[red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
[blue]Ask me about my grandson, get a grand answer.[/blue]

 

[errolf]

I am using the following code It is a modified version of the Microsoft Dynamic Report.
' Constant for maximum number of columns EmployeeSales query would
' create plus 1 for a Totals column. Here, you have 9 employees.
Const conTotalColumns = 14

' Variables for Database object and Recordset.
Dim dbsReport As DAO.Database
Dim rstReport As DAO.Recordset

' Variables for number of columns and row and report totals.
Dim intColumnCount As Integer
Dim lngRgColumnTotal(1 To conTotalColumns) As Long
Dim lngReportTotal As Long

Private Sub InitVars()

Dim intX As Integer

' Initialize lngReportTotal variable.
lngReportTotal = 0

' Initialize array that stores column totals.
For intX = 1 To conTotalColumns
lngRgColumnTotal(intX) = 0
Next intX

End Sub


Private Function xtabCnulls(varX As Variant)

' Test if a value is null.
If IsNull(varX) Then
' If varX is null, set varX to 0.
xtabCnulls = 0
Else
' Otherwise, return varX.
xtabCnulls = varX
End If

End Function


Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
' Put values in text boxes and hide unused text boxes.

Dim intX As Integer
' Verify that you are not at end of recordset.
If Not rstReport.EOF Then
' If FormatCount is 1, put values from recordset into text boxes
' in "Detail" section.
If Me.FormatCount = 1 Then
For intX = 1 To intColumnCount
' Convert Null values to 0.
Me("Col" + Format(intX)) = xtabCnulls(rstReport(intX - 1))
Next intX

' Hide unused text boxes in the "Detail" section.
For intX = intColumnCount + 2 To conTotalColumns
Me("Col" + Format(intX)).Visible = False
Next intX

' Move to next record in recordset.
rstReport.MoveNext
End If
End If

End Sub


Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)

Dim intX As Integer
Dim lngRowTotal As Long

' If PrintCount is 1, initialize rowTotal variable.
' Add to column totals.
If Me.PrintCount = 1 Then
lngRowTotal = 0

For intX = 5 To intColumnCount
' Starting at column 4 (first text box with crosstab value),
' compute total for current row in the "Detail" section.
lngRowTotal = lngRowTotal + Me("Col" + Format(intX))

' Add crosstab value to total for current column.
lngRgColumnTotal(intX) = lngRgColumnTotal(intX) + Me("Col" + Format(intX))
Next intX

' Put row total in text box in the "Detail" section.
Me("Col" + Format(intColumnCount + 1)) = lngRowTotal
' Add row total for current row to grand total.
lngReportTotal = lngReportTotal + lngRowTotal
End If
End Sub


Private Sub Detail_Retreat()

' Always back up to previous record when "Detail" section retreats.
rstReport.MovePrevious

End Sub


Private Sub PageHeader_Format(Cancel As Integer, FormatCount As Integer)

Dim intX As Integer

' Put column headings into text boxes in page header.
For intX = 1 To intColumnCount
Me("Head" + Format(intX)) = rstReport(intX - 1).NAME
Next intX

' Make next available text box Totals heading.
Me("Head" + Format(intColumnCount + 1)) = "Totals"

' Hide unused text boxes in page header.
For intX = (intColumnCount + 2) To conTotalColumns
Me("Head" + Format(intX)).Visible = False
Next intX

End Sub




Private Sub Report_Close()

On Error Resume Next

' Close recordset.
rstReport.Close

End Sub


Private Sub Report_NoData(Cancel As Integer)

MsgBox "No records match the criteria you entered.", vbExclamation, "No Records Found"
rstReport.Close
Cancel = True

End Sub


Private Sub Report_Open(Cancel As Integer)

' Create underlying recordset for report using criteria entered in
' EmployeeSalesDialogBox form.

Dim intX As Integer
Dim qdf As QueryDef
Dim frm As Form

' Set database variable to current database.
Set dbsReport = CurrentDb
Set frm = Forms!GsDataFrm
' Open QueryDef object.
Set qdf = dbsReport.QueryDefs("test")
' Set parameters for query based on values entered
' in EmployeeSalesDialogBox form.
qdf.Parameters("Forms!gsdatafrm!BeginningDate") _
= frm!BeginningDate
qdf.Parameters("Forms!gsdatafrm!EndingDate") _
= frm!EndingDate
qdf.Parameters("Forms!gsdatafrm!Spers") _
= [Forms]![GsDataFrm]![Spers]
' Open Recordset object.
Set rstReport = qdf.OpenRecordset()

' Set a variable to hold number of columns in crosstab query.
intColumnCount = rstReport.Fields.Count

End Sub


Private Sub ReportFooter_Print(Cancel As Integer, PrintCount As Integer)

Dim intX As Integer

' Put column totals in text boxes in report footer.
' Start at column 2 (first text box with crosstab value).
For intX = 4 To intColumnCount
Me("Tot" + Format(intX)) = lngRgColumnTotal(intX)
Next intX

' Put grand total in text box in report footer.
Me("Tot" + Format(intColumnCount + 1)) = lngReportTotal

' Hide unused text boxes in report footer.
For intX = intColumnCount + 2 To conTotalColumns
Me("Tot" + Format(intX)).Visible = False
Next intX

End Sub


Private Sub ReportHeader_Format(Cancel As Integer, FormatCount As Integer)

' Move to first record in recordset at the beginning of the report
' or when the report is restarted. (A report is restarted when
' you print a report from Print Preview window, or when you return
' to a previous page while previewing.)
rstReport.MoveFirst

'Initialize variables.
InitVars

End Sub



Thanks for your prompt reply.
Errol

 

[dhookom]

That solution isn't very good. I much prefer the solution in the Crosstab.mdb available for download at

http://www.invisibleinc.com/divFiles.cfm?divDivID=4.

This solution is more flexible, accomodates any number of columns, uses much less code, and is more efficient.

You will not have issues between print and preview with this alternative solution.

Duane
MS Access MVP
[green]Ask a great question, get a great answer.[/green]
[red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
[blue]Ask me about my grandson, get a grand answer.[/blue]

 

[errolf]

Thanks Duane ,
I have downloaded the file but at present I haven't been able to use it for my application.
The column headings that I want to be dynamic are dates formatted as mmm-yy.
Thanks for your help
Errolf

 

[dhookom]

If you would have stated the column headings were dates, I would have provided an entirely different solution that is much simpler. See if faq703-5466 meets your needs. It creates monthly columns but can be re-purposed [blue](I'm watching Trading Spaces on TV)[/blue] to use daily or weekly or whatever columns.

Duane
MS Access MVP
[green]Ask a great question, get a great answer.[/green]
[red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
[blue]Ask me about my grandson, get a grand answer.[/blue]

 

[errolf]

Gday Duane,
Thanks for your reply,
I have looked at the FAQ however maybe I have not been clear in my detail, The report I create from a query may have 1 column or up to 15 columns depending on the data required by the operator. The query column headings are generated using the following code
datStart = Me!BeginningDate
datEnd = Me!EndingDate
intMonths = DateDiff("m", datStart, datEnd)
While i <= intMonths
strDatesQSL = Format(DateAdd("m", i, datStart), "mmm-yy")
If i < intMonths Then
strPivotQSL = strPivotQSL & "'" & strDatesQSL & "',"
Else
strPivotQSL = strPivotQSL & "'" & strDatesQSL & "'"
End If
i = i + 1
Wend
I need to replicate this action on the report.
Hope you enjoyed Trading Places

Regards
Errol

 

[dhookom]

You could create the report with 15 columns as my FAQ suggests. Then, make controls invisible based on the number of dates selected. This requires much less code than your example above.

Duane
MS Access MVP
[green]Ask a great question, get a great answer.[/green]
[red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
[blue]Ask me about my grandson, get a grand answer.[/blue]