stopping a procedure if the user cancels a delete operation

[Paul James]

I'm using the following code for the On Delete event of a form to clear a
checkbox in a related table if the user deletes a record in the form:

Private Sub Form_Delete(Cancel As Integer)
Dim InvoiceNumber As String
If Not IsNull([Forms]![frmInvoice_AccountingOnly]![Receipt_ID]) Then
InvoiceNumber = [Forms]![frmInvoice_AccountingOnly]![Receipt_ID]
CurrentDb.Execute "UPDATE tblReceipt SET Matched = 0 WHERE
tblReceipt.ReceiptID = " & InvoiceNumber & ""
End If
End Sub

I would like the this code to run if the user clicks "Yes" when prompted to
confirm the deletion. However, if the user clicks "No" I would like to
prevent that code from executing.

What can I put in the procedure above to keep it from running if the user
fails to confirm the deletion?

Thanks in advance.

Paul

 

[Ken Snell]

I would use your own message box to ask for confirmation (this means you'll
also need to put code on the BeforeDelConfirm event); I also added a Cancel
if the answer is No:

Private Sub Form_Delete(Cancel As Integer)
Dim InvoiceNumber As String

If Not IsNull([Forms]![frmInvoice_AccountingOnly]![Receipt_ID]) Then
If vbYes = MsgBox("Are you sure you want to delete this record?", _
vbQuestion+vbYesNo, "Delete?") Then
InvoiceNumber = [Forms]![frmInvoice_AccountingOnly]![Receipt_ID]
CurrentDb.Execute "UPDATE tblReceipt SET Matched = 0 WHERE
tblReceipt.ReceiptID = " & InvoiceNumber & ""
Else
Cancel = True
End If
End If
End Sub


Then put this code in the BeforeDelConfirm event to prevent the showing of
ACCESS' confirming message box:

Private Sub Form_BeforeDelConfirm(Cancel As Integer, Response As Integer)
Response = acDataErrContinue
End Sub

 

[Paul James]

Great. Thanks Ken.