Audit Trail

[Chloe]

I like this code I found, but I would like to put the date
in a seperate field and the Exact user name
not "Admin "or "User" entered in a seperate field and keep
the data changed in the memo field.

Can someone help please.

Function AuditTrail()
On Error GoTo Err_Handler

Dim MyForm As Form, C As Control, xName As String
Set MyForm = Screen.ActiveForm

'Set date and current user if form has been updated.
MyForm!Updates = MyForm!Updates & Chr(13) & Chr(10) & _
"Changes made on " & Date & " by " & CurrentUser()
& ";"

'If new record, record it in audit trail and exit sub.
If MyForm.NewRecord = True Then
MyForm!Updates = MyForm!Updates & Chr(13) & Chr
(10) & _
"New Record """
End If

'Check each data entry control for change and record
'old value of Control.
For Each C In MyForm.Controls

'Only check data entry type controls.
Select Case C.ControlType
Case acTextBox, acComboBox, acListBox,
acOptionGroup
' Skip Updates field.
If C.Name <> "Updates" Then

' If control was previously Null,
record "previous
' value was blank."
If IsNull(C.OldValue) Or C.OldValue = ""
Then
MyForm!Updates = MyForm!Updates & Chr
(13) & _
Chr(10) & C.Name & "--previous value
was blank"

' If control had previous value, record
previous value.
ElseIf C.Value <> C.OldValue Then
MyForm!Updates = MyForm!Updates & Chr
(13) & Chr(10) & _
C.Name & "==previous value was " &
C.OldValue
End If
End If
End Select
Next C

TryNextC:
Exit Function

Err_Handler:
If Err.Number <> 64535 Then
MsgBox "Error #: " & Err.Number & vbCrLf
& "Description: " & Err.Description
End If
Resume TryNextC
End Function

 

[Douglas J. Steele]

Grab the code from http://www.mvps.org/access/api/api0008.htm at "The Access
Web"

Paste that code into a module, and use fOSUserName() wherever you currently
have CurrentUser().

(Make sure you don't name the module fOSUserName)