Access: Reading deleted record buffer

Question

According to the Access (2003) documentation, when you delete a record, it's placed in a buffer, then the BeforeDeleteConfirm event is raised, then the AfterConfirmDelete ad Delete events. In my table if a record is deleted, I need to add one of its field values (a quantity value) to a field in another table.

I tried setting the quantity to a private variable in the Current event, but as soon as I delete click delete, it looks like the Current event fires again and resets my variable.

How can I access the value of a field in a deleted record?

Answer 1

This solution may require some redesigning, but it's what I use for a form of my own:

1. Add a boolean field to your record table called "Visible".
2. On your main form, set your form.filter="[Visible]=True"
3. On your main form, set AllowFilters=Yes.
4. On your main form, set your Form.Allow Deletions=False (we don't want them actually deleting the record)
5. Make a custom Delete command button that sets Visible=False.
6. If you feel the need later, you could add some maketable queries and delete queries to clean up your main table and move the deleted values off to another table.

This way, the user doesn't actually 'delete' the record. Rather, they just make that record invisible. And later, when the form is unloaded, you could add in some clean-up queries.

Answer 2

The values in bound controls are still available at the form delete event. In this sample, txtid is a bound text box.

Private Sub Form_Delete(Cancel As Integer)
    Dim strActivity As String
    Dim strSql As String

    strActivity = Me.txtid & " deleted."
    strSql = "INSERT INTO tblAudit (activity) " & _
        "VALUES (""" & strActivity & """);"
    CurrentDb.Execute strSql, dbFailOnError
End Sub

Answer 3

Generally, if I want to do something in the OnDelete events other than the default actions, I will tend to turn AllowDeletes OFF and create a DELETE RECORD command button that does what I want.

Thus, I don't depend on the built-in events. If I need to store a value, I do it, the pop up the confirm message, and then do what's needed, by whichever method I consider easiest.

I just find this a lot easier, particularly because user settings can cause things to behave differently. For example, the two Confirm events don't happen when the users has these settings (from the A2003 help file, but it's always been this way):

Note The AfterDelConfirm event does not occur and the Delete Confirm dialog box isn't displayed if you clear the Record Changes check box under Confirm on the Edit/Find tab of the Options dialog box, available by clicking Options on the Tools menu.

So I just avoid the whole thing by rolling my own deletion code and avoiding the default behaviors.

(and yes, if you've followed my posts over the years, you know that it's pretty unusual for me to advise against simply using default Access behaviors, but this is one case where I feel more control is appropriate in almost all cases)