Database Audit Trail (1 Viewer)

[cnstarz]

Is there way a to get this audit trail module to work with a form in Datasheet view? It works fine with a form in Single Form view; I can open the table where the audits are saved and everything looks good. But when implemented on a form in datasheet view, nothing happens. I don't get any errors and nothing is saved to the audit table. Any advice?

Link: http://www.fontstuff.com/access/acctut21.htm

Here's the vba code:

Sub AuditChanges(IDField As String, UserAction As String)
On Error GoTo AuditChanges_Err

    Dim db As DAO.Database
    Dim rsT As DAO.Recordset
    Dim ctl As Control
    Dim datTimeCheck As Date
    Dim strUserID As String

    Dim frm As Form
    
    Set db = CurrentDb()
    Set rsT = db.OpenRecordset("tbAssetHistory", dbOpenDynaset)
    datTimeCheck = Now()
    strUserID = Environ("USERNAME")
    
    Select Case UserAction
        Case "EDIT"
            For Each ctl In Screen.ActiveForm.Controls
                If ctl.Tag = "Audit" Then
                    If Nz(ctl.value) <> Nz(ctl.OldValue) Then
                        With rsT
                            .AddNew
                            ![DateTime] = datTimeCheck
                            ![Login] = strUserID
                            ![FormName] = Screen.ActiveForm.Name
                            ![Action] = UserAction
                            ![Asset_ID] = Screen.ActiveForm.Controls(IDField).value
                            ![FieldName] = ctl.ControlSource
                            ![OldValue] = ctl.OldValue
                            ![NewValue] = ctl.value
                            .Update
                        End With
                    End If
                End If
            Next ctl
        Case Else
            With rsT
                .AddNew
                ![DateTime] = datTimeCheck
                ![Login] = strUserID
                ![FormName] = Screen.ActiveForm.Name
                ![Action] = UserAction
                ![Asset_ID] = Screen.ActiveForm.Controls(IDField).value
                .Update
            End With
    End Select
    
AuditChanges_Exit:
    On Error Resume Next
    rsT.Close
    Set rsT = Nothing
    Exit Sub
    
AuditChanges_Err:
    MsgBox Err.Description, vbCritical, "ERROR!"
    Resume AuditChanges_Exit
End Sub

You call it in the BeforeUpdate event like so:

Private Sub Form_BeforeUpdate(Cancel As Integer)
        
    'Track all changes made to the record
        If Me.NewRecord Then
            Call AuditChanges("Asset_ID", "NEW")
        Else
            Call AuditChanges("Asset_ID", "EDIT")
        End If

End Sub

 

[MarkK]

Maybe you don't see the errors because of your "on error resume next" statement. Doesn't that just suppress all errors?

The reference to Screen.ActiveForm will never return a subform, only a top level form. To fix this, it may work to pass a form reference to AuditChanges, like . . .

Sub AuditChanges(ActiveForm As Access.Form, IDField as string, Action As String)

. . . and then inside the routine, change your Screen.ActiveForm references to use the parameter "ActiveForm" instead.

Then call the routine like . . .

Private Sub Form_BeforeUpdate(Cancel As Integer)
[COLOR="Green"]    'Track all changes made to the record[/COLOR]
    dim tmp as string
    tmp = "EDIT"
    If Me.NewRecord Then tmp = "NEW"
    AuditChanges Me, "Asset_ID", tmp
End Sub

 

[irish634]

Is your form in the datasheet view set to a SQL query as a record source?

I don't use that particular audit trail, but mine works in any view from the form's before_update event.