Audit Log Delete Record Puzzle

[Superglide]

Perhaps a real challenge for you. I am creating an audit log for a database I have developed. This has been an interesting challenge, more complex than I expected. Here's a (hopefully brief) summary of how it works.

For the audit log, I want to capture previous values for select fields when they are changed. To accomplish this, I have onDirty and onUndo events for the fields I want log entries for. For example:

cmbStateProvince_Dirty
'This code captures the previous value of the record when it is changed by the user.
strPrevState = Nz("[State:" & Me.cmbStateProvince.Column(1) & "]", "null")

cmbStateProvince_Undo
'This code clears the previous stored value when the user undoes the change)
strPrevState = vbNullString​

I also have an onDirty for the form as well; this captures the record ID.

Then, I have a Form_AfterUpdate, which determines if anything changed, and if so writes the previous values to the log.

Form_AfterUpdate
strHoldPrevData = (strPrevState & strPrevZip & strPrevPhone)
If strHoldPrevData <> "" Then
Write to the audit log
Else
End if
ClearStrings​

(ClearStrings is a private sub that sets all str values to vbnullstring.)

Easy.

Deleting records is similarly straightforward. When the user deletes a record, I do two things:

Form_Delete
'This code determines, prior to deleting the record, if any fields/data were changed; if so, it creates a log entry for those changes.
If strHoldPrevData <> "" Then
Write to the audit log
Else
End if

'This code then captures the other previous values for the field I want to track as part of the log; this way I capture all current field data/values of the record prior to its deletion.
strPrevState = Nz("[State:" & Me.cmbStateProvince.Column(1) & "]", "null")
strPrevZip = Nz("[Zip:" & Me.txtZip & "]", "null")
strRecordID = Nz(Me.txtRegistrantID, "")
strHoldPrevData = (strPrevState & strPrevZip & strPrevPhone)​

Then, I have an after delete confirm on the form, which, if the user says Yes, deletes the record and writes the log.

Form_AfterDelConfirm
If Status = acDeleteOK Then
Write the audit log
Else
End If

ClearStrings​

Works beautifully. But there is a problem.

If the record has related records in another table, then after the user says Yes to delete the record, they receive the message (an error) saying they cannot delete the record because there are related records (I have cascade deletes turned off). At this point, correctly no log is written (except the log for changes to the original record data, if there are any; but no delete entry is written).

If the user attempts again (prior to exiting the form) to delete the record, and receives once again the error saying the record cannot be deleted because of related records, I get a delete record log entry even though no record has been deleted.

I am stumped where this is happening.

I have tried writing ClearStrings into the error handling code, for example:

On Error GoTo Err_Form_Delete

Exit_Form_Delete:
Exit Sub

Err_Form_Delete:
ClearStrings
MsgBox err.Description
Resume Exit_Form_Delete​

I've also tried this on AfterDeleteConfirm, but this it doesn't work, or writes an empty entry to the audit log table.

Any thoughts what I'm missing? Better way to do this?

The app is not crashing, just writing bogus delete records to the audit log.

 

[Superglide]

Thanks all who viewed. I know it's quite a complex puzzle.

Here is a sample db with vba code that shows what I'm doing. Rename it to .mdb.

If you modify any registrant info, it'll create a log entry, which you can view from the Edit Registrant form by clicking on the H button.

If you go to an entry that has NO related record and select the record and delete it, you will be able to see the delete log entry by looking in the tblAuditLog.

If you go to an entry that has a related record and select the record and delete it, you get the error message. If you select it again and delete it, you get the error message again. But this time you will be able to click on the H button and see that a delete record entry was created.

I'm not sure where to clear out the data when an error occurs while attempting to delete a record.

Thanks for any help!

 

[JANR]

If the record has related records in another table, then after the user says Yes to delete the record, they receive the message (an error) saying they cannot delete the record because there are related records (I have cascade deletes turned off).

If you want to fysically delete records then one option is to turn cascade delete ON, BUT that has dangerous drawbacks.

You can however create your own cascade delete which you have better control, but you have to delete the ChildRecords first and then the ParentRecord

ex:

Public Sub DeleteRecords(RecordID As Long)
Currentdb.Execute " DELETE * FROM tblChild WHERE [ForeignKEY] =" & RecordID, dbFailOnError
Currentdb.Execute " DELETE * FROM tblParent WHERE [RecID] =" & RecordID, dbFailOnError
End Sub

Just pass the parent recordID to the sub. (assuming it's a number)

DeleteRecords Me!RecID

While it seems ok to get rid of records you dont want anymore, most developers just flags a record as deleted instead of acctually deleting it. It has many advantages, for one you can easilly rollback if a mistake is made.

Just add a Boolean Yes/No field in your parent table and set it to TRUE when a record is "deleted" and then you just filter them out in your forms recordsource by adding a where-clause

..... WHERE boolDeleted = False;

JR

 

[Superglide]

Thanks for the response JR. I considered the delete boolean flag on deleted records. I ultimately decided to go with the physical delete because 99% of the time a record is deleted due to duplication in data entry (i.e. a business process issue, which we apparently can't solve). So actually deleting them is OK, but we still want a record of the event.

That said I've continued to shy away from cascade deletes because of the danger.

To get around my problem, I might take your suggestion and perform a cascade delete and write the logs accordingly.

Thanks for the feedback!


-kjr