Problems with Delete and Me.Recordset , Me.RecordsetClone

[Dave M]

Hi,

I'm using Access 2002 with Jet 4.0, SP7. I'm setting up
an audit trail and have noticed some odd behavior with
record deletions I initiate on the form (also problems
with replace, but just focusing on delete for now).
Here's a simplified example of the problem.

I have a form bound to a data table, with primary
key "ID". I have set the form to confirm deletions. In
the Private Sub Form_Delete, I have

Private Sub Form_Delete(Cancel As Integer)
MsgBox Me.Recordset.Fields("ID").Value
End Sub

If I attempt to delete a record , the Message Box appears
and displays the ID of the record being deleted. Then
the delete confirmation dialog box appears. If I say no
at the confirmation step, the form becomes unbound and
all the form values show "#Name?".

If I attempt to delete a record and confirm the deletion,
the record is deleted. However if I try to delete the
next record I get an error: Run-time error'3167':
Record is deleted. Doing a Me.Requery at the
AfterDelConfirm doesn't resolve the issue.

Now, if I use Me.RecordsetClone instead, I get a
different set of errors.

Private Sub Form_Delete(Cancel As Integer)
MsgBox Me.RecordsetClone.Fields("ID").Value
End Sub

If I attempt to delete a record and say no at the
confirmation step, everything seems fine until I try to
delete the record again. Then I get Run-time
error '3420': Object invalid or no longer set.

If I attempt to delete a record and confirm the deletion,
the record is deleted. But when I try to delete the next
record I get error '3167'.

These errors occur when I try to access any properties of
either Me.Recordset or Me.RecordsetClone in Sub
Form_Delete. I assume it must interefere with the
transaction in some way. Any insights??

Thanks!

 

[Allen Browne]

Dave, you are right that Access wraps the deletion in a transaction, which
is interferring with what you are trying to do.

For an explanation of how to create a trail of deletions, and the
interaction of the deletion events, see:
Audit Trail - Log changes at the record level
at:
http://allenbrowne.com/AppAudit.html