CODEWITHSUNDEEP
ms-accessvba
user2119980
user2119980

Reputation: 509

Access Delete Command Issue

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

Upvotes: 0

Views: 267

Answers (2)

HansUp
HansUp

Reputation: 97131

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 _

Upvotes: 2

Johnny Bones
Johnny Bones

Reputation: 8414

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.

Upvotes: 0

Related Questions