Access Delete Command Issue

Question

I am still working away on this form and now I am ecountering an error with the delete code portion. I believe it is having a problem recognizing what I am selecting but I could be wrong. The error message reads RUN TIME ERROR 3265 ITEM NOT FOUND IN THIS COLLECTION

The whole code reads:

Private Sub cmdDelete_Click()
'delete record
'check existing selected record
If Not (Me.TableSub.Form.Recordset.EOF And Me.TableSub.Form.Recordset.BOF) Then
    'confirm delete
    If MsgBox("Are you sure you want to delete this record?", vbYesNo) = vbYes Then
    'delete now
    CurrentDb.Execute "DELETE FROM KWTable WHERE text_key='" &     Me.TableSub.Form.Recordset.Fields    ("text_key") & "'"
        'refresh data in list
        Me.TableSub.Form.Requery
    End If
End If
End Sub

And the portion that is highlighted is:

CurrentDb.Execute "DELETE FROM KWTable WHERE text_key='" & Me.TableSub.Form.Recordset.Fields    ("text_key") & "'"

HansUp · Accepted Answer · 2013-07-29 16:52:08Z

The error indicates the subform's Recordset does not include a field named text_key.

In detail, you are getting error #3265, "Item not found in this collection." The only place in the code which could trigger that error is Me.TableSub.Form.Recordset.Fields("text_key"). You know Me.TableSub.Form.Recordset is valid, because if it weren't then you would have gotten an error earlier (on If Not (Me.TableSub.Form.Recordset ...). That means the recordset's Fields collection does not include an Item named "text_key".

If you revise the code as @JohnnyBones suggested, you will continue to get the same error, but the error will then be triggered at the "MySQLString = ..." line instead of the CurrentDb.Execute line.

When you get that error message, click the Debug button, and use this in the Immediate window to list the names of the fields in that Recordset.

for each f in Me.TableSub.Form.Recordset.Fields: _
? f.Name : _
next

Note the _ line continuation characters. You must use at least 1 space before and 0 spaces after each _

Johnny Bones · Accepted Answer · 2013-07-26 20:10:05Z

0

My best guess is that your problem lies here:

Me.TableSub.Form.Recordset.Fields    ("text_key")

Try doing this:

MySQLString = "DELETE FROM KWTable WHERE text_key='" &     Me.TableSub.Form.Recordset.Fields    ("text_key") & "'"
CurrentDb.Execute MySQLString

I have a feeling that when you break the code, your MySQLString variable isn't going to be equal to what you think it is. And I'm pretty sure that's because of the double-quotes around the field name, unless I'm misunderstanding your error.

answered Jul 26, 2013 at 20:10

Johnny Bones

8,5007 gold badges61 silver badges127 bronze badges

1 Comment

user2119980 Over a year ago

I tried switching in your code and I continue to get the same error.

Collectives™ on Stack Overflow

Access Delete Command Issue

2 Answers 2

Comments

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Related