Delete record in subform (Error 3167)

[skk]

Hi!

I'm getting an error msg below.
Record is deleted. (Error 3167)
You referred to a record that you deleted or that another user in a
multiuser environment deleted. Move to another record, and then try the
operation again.

Here is the code. This is a subform check box field where user can check or
uncheck to invite. What I'm trying to do is whenever user uncheck the Invite
field I want to delete the record and that's when I get this msg "Record is
deleted" the Error 3167. Thanks for your help!

Private Sub Invite_Click()
Dim s As String
If (Me.Invite = 0) Then

s = "Delete tblInviteesList.* FROM tblInviteesList " & _
"Where InviteesID = " & Me.InviteesID & " "
CurrentDb.Execute s
CurrentDb.TableDefs.Refresh
'Me.Requery

End If
End Sub

 

[Allen Browne]

The problem occurs because you are editing that record in your form, and at
the same time you are trying to delete it from your table.

Use the AfterUpdate event of the check box.
Test whether the box was checked or unchecked.
Undo the edit that is in progress.
Test if it is a new record (so can't be deleted.)
Then delete.

This kind of thing:

Private Sub Invite_AfterUpdate
If Me.Invite.Value Then
Me.Undo
If Not Me.NewRecord Then
RunCommand acCmdDeleteRecord
End If
End If
End Sub

 

[skk]

Brilliant! Thank you so much Allen now I understand what that error msg mean
and it works.