Printed report does not match print Preview

  • Thread starter Thread starter Jeff Wimer
  • Start date Start date
J

Jeff Wimer

I have created some reports using VBA to print crosstab reports from a post
I found here. The printed reports order and data do not match the print
preview. I am not sure what is wrong.
TIA Jeff
 
I can't see anything wrong with them from here.

Maybe you should describe your data, desired display, and resulting display.
 
The data is from a crosstab report. So the VBA code takes into account the
variabl length of the crosstab. The preview displays corectly, but when I go
to print the data, the data, is all messed up, the headers and formating all
look okay.. How does the preview to print porcess work? Do I need to reset
variables, so the data can restart?
 
How do we know if you are using code and what it might look like? We can't
see any of it. I prefer to not use most dynamic crosstab code that sets
control sources etc using a bunch of convoluted code. I believe there are
better methods but we still have no idea what you have for requirements and
how you are attempting to solve this.
 
The data is from a crosstab report. So the VBA code takes into account the
variabl length of the crosstab. The preview displays corectly, but when I go
to print the data, the data, is all messed up, the headers and formating all
look okay.. How does the preview to print porcess work? Do I need to reset
variables, so the data can restart?
Click to expand...

Without seeing your code, my guess is that you have code doing some of
the calculations in the report section's format or print events.
Place code in the Report Header Format event to reset all of those
controls to =0.
 
First, let me apologize for the length of the post. I am not sure what area
you may need to look at.
Secondly, I am unfamiliar with how the report process works. I am assuming
that when you call a report, for preview, that the report opens and goes
through all of the routines to give you the data on screen. Then I assume
that when you hit the print button from the preview screen, that the report
either is sent as a snapshot to the printer, or that the print button makes
a call to run through all of the procedures again?
Lastly, if there is a better process to print variable length crosstab
queries, I would sure be interested. Unfortunately, my boss demands dynamic
length reports...

Thanks again,
Jeff

Option Compare Database

' 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
Dim rstFooter As DAO.Recordset
Dim rstTotal 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
Dim qdf2 As String


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("txtData" + Format(intX)) = xtabCnulls(rstReport(intX - 1))
Next intX

' Hide unused text boxes in the "Detail" section.
For intX = intColumnCount + 2 To conTotalColumns
Me("txtData" + 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 = 2 To intColumnCount
' Starting at column 2 (first text box with crosstab value),
' compute total for current row in the "Detail" section.
lngRowTotal = lngRowTotal + Me("txtData" + Format(intX))

' Add crosstab value to total for current column.
lngRgColumnTotal(intX) = lngRgColumnTotal(intX) + Me("txtData" +
Format(intX))
If Me("txtdata" + Format(intX)) = 0 Then
Me("txtdata" + Format(intX)).Visible = False
Else
Me("txtdata" + Format(intX)).Visible = True
End If
Next intX

' Put row total in text box in the "Detail" section.
Me("txtData" + 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 PageHeaderSection_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("txtHeader" + Format(intX)) = rstReport(intX - 1).Name
Next intX

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

' Hide unused text boxes in page header.
For intX = (intColumnCount + 2) To conTotalColumns
Me("txtHeader" + 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

' Open QueryDef object.
Set qdf = dbsReport.QueryDefs("qryLoads_year_month_crosstab_Total")


'Set rstReport = mydb.OpenRecordset("qryHours_Load_All", dbOpenDynaset)

' 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
Dim AvgLoads As Double
Dim mMonth As Variant
Dim qdf2, qdf3 As String

qdf2 = "qryAverage_Loads_Month_Total"
qdf3 = "qryTotal_Average_Loads_Year"

Set rstFooter = dbsReport.OpenRecordset(qdf2)
Set rstTotal = dbsReport.OpenRecordset(qdf3)

Me.txtSum2 = rstFooter!January1
Me.txtSum3 = rstFooter!February1
Me.txtSum4 = rstFooter!March1
Me.txtSum5 = rstFooter!April1
Me.txtSum6 = rstFooter!May1
Me.txtSum7 = rstFooter!June1
Me.txtSum8 = rstFooter!July1
Me.txtSum9 = rstFooter!August1
Me.txtSum10 = rstFooter!September1
Me.txtSum11 = rstFooter!October1
Me.txtSum12 = rstFooter!November1
Me.txtSum13 = rstFooter!December1

intX = 14
' Put grand total in text box in report footer.
Me("txtSum" + Format(intColumnCount + 1)) = rstTotal!Average

' Hide unused text boxes in report footer.
For intX = intColumnCount + 2 To conTotalColumns
Me("txtSum" + 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
 
I don't care for that code intensive and fairly inflexible method. The
solution I use is found in the Crosstab.MDB in the downloads at
http://www.invisibleinc.com/divFiles.cfm?divDivID=4. This solution is much
faster and isn't prone to errors like you are experiencing.

--
Duane Hookom
MS Access MVP


Jeff Wimer said:
First, let me apologize for the length of the post. I am not sure what
area you may need to look at.
Secondly, I am unfamiliar with how the report process works. I am assuming
that when you call a report, for preview, that the report opens and goes
through all of the routines to give you the data on screen. Then I assume
that when you hit the print button from the preview screen, that the
report either is sent as a snapshot to the printer, or that the print
button makes a call to run through all of the procedures again?
Lastly, if there is a better process to print variable length crosstab
queries, I would sure be interested. Unfortunately, my boss demands
dynamic length reports...

Thanks again,
Jeff

Option Compare Database

' 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
Dim rstFooter As DAO.Recordset
Dim rstTotal 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
Dim qdf2 As String


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("txtData" + Format(intX)) = xtabCnulls(rstReport(intX - 1))
Next intX

' Hide unused text boxes in the "Detail" section.
For intX = intColumnCount + 2 To conTotalColumns
Me("txtData" + 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 = 2 To intColumnCount
' Starting at column 2 (first text box with crosstab value),
' compute total for current row in the "Detail" section.
lngRowTotal = lngRowTotal + Me("txtData" + Format(intX))

' Add crosstab value to total for current column.
lngRgColumnTotal(intX) = lngRgColumnTotal(intX) + Me("txtData" +
Format(intX))
If Me("txtdata" + Format(intX)) = 0 Then
Me("txtdata" + Format(intX)).Visible = False
Else
Me("txtdata" + Format(intX)).Visible = True
End If
Next intX

' Put row total in text box in the "Detail" section.
Me("txtData" + 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 PageHeaderSection_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("txtHeader" + Format(intX)) = rstReport(intX - 1).Name
Next intX

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

' Hide unused text boxes in page header.
For intX = (intColumnCount + 2) To conTotalColumns
Me("txtHeader" + 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

' Open QueryDef object.
Set qdf = dbsReport.QueryDefs("qryLoads_year_month_crosstab_Total")


'Set rstReport = mydb.OpenRecordset("qryHours_Load_All", dbOpenDynaset)

' 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
Dim AvgLoads As Double
Dim mMonth As Variant
Dim qdf2, qdf3 As String

qdf2 = "qryAverage_Loads_Month_Total"
qdf3 = "qryTotal_Average_Loads_Year"

Set rstFooter = dbsReport.OpenRecordset(qdf2)
Set rstTotal = dbsReport.OpenRecordset(qdf3)

Me.txtSum2 = rstFooter!January1
Me.txtSum3 = rstFooter!February1
Me.txtSum4 = rstFooter!March1
Me.txtSum5 = rstFooter!April1
Me.txtSum6 = rstFooter!May1
Me.txtSum7 = rstFooter!June1
Me.txtSum8 = rstFooter!July1
Me.txtSum9 = rstFooter!August1
Me.txtSum10 = rstFooter!September1
Me.txtSum11 = rstFooter!October1
Me.txtSum12 = rstFooter!November1
Me.txtSum13 = rstFooter!December1

intX = 14
' Put grand total in text box in report footer.
Me("txtSum" + Format(intColumnCount + 1)) = rstTotal!Average

' Hide unused text boxes in report footer.
For intX = intColumnCount + 2 To conTotalColumns
Me("txtSum" + 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
Click to expand...
 
Back
Top