lemon_balm
Registered User.
- Local time
- Today, 00:57
- Joined
- Feb 7, 2006
- Messages
- 65
I have been trying to get my crosstab working and have run into a spot of bother. I have followed the process detailed in SOLUTIONS.mdb to the letter but keep getting thrown out with the rejection Run-Time Error 2448 You can't assign a value to this object" at the line highlighted in the code below. What seems strange is that the object causing the problem is just being passed a standard variable which has been declared at 3 other instances in the code with no problems occuring.
If anyone has any ideas then they would be greatly appreciated.
Regards
Jason
If anyone has any ideas then they would be greatly appreciated.
Regards
Jason
Code:
Option Compare Database
Option Explicit
Const conTotalColumns = 12
Dim dbsReport As Database
Dim rstReport As Recordset
Dim intColumnCount As Integer
Dim lngRgColumnTotal(1 To conTotalColumns) As Long
Dim lngReportTotal As Long
Private Sub InitVars()
Dim intX As Integer
lngReportTotal = 0
For intX = 1 To conTotalColumns
lngRgColumnTotal(intX) = 0
Next intX
End Sub
Private Function xtabCnulls(varX As Variant)
If IsNull(varX) Then
xtabCnulls = 0
Else
xtabCnulls = varX
End If
End Function
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim intX As Integer
If Not rstReport.EOF Then
If Me.FormatCount = 1 Then
For intX = 1 To intColumnCount
Me("Col" + Format$(intX)) = xtabCnulls(rstReport(intX - 1))
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
Dim lngRowTotal As Long
If Me.PrintCount = 1 Then
lngRowTotal = 0
For intX = 2 To intColumnCount
lngRowTotal = lngRowTotal + Me("Col" + Format$(intX))
lngRgColumnTotal(intX) = lngRgColumnTotal(intX) + Me("Col" + Format$(intX))
Next intX
Me("Col" + Format$(intColumnCount + 1)) = lngRowTotal
lngReportTotal = lngReportTotal + lngRowTotal
End If
End Sub
Private Sub Detail_Retreat()
rstReport.MovePrevious
End Sub
Private Sub PageHeader_Format(Cancel As Integer, FormatCount As Integer)
Dim intX As Integer
For intX = 1 To intColumnCount
Me("Head" + Format$(intX)) = rstReport(intX - 1).name
Next intX
Me("Head" + Format$(intColumnCount + 1)) = "Totals"
For intX = (intColumnCount + 2) To conTotalColumns
Me("Head" + Format$(intX)).Visible = False
Next intX
End Sub
Private Sub Report_Open(Cancel As Integer)
Dim intX As Integer
Dim qdf As QueryDef
If Not (IsLoaded("SetDates")) Then
Cancel = True
MsgBox "To preview or print this report, you must open Setdates in form view", vbExclamation, "Mustopen dialog box"
Exit Sub
End If
Set dbsReport = CurrentDb
Set qdf = dbsReport.QueryDefs("xtabqry")
qdf.Parameters("forms!setdates!begdate") = forms!setdates!begdate
qdf.Parameters("forms!setdates!enddate") = forms!setdates!enddate
Set rstReport = qdf.OpenRecordset()
intColumnCount = rstReport.Fields.Count
End Sub
Private Sub Report_close()
On Error Resume Next
rstReport.Close
End Sub
Private Sub Report_NoData(Cancel As Integer)
MsgBox "No records match the criteria", vbExclamation, "No Records Found"
rstReport.Close
Cancel = True
End Sub
Private Sub ReportFooter_Print(Cancel As Integer, PrintCount As Integer)
Dim intX As Integer
For intX = 2 To intColumnCount
[B][COLOR="Red"]Me("Tot" + Format$(intX)) = lngRgColumnTotal(intX)[/COLOR][/B]
Next intX
Me("Tot" + Format$(intColumnCount + 1)) = lngReportTotal
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)
rstReport.MoveFirst
InitVars
End Sub