Record deleting after setting me.AllowDeletions to false

Question

Using Access 2007 front end with SQL Server 2008 r2 back end. I have records that can be ticked as 'Patient Seen'. If 'Patient Seen' is True then deletion is not supposed to happen. Conversely if 'Patient Seen' is false deletion of record is allowed. What I have so far is this:

    Set db = CurrentDb
    Set rs = db.OpenRecordset("select PatientSeen,PatientLogID from PatientLog where           PatientLogID = " & Me.PatientLogID, dbOpenDynaset, dbSeeChanges)
    test = rs!PatientLogID
    If MsgBox("Are you sure you want to delete record?", vbYesNo, "Delete Record") =   vbYes Then
    If rs!PatientSeen = False Then

    strSQL = "UPDATE [PatientLog] " _
       & "Set IsDeleted = 1 " _
       & "Where PatientLogID = " & Me.PatientLogID
    'View evaluated statement in Immediate window.
    Debug.Print strSQL
    DoCmd.SetWarnings False
    DoCmd.RunSQL strSQL

    ElseIf rs!PatientSeen = True Then
    MsgBox ("Patient already seen, cannot delete")
    Me.AllowDeletions = False
    'Me.Undo
    End If
    Else
    Me.Undo
    Exit Sub
    End If

The Patient Seen false works fine. It updates the IsDeleted to 1 for the correct record. However if the Patient Seen is True the msgbox (Patient already seen...) pops up but the record still gets completely deleted from table instead of. Same for if I click no on the initial question of 'sure you want to delete'. Instead of just exiting sub it deletes the record.

Im still fairly new to Access and building a back end SQL but pretty familiar with VBA from years of Excel reports. Any suggestions greatly apprecitated.

Where does this code live? Clearly, it is in a form, but is it on a button click? A form event? Where does the actual deletion occur? Nothing in your code actually deletes a record. — pteranodon

pteranodon pteranodon · Accepted Answer · 2013-12-31T20:41:03

The line Me.AllowDeletions = False does not stop a deletion in progress within the OnDelete event, it just changes the form property AllowDeletions to No (which will prevent deletions of other records after this one is done).

To stop the current record from being deleted in the OnDelete event, just add the line Cancel = True. The state of the Cancel parameter (which is False coming in) when exitting OnDelete determines whether the deletion is committed.

Record deleting after setting me.AllowDeletions to false

1 Answers