CODEWITHSUNDEEP
ms-accessvba
user2296381
user2296381

Reputation: 197

Delete Rows of Data From Live Table inside Form?

I'm trying to make an Access GUI that can let users freely edit data before submitting.

However, it won't let me freely delete the data without looping into an Run Time Error 3167 "Record is deleted". Obviously it has something to do with the fact that I am telling it to delete the data from a live table that is always being displayed via the Form.

Can anyone tell me a way I can work around this?

Private Sub Cmd_delete_batch_Click()
Dim RS As Recordset
Dim ls_Batch_type As String
Dim ls_batch_no As String

If MsgBox("This action will delete all selected Mail Merge Items. Are you Sure?", vbYesNo, "Mail Merge Delete Warning") = vbNo Then
   Exit Sub
End If

    Set RS = FRM_Batch.Form.RecordsetClone
    RS.MoveFirst
    For i = 1 To RS.RecordCount
        If RS.Fields("SELECT_FLAG") = True Then
            CurrentDb.Execute "DELETE * FROM tblFinalMailMerge WHERE SELECT_FLAG = True"

        End If
    RS.MoveNext
Next i

FRM_Batch.Form.Requery

1

Upvotes: 1

Views: 139

Answers (1)

HansUp
HansUp

Reputation: 97131

Seems to me you want to delete all records from tblFinalMailMerge where the user has set SELECT_FLAG to True. So I don't see a need to use a recordset for this. Just execute the DELETE statement.

If MsgBox("This action will delete all selected Mail Merge Items. " & _
        "Are you Sure?", vbYesNo, _
        "Mail Merge Delete Warning") = vbYes Then
    CurrentDb.Execute "DELETE FROM tblFinalMailMerge " & _
        "WHERE SELECT_FLAG = True", dbFailOnError
End If

Upvotes: 2

Related Questions