[AccessD] Audit Trail Suggestions

[Stuart McLachlan]

I do something similar if it's an Access BE.  Here's one I wrote many years ago that is still in 
use:

In one Form's Before_Update:
...
For Each ctl In Me.Controls
    strCtrlType = Left$(ctl.Name, 3)
    If InStr("txt:cbo", strCtrlType) > 0 Then
        If Nz(ctl.Value) <> Nz(ctl.OldValue) Then
            Audit "Elec", ElectorKey, "Chng: " & Mid$(ctl.Name, 4) & "- ''" & Nz(ctl.OldValue) & "'' 
to ''" & Nz(ctl.Value) & "''"
        End If
    End If
Next
...

Other forms whihc are used for maintaining other data use difference parameters in place of 
"Elec" and ElectorKey such as "Loc" and LocationKey etc

And the function Audit:

Function Audit(object As String, id As Long, occurrence As String)
Dim strSQL As String
strSQL = "insert into tblAudit (user,object,objid,Action) values('" & username & "','" & object & 
"'," & id & ",'" & occurrence & "')"
CurrentDb.Execute strSQL
End Function

which also uses the function userName:

Declare Function GetUserName Lib "advapi32.dll" Alias "GetUserNameA" (ByVal lpBuffer As 
String, nSize As Long) As Long

Function username()
Dim uname As String * 127
Dim Response As Long
Response = GetUserName(uname, 32)
If Len(uname) > 1 Then
    username = Left$(uname, InStr(uname, Chr$(0)) - 1)
Else
    username = "No logged In User"
End If
End Function





On 13 Mar 2015 at 22:25, Rocky Smolin wrote:

> In an app I just created for a company doing a DNA mutation assay in
> which every field in every form that's changed needs to be recorded in
> an audit trail table, the following sub is called in the after update
> event of each bound field.