Runtime error '91'
Running Access 2010.
I have two reports running off of the same crosstab query. I copied one report to make the second report, then modified the second report to change the background of column fields satisfying certain conditions. These lines of code were added to the Detail_Format section, in color below. The report with the extra code lines does not error--the original report errors. Would appreciate any insights!
If I say OK (rather than debug) after the error message, I can then click the button for the report again and it runs without complaint. And, as I said, the report with the added code never errors.
Running Access 2010.
I have two reports running off of the same crosstab query. I copied one report to make the second report, then modified the second report to change the background of column fields satisfying certain conditions. These lines of code were added to the Detail_Format section, in color below. The report with the extra code lines does not error--the original report errors. Would appreciate any insights!
Code:
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 = 11
' 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 colname As Variant
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
Me("Col" + Format(intX)) = rstReport(intX - 1)
colname = "Col" + Format(intX)
'Code in orange added into the second report which does not error:
[COLOR="DarkOrange"]
If Me!flag = "u" And colname <> "Col1" Then
Me(colname).BackColor = RGB(255, 255, 139)
Else
Me(colname).BackColor = vbWhite
End If[/COLOR]
Next intX
' Hide unused text boxes in the "Detail" section.
'For intX = intColumnCount + 2 To conTotalColumns
For intX = intColumnCount + 1 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 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("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 11
Me("Head" + Format(intX)).Visible = False
Next intX
End Sub
Private Sub Report_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("Rept2NoFilter_Crosstab")
' Open Recordset object.
Set rstReport = qdf.OpenRecordset()
' Set a variable to hold number of columns in crosstab query.
intColumnCount = rstReport.Fields.Count
If intColumnCount > 11 Then
DoCmd.OpenForm "Oopsnumber"
Cancel = True
End If
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 SubIf I say OK (rather than debug) after the error message, I can then click the button for the report again and it runs without complaint. And, as I said, the report with the added code never errors.
