Audit Trails

[Crampton]

Ive copied the following script from the Microsoft Articles on how to keep an Audit Trail of changes to records in a form.

It works perfectly however, i dont not want it to record blank values, only changes to a record. At the moment it is recording every field that is blank. I just need a change to a field.

The area of the script I think is the problem is shown below:

'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

Many Thanks for your help

 

[ghudson]

Check out the sample I posted a few moons ago. It does not record Null values.

Audit Trail

HTH

 

[Crampton]

Audit Trails Revised

Thanks for your help GHudson its worked wonders.

A small point though, i use a login form that asks the user to enter username and password. How can i get this username recorded on the audit trail and not the Access Workgroups security username?

Ie- your code stats

Dim MyForm As Form
Dim ctl As Control
Dim sUser As String
Set MyForm = Screen.ActiveForm
' sUser = "User: " & UsersID 'You need to identify your users if you are not using Access security with workgroups.
sUser = CurrentUser

Is there a way to do this??

Many thanks for your help

 

[ghudson]

How are you using the users Login Name in your db? Are you already retrieving it for any other purposes in your db?

If not, I suggest that you use a public function that retrieves the users Login Name with a DLookup function (check the help files if you need examples). Then call that public function in the function within the audit trail.

Say you call your public function LoginName()...

sUser = LoginName

HTH

 

[Crampton]

Thanks for your help GHudson, its working perfectly now.

Thanks again for your help