run-time error 3265 Item Not Found in This Collection

[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

 

[Brendan Reynolds]

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.

 

[George Nicholson]

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] & "'"

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.

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

 

[Richard Hollenbeck]

Thanks! That did the trick.

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.

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

 

[Tim Ferguson]

(I would imagine that an
empty recordset has no fields to reference).

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