View Single Post
  #12  
Old May 18th, 2010, 03:01 AM posted to microsoft.public.access.reports
Duane Hookom[_4_]
external usenet poster
 
Posts: 316
Default Report Won't Print

The news groups have been flakey today. I don't care for your choice of the
crosstab report method. I much prefer the less code and more flexible
solution found at
http://www.rogersaccesslibrary.com/f...b_topic11.html.


--
Duane Hookom
MS Access MVP


"Lydia" wrote in message
...
I have posted twice my reply and haven't seen it show up yet. I will give
it
another try:

The following is the code behind the report:

Option Compare Database
Option Explicit
Const conTotalColumns = 8

Dim dbsReport As DAO.Database
Dim rstReport As DAO.Recordset
Dim intColumnCount As Integer
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim intX As Integer
If Not rstReport.EOF Then
If FormatCount = 1 Then
For intX = 1 To intColumnCount - 3
Me("col" + Format(intX)) = rstReport(intX + 2)
Next intX
For intX = intColumnCount - 2 To conTotalColumns
Me("Col" + Format(intX)).Visible = False
Next intX
rstReport.MoveNext

End If
End If
End Sub

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
Dim intX As Integer
If InStr(Me("Col3"), "RM") 0 Or InStr(Me("Col3"), "CM") 0 Then
For intX = 3 To intColumnCount - 3
Me("Col" + Format(intX)).BackColor = 13948116
Next intX
Else
For intX = 3 To intColumnCount - 3
Me("Col" + Format(intX)).BackColor = vbWhite
Next intX
End If
If Me("Col3") = "KS" Or Me("Col3") = "KR" Or Me("Col3") = "KC" _
Or Me("Col3") = "RS" Or Me("Col3") = "TM" Then
For intX = 1 To intColumnCount - 5
Me("lnDivide" + Format(intX)).Visible = True
Next intX
For intX = intColumnCount - 4 To 6
Me("lnDivide" + Format(intX)).Visible = False
Next intX
Else
For intX = 1 To 6
Me("lnDivide" + Format(intX)).Visible = False
Next intX
End If
If Me("Col3") = "CPT" Or Me("Col3") = "ODR" Or Me("Col3") = "TM" Then
For intX = 3 To 8
Me("Col" + Format(intX)).FontItalic = True
Me("Col" + Format(intX)).FontBold = True
Next intX
If Me("Col3") = "CPT" Then
For intX = 3 To 8
Me("Col" + Format(intX)).ForeColor = 16711935
Me("Col" + Format(intX)).FontSize = 12
Next
End If
If Me("Col3") = "ODR" Then
For intX = 3 To 8
Me("Col" + Format(intX)).ForeColor = 16711680
Me("Col" + Format(intX)).FontSize = 12
Next
End If
If Me("Col3") = "TM" Then
For intX = 3 To 8
Me("Col" + Format(intX)).ForeColor = 0
Me("Col" + Format(intX)).FontSize = 12
Next
End If
Else
For intX = 3 To 8
Me("Col" + Format(intX)).FontItalic = False
Me("Col" + Format(intX)).FontBold = False
Me("Col" + Format(intX)).ForeColor = 0
Me("Col" + Format(intX)).FontSize = 11
Next intX
End If
End Sub

Private Sub Detail_Retreat()
rstReport.MovePrevious
End Sub

Private Sub GroupHeader0_Format(Cancel As Integer, FormatCount As Integer)
If FormatCount = 1 Then
Me("col1") = rstReport(3)
Me("Col2") = rstReport(4)
End If
End Sub
Private Sub PageHeaderSection_Format(Cancel As Integer, FormatCount As
Integer)
pgH1 = rstReport(1) & ": " & Me.OpenArgs
pgH2 = "Vehicle ID: " & rstReport(0)
txtFleet = Me.OpenArgs
End Sub

Private Sub Report_Close()
rstReport.Close
End Sub
Private Sub Report_NoData(Cancel As Integer)
rstReport.Close
Cancel = True
End Sub

Private Sub Report_Open(Cancel As Integer)
On Error GoTo ErrorHandler:
Dim intX As Integer
Dim qdf As QueryDef
Dim frm As Form
' Set database variable to current database.
Set dbsReport = CurrentDb()
Set frm = Forms!frmCriteria
Me.RecordSource = "qryCrosstab"
Set qdf = dbsReport.QueryDefs("qryCrosstab")
qdf.Parameters("Forms!frmCriteria!cmb_VehicleID") _
= frm!cmb_VehicleID
qdf.Parameters("Forms!frmCriteria!cmbRequestNo") _
= frm!cmbRequestNo
' Open Recordset object.
Set rstReport = qdf.OpenRecordset()
rstReport.MoveFirst

' Set a variable to hold number of columns in crosstab query.
intColumnCount = rstReport.Fields.Count
ErrorHandlerExit:
Exit Sub
ErrorHandler:
Dim strErrDesc As String
If Right(Err.Description, 1) = "." Then
strErrDesc = Err.Description
Else
strErrDesc = Err.Description & "."
End If

MsgBox "Error: " & Err & " occurred: " & strErrDesc
Resume ErrorHandlerExit:
End Sub


--
Lydia Liu
Access/VB Programmer