Log Deletes (1 Viewer)

[khurram7x]

Hi,

What is the best place to log data into history/audit table before record is deleted please??

If I do this in Before_Delete event, then I've to delete the record from history/audit table if user presses Cancel on delete confirmation box.

If I use After_Delete, then it'll be too late.

In my understanding, I create a temporary table, saves table values there and then copy those values to audit/history table in after delete confirm table and remove the table.

Is there a standard way to do this without introducing this intermediary table please?

Thanks,
K

 

[CJ_London]

in the before delete event, store the uniqueID in a public variable.

in the after delete event, insert to log using the public variable value.

 

[arnelgp]

use Delete event to save save the field(s) value to an array.

use AfterDelConfirm event to save each array value to your audit table. Take note of the Status parameter of the sub. You should test to see if the status=0 (meaning user choose yes to delete the record(s)). the loop to each array and save to your audit table.

 

[gemma-the-husky]

for the benefit of myself and all users, if you just select a record, and press delete. what events actually fire that are trappable?

if you turn warnings off do you still get the before and after delete confirm events?

 

[arnelgp]

the Delete event is trappable event warning is off.
in that case you can have a timer to check if the length of the array in my post is zero. if not use the timer event to save the array to audit table, set the length of array to zero, for another delete operation.

initially setting the length of array to zero at load time of form.

but, who would disable confirmation for deletion?

 

[jdraw]

Depending on the sophistication of your application and requirements, you could use Transaction Logging.
more about committing Transactions here.

Good luck.

 

[The_Doc_Man]

This is partly a planning issue.

If I have records intended to be deleted at some point in their life cycle, I always include a spare Boolean (Y/N) flag in the record. That flag is set false for all records until I do a DELETE that encompasses a subset of all records in the table. When I trigger the "select records to delete" code (whether as a dialog or as an update query based on data criteria), I set the flag to TRUE to show that it is about to be deleted. If I was planning this ahead of time, my queries also were coded to refuse to see any records for which this particular flag was TRUE. All the queries ... except the ones that do logging and deletion.

I trigger the query that "does its thing" to do an INSERT INTO my audit table with the appropriate data and audit-action codes that tell me the record was audited pre-deletion. Then when all auditing is done, just do a DELETE from the table WHERE PREDELETE = TRUE and there you go, job finished.

If you did not anticipate the need for this activity up front, you can still retrofit the right flag into the table, assure that it is FALSE for all records, and then go on with the process I described above.

 

[khurram7x]

Thank you, for all your replies. What I'm understanding is I've to save the data from table into some temporary table or variables and then after delete copy this data to History/Log table.

If I use before delete, then I'll simply copy the record from base table to log table... same as I do when I'm using Updates!!

I could also use OnClick event of 'Delete' button to copy records from main to base table.

Last two options will be much simpler practice as compare to copy the record to some other table. Just I'm interested to find out please what's the best place to use this INSERT INTO query and why, Before Delete or 'Delete' button??

As requested, pasting the code under OnClick 'Delete' button. I've just now set the Warnings Off/On.

Dim Response

If Form.NewRecord And Not (Form.Dirty) Then
DoCmd.Beep
ElseIf Form.NewRecord And Form.Dirty Then
Me.Undo
ElseIf Not Form.NewRecord Then
Response = MsgBox("Are you sure you really want to remove this record??", vbYesNo + vbCritical + vbDefaultButton2, "WARNING")
If Response = vbYes Then
Response = MsgBox("Deleting this record will also delete all progress information recorded against this item. Please click 'Cancel' if you still want to CANCEL this delete operation.", vbOKCancel + vbCritical + vbDefaultButton2, "FINAL WARNING!!")
If Response = vbOK Then
DoCmd.SetWarnings (0)
DoCmd.RunCommand acCmdDeleteRecord
DoCmd.SetWarnings (1)
End If
End If
End If

Thank you,
K

 

[arnelgp]

if you disable the warnings there, BeforeDelConfirm and AfterDelConfirm event will not fire.
So you need a way, maybe a Timer event that will check if there is a buffer for deletion.

 

[gemma-the-husky]

the Delete event is trappable event warning is off.
in that case you can have a timer to check if the length of the array in my post is zero. if not use the timer event to save the array to audit table, set the length of array to zero, for another delete operation.

initially setting the length of array to zero at load time of form.

but, who would disable confirmation for deletion?

I would.
I tend to disallow deletes on forms.

if I want a delete I add a button
then, in the button click

pseudocode

if msgbox("please confirm deletion") then
    runcommand accmdeleterecord
end if

it is an irritation that there is no afterdelete event, or other suitable event that could be used for "tidying up"

maybe you could set a "deleted record" flag, and then test this in the current event after the deletion.

 

[khurram7x]

Is OnDelete more like BeforeDelete event?

I feel like Delete button is a place then where I could write query to copy record in history table.

Thank you for all the information and advice.

K