Why isn't MS Access "item found in collection?"

Question

I have a non-linked table "tblGrantRptData" in which I am trying to modify field records for subsequent filtering and export to EXCEL. I have ensured that all field names are correctly spelled, yet I still get an Error 3265, Item not found in this collection.

I have confirmed that MemmonthlyIncome is the correct spelling and is identified in the design as "currency."

enter image description here

here is the design veiw that appears to show the field name:

enter image description here

It stops at this line: If IsNull (!MemmonthlyIncome) Then

with the error 3265

If I can get some help to resolve this, I would then like to store the range "0-30"....

One suggestion below was to “decompile" and "recompile.” I have read that this may cause problems when the database is used on multiple workstations. I have revised the code to just get to first base….Any suggestions?

Private Sub cmdGenerateGrantRpt_Click()


'now run the qqAll query - this generates the tblGrantRptData - then close the query

DoCmd.SetWarnings False
DoCmd.OpenQuery "qqAll", acViewNormal, acEdit
DoCmd.Close acQuery, "qqAll"
DoCmd.SetWarnings True

'First set up the table: tblGrantRptData with the correct data in fields
Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("tblGrantRptData", dbOpenTable)

'Check to see if the recordset actually contains rows and fill in particular values

With rs
If .EOF And .BOF Then
MsgBox "There are no records in this time interval"
Exit Sub

Else
    .MoveFirst
    Do Until .EOF = True
        'Replace the monthly income with income categories
        If IsNull(!MemmonthlyIncome) Then
        .Edit
        !MemmonthlyIncome = "0-30"
        .Update
        End If
    Loop
End If
End With
rs.Close 'Close the recordset
Set rs = Nothing 'Clean up

End Sub

Compact & Repair. Can you edit this field directly in the underlying table using the UI? If so, Search & Replace `MemmonthlyIncome` with the name of the field in Table Design View. — kismert, Jun 04 '15 at 00:25

score 1 · Answer 1 · answered Jun 04 '15 at 03:31

1

In VBA, there is no Is Null expression as there is in SQL. Change to IsNull(rs!MemmonthlyIncome).

Possibly, Null is being referenced in recordset collection and hence returning the error.

answered Jun 04 '15 at 03:31

Parfait

104,375
17
94
125

good catch. However, still stops at this line: If rs!MemmonthlyIncome * 12 <= 30000 Or IsNull(rs!MemmonthlyIncome) Then with error:3265 @Parfait – Michael Jun 04 '15 at 15:30
Come to think of it too, `Is Null` would have outputted a different error. However, I just recreated your table and all worked. Check the field name carefully (three m's). Else, try [decompiling](http://stackoverflow.com/questions/3266542/how-does-one-decompile-and-recompile-a-database-application) the code or check for lingering [breakpoints](http://www.techonthenet.com/access/tutorials/vbadebug/debug02.php). – Parfait Jun 05 '15 at 01:37

HansUp · Accepted Answer · 2015-06-23T21:09:16.990

You get error #3265, "Item not found in this collection", at IsNull(!MemmonthlyIncome) in this context ...

Set rs = db.OpenRecordset("tblGrantRptData", dbOpenTable)
With rs
    If IsNull(!MemmonthlyIncome) Then

That would happen if tblGrantRptData does not include a field named MemmonthlyIncome. What you are seeing as the column heading in the table Datasheet View may be the field's Caption property. You could check the field's Name and Caption properties in the table's Design View. Or you could list the actual field names for your table in the Immediate window.

Here's an abbreviated list of the field names in my Contacts table:

set db = currentdb
for each fld in db.TableDefs("Contacts").Fields : ? fld.name : next
ID
Company
Last Name
First Name

You just added this screen capture of your table in Design View ...

enter image description here

Notice the field is named MemmothlyIncome, not MemmonthlyIncome (moth vs. month). So it wasn't a Name vs. Caption difference after all; you simply tried to use a misspelled field name. That spelling problem is also visible in the DataSheet View screen capture, but we didn't notice it there.

I added a screen shot of the "design view"...Also confirmed with the "database documenter" that the field is there.... — Michael, Jun 23 '15 at 21:24
The field is named `MemmothlyIncome`, but you're asking for `rs!MemmonthlyIncome` ... those names are not the same ... the first includes 1 `n`, the second includes 2 `n` ... `moth` <> `month` — HansUp, Jun 23 '15 at 21:29
You solved this issue...duuu...how many hours have I looked at those names and saw them the same...thank you! — Michael, Jun 23 '15 at 21:52

Why isn't MS Access "item found in collection?"

2 Answers2