run-time error 3265 Item Not Found in This Collection

  • Thread starter Thread starter Richard Hollenbeck
  • Start date Start date
R

Richard Hollenbeck

Does anybody have insight on this particular error?

Run-time error '3265':
Item not found in this collection

Here's a snippet of my code. The last line (txtAverages = rs...) was
highlighted in yellow:

Private Sub Form_Load()
Dim dbMain As DAO.Database, rsMain As DAO.Recordset, StrSql As String

Set dbMain = CurrentDb()
StrSql = "SELECT [qryCourseGradeDistribution].[Current Average] "
StrSql = StrSql & "FROM [qryCourseGradeDistribution] "
StrSql = StrSql & "WHERE [qryCourseGradeDistribution].[courseCode]= '"
StrSql = StrSql & " [Forms]![frmSelectCourse]![cboSelectCourse] & "'"

Set rsMain = dbMain.OpenRecordset(StrSql, dbOpenDynaset)
txtAverages = rsMain.Fields(1)
End Sub

Thanks,
Rich Hollenbeck
 
Your recordset has only one field, and the Fields collection is zero-based.
There is no .Fields(1) in your recordset, there is only .Fields(0).

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.
 
Are you actually getting any records returned? (I would imagine that an
empty recordset has no fields to reference).

Try insert a Breakpoint on "Set rsMain..." and type ?strSQL in the Immediate
window and double
check whether the SQL string you've constructed is what you intended.
StrSql = StrSql & "WHERE [qryCourseGradeDistribution].[courseCode]= '"
StrSql = StrSql & " [Forms]![frmSelectCourse]![cboSelectCourse] & "'"
Click to expand...
Looks wrong to me.
StrSql = StrSql & "WHERE [qryCourseGradeDistribution].[courseCode]= ' "
StrSql = StrSql & [Forms]![frmSelectCourse]![cboSelectCourse] & "'"
Would be my guess (extra space at end of 1st line, 1st quote removed from
2nd line)

You might also do a standard rsMain.EOF check and exit the procedure if
there are no records.

--
George Nicholson

Remove 'Junk' from return address.


Richard Hollenbeck said:
Does anybody have insight on this particular error?

Run-time error '3265':
Item not found in this collection

Here's a snippet of my code. The last line (txtAverages = rs...) was
highlighted in yellow:

Private Sub Form_Load()
Dim dbMain As DAO.Database, rsMain As DAO.Recordset, StrSql As String

Set dbMain = CurrentDb()
StrSql = "SELECT [qryCourseGradeDistribution].[Current Average] "
StrSql = StrSql & "FROM [qryCourseGradeDistribution] "
StrSql = StrSql & "WHERE [qryCourseGradeDistribution].[courseCode]= '"
StrSql = StrSql & " [Forms]![frmSelectCourse]![cboSelectCourse] & "'"

Set rsMain = dbMain.OpenRecordset(StrSql, dbOpenDynaset)
txtAverages = rsMain.Fields(1)
End Sub

Thanks,
Rich Hollenbeck
Click to expand...
 
Thanks! That did the trick.
Brendan Reynolds said:
Your recordset has only one field, and the Fields collection is zero-based.
There is no .Fields(1) in your recordset, there is only .Fields(0).

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.


Richard Hollenbeck said:
Does anybody have insight on this particular error?

Run-time error '3265':
Item not found in this collection

Here's a snippet of my code. The last line (txtAverages = rs...) was
highlighted in yellow:

Private Sub Form_Load()
Dim dbMain As DAO.Database, rsMain As DAO.Recordset, StrSql As String

Set dbMain = CurrentDb()
StrSql = "SELECT [qryCourseGradeDistribution].[Current Average] "
StrSql = StrSql & "FROM [qryCourseGradeDistribution] "
StrSql = StrSql & "WHERE [qryCourseGradeDistribution].[courseCode]= '"
StrSql = StrSql & " [Forms]![frmSelectCourse]![cboSelectCourse] & "'"

Set rsMain = dbMain.OpenRecordset(StrSql, dbOpenDynaset)
txtAverages = rsMain.Fields(1)
End Sub

Thanks,
Rich Hollenbeck
Click to expand...
Click to expand...
 
(I would imagine that an
empty recordset has no fields to reference).
Click to expand...

Yes it does. It just doesn't have any records.... Think of this:

Set rs = db.OpenRecordset( _
"SELECT Something, SomethingElse FROM MyTable WHERE False", _
dbOpenSnapshot, dbForwardOnly
)

Debug.print rs.BOF, rs.EOF ' prints true, true

Debug.print rs.Fields.Count ' 2
Debug.print rs.Fields(1).Name ' SomethingElse
Debug.print rs.Fields(0).Type ' depends on the original table

Debug.print rs.Fields(0).Value ' error: no current record



So the fields exist, but the values are held in the current record, which
does not exist.

Hope that helps


Tim F
 
Back
Top