Access Audit Trail - Need to only record deleted and edited records

[MHamid]

Hello,

I am having an issue with my audit trail. It works perfectly, however I was told that only edited and deleted records will need to be kept in the audit trail. New records do not need to be in the audit trail.

As I attempted to manipulate my code to exclude new records, I've noticed that it still records them as Edited records. This should not be happening. If it's a new record being added, then the audit trail should ignore these. What is it that I am doing wrong?

Here is the Audit Trail Function code:

Option Compare Database
Public Function AuditChanges(txtRefID As String, UserAction As String)
On Error GoTo AuditErr
Dim DB As Database
Dim rst As Recordset
Dim clt As Control
Dim UserLogIn As String
Set DB = CurrentDb
Set rst = DB.OpenRecordset("SELECT * FROM tbl_AuditTrail", adOpenDynamic)
UserLogIn = Environ("USERNAME")
Select Case UserAction
    Case "New"
        With rst
            .AddNew
            ![DateTime] = Now()
            ![UserName] = UserLogIn
            ![FormName] = Screen.ActiveForm.Name
            ![Action] = UserAction
            ![RecordID] = Screen.ActiveForm.Controls(txtRefID).Value
            .Update
        End With
        
    Case "Delete"
        With rst
            .AddNew
            ![DateTime] = Now()
            ![UserName] = UserLogIn
            ![FormName] = Screen.ActiveForm.Name
            ![Action] = UserAction
            ![RecordID] = Screen.ActiveForm.Controls(txtRefID).Value
            .Update
        End With
        
    Case "Edit"
        For Each clt In Screen.ActiveForm.Controls
            If (clt.ControlType = acComboBox _
                 Or clt.ControlType = acTextBox) Then
                 If Nz(clt.Value) <> Nz(clt.OldValue) Then
                    With rst
                        .AddNew
                        ![DateTime] = Now()
                        ![UserName] = UserLogIn
                        ![FormName] = Screen.ActiveForm.Name
                        ![Action] = UserAction
                        ![RecordID] = Screen.ActiveForm.Controls(txtRefID).Value
                        ![FieldName] = clt.ControlSource
                        ![OldValue] = clt.OldValue
                        ![NewValue] = clt.Value
                        .Update
                    End With
                 End If
            End If
        Next clt
End Select
rst.Close
DB.Close
Set rst = Nothing
Set DB = Nothing
AuditErr:
    'MsgBox Err.Number & " : " & Err.Description, vbCritical, "Error"
    Exit Function
    
End Function

The code I originally used to call the audit trail for all new and edited records is:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.NewRecord Then
    Call AuditChanges("txtRefID", "New")
Else
    Call AuditChanges("txtRefID", "Edit")
End If
End Sub

What is it that I need to do to exclude new records?
I was thinking that the "Call AuditChanges("txtRefID", "New")" will need to be changed to do nothing if it's a new record, but I don't know what it should be replaced with.


Thank you

 

[xenou]

As a start, since you want to do nothing, you can try replacing with nothing:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.NewRecord Then
    'Call AuditChanges("txtRefID", "New")
Else
    Call AuditChanges("txtRefID", "Edit")
End If
End Sub

or (same logical result):

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Not Me.NewRecord Then
    Call AuditChanges("txtRefID", "Edit")
End If
End Sub

 

[MHamid]

Ha! That simple. As far as I can tell it works. Thank you Xenou!!

However, I added a date and an attachment columns to my table and form and now the add button is not working again. I really think I'm going to completely change the add record code. Ugh!!!