accessaspire219
Registered User.
- Local time
- Yesterday, 19:47
- Joined
- Jan 16, 2009
- Messages
- 126
I am getting this error everytime I run this script. Can anyone tells what is causing the error?
To give you a brief description of my database -
I have got a database that consists of a query which calculates monthly inventory data (MIOH) A cross tab query then creates a cross tab such that employee number is the row heading, month (mm/yyyy) , YTD (yyyy) are column headings and MIOH is the value. There are two parameters in this query which are entered by the user using a form. These are start date and end date. The SQL of the cross tab query is given below.
CrossTab query SQL
[/SIZE][/FONT]
The code I am using is:
I get error 3265 - item not found in collection and the debugger highlights the line
When I hover my mouse over the rstReport(intX).Name part, the pop up shows "rstReport(intX).Name = <item not found in this collection>
Any help is appreciated!
Thanks!
To give you a brief description of my database -
I have got a database that consists of a query which calculates monthly inventory data (MIOH) A cross tab query then creates a cross tab such that employee number is the row heading, month (mm/yyyy) , YTD (yyyy) are column headings and MIOH is the value. There are two parameters in this query which are entered by the user using a form. These are start date and end date. The SQL of the cross tab query is given below.
CrossTab query SQL
Code:
[FONT=Calibri][SIZE=3]TRANSFORM Sum(Union.METRIC) AS SumOfMETRIC[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]SELECT Union.ABUYR, Union.TYPE[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]FROM [Union][/SIZE][/FONT]
[FONT=Calibri][SIZE=3]WHERE (((Union.PERIOD1) Between [Forms].[Form1]![StartDate] And [Forms].[Form1]![EndDate]))[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]GROUP BY Union.ABUYR, Union.TYPE[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]PIVOT Union.PERIOD1;The code I am using is:
Code:
Option Compare Database
' Constant for maximum number of columns CrossTab query would
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_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("Head" + Format(intX)) = rstReport(intX).Name
Next intX
' Hide unused text boxes in page header.
For intX = (intColumnCount + 1) 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
' Form1 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!Form1
' Open QueryDef object.
Set qdf = dbsReport.QueryDefs("CrossTab")
' Set parameters for query based on values entered
' in Form1 form.
qdf.Parameters("[Forms].[Form1]![StartDate]") _
= frm!StartDate
qdf.Parameters("[Forms].[Form1]![EndDate]") _
= frm!EndDate
' 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 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 SubI get error 3265 - item not found in collection and the debugger highlights the line
Code:
Me("Head" + Format(intX)) = rstReport(intX).NameAny help is appreciated!
Thanks!
Last edited:
