Audit trail

[Chimp8471]

i dare say that this is a topic that has been tackled 100's of times before, and although spending hours sifting through the search files i really am none the wiser.

i have inserted an Audit trail into my current database to try and track who is playing with the figures

the way i have done it is to copy the following code into a Module:


Option Compare Database
Option Explicit

Public Function Audit_Trail()
On Error GoTo Err_Audit_Trail

'ACC2000: How to Create an Audit Trail of Record Changes in a Form
'http://support.microsoft.com/default.aspx?scid=kb;en-us;197592

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

'If new record, record it in audit trail and exit function.
If MyForm.NewRecord = True Then
MyForm!AuditTrail = MyForm!tbAuditTrail & "New Record added on " & Now & " by " & sUser & ";"
Exit Function
End If

'Set date and current user if the form (current record) has been modified.
MyForm!AuditTrail = MyForm!tbAuditTrail & vbCrLf & vbLf & "Changes made on " & Now & " by " & sUser & ";"

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

'Only check data entry type controls.
Select Case ctl.ControlType
Case acTextBox, acComboBox, acListBox, acOptionGroup, acCheckBox
If ctl.Name = "tbAuditTrail" Then GoTo TryNextControl 'Skip AuditTrail field.
'If new and old value do not equal
If ctl.Value <> ctl.OldValue Then
MyForm!AuditTrail = MyForm!tbAuditTrail & vbCrLf & ctl.Name & ": Changed From: " & ctl.OldValue & ", To: " & ctl.Value
'If old value is Null and new value is not Null
ElseIf IsNull(ctl.OldValue) And Len(ctl.Value) > 0 Or ctl.OldValue = "" And Len(ctl.Value) > 0 Then
MyForm!AuditTrail = MyForm!tbAuditTrail & vbCrLf & ctl.Name & ": Was Previoulsy Null, New Value: " & ctl.Value
'If new value is Null and old value is not Null
ElseIf IsNull(ctl.Value) And Len(ctl.OldValue) > 0 Or ctl.Value = "" And Len(ctl.OldValue) > 0 Then
MyForm!AuditTrail = MyForm!tbAuditTrail & vbCrLf & ctl.Name & ": Changed From: " & ctl.OldValue & ", To: Null"
End If
End Select

TryNextControl:
Next ctl

Exit_Audit_Trail:
Exit Function

Err_Audit_Trail:
If Err.Number = 64535 Then 'Operation is not supported for this type of object.
Exit Function
ElseIf Err.Number = 2475 Then 'You entered an expression that requires a form to be the active window
Beep
MsgBox "A form is required to be the active window!", vbCritical, "Invalid Active Window"
Else
Beep
MsgBox Err.Number & " - " & Err.Description
End If
Resume Exit_Audit_Trail

End Function


this module is then saved as dAuditTrail

my form that i am trying to capture the changes from is called frmDataEntry

and i want the data to be stored in my table called tblProduction

but

the problems i am having are :

the data is being captured in the form (i have created a text box which when double clicked zooms to show changes) however this data is not then transfered to the table where i have a field called Audit trail

i am having one or two other problem but will try to sort this one out first......

Many Thanks

Andy

-----------------------------------------------------------------------------------
please note that the code i have used is taken from this forum....so thanks for the help with that.

 

[Chimp8471]

also meant to mention that the following code is behind on double click function on a text box:

Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo Form_BeforeUpdate_Err

Call Audit_Trail

Form_BeforeUpdate_Exit:
Exit Sub

Form_BeforeUpdate_Err:
MsgBox Err.Number & " - " & Err.Description
Resume Form_BeforeUpdate_Exit

End Sub

Private Sub tbAuditTrail_BeforeUpdate(Cancel As Integer)

End Sub

Private Sub tbAuditTrail_DblClick(Cancel As Integer)
On Error GoTo tbAuditTrail_DblClick_Err

Beep
DoCmd.RunCommand acCmdZoomBox

tbAuditTrail_DblClick_Exit:
Exit Sub

tbAuditTrail_DblClick_Err:
MsgBox Err.Number & " - " & Err.Description
Resume tbAuditTrail_DblClick_Exit

End Sub

hope this helps

 

[Hayley Baxter]

You might be able to use this sample database to help you.

 

[Chimp8471]

hmmmmm......thanks but not 100% that thats what i am after

i need to know when any changes have been made to the system.

Ideally without people knowing that i can check this info

 

[Graham T]

Andy

There are a couple fo different examples available at:

rogersaccesslibrary

HTH

Graham

 

[Chimp8471]

thank you very much....i will have a look.

The problem is though that i struggle when it come to adding it into my database.

Cheers

Andy

 

[ghudson]

Chimp8471,

I recognize my Audit Trail code that you have posted above. Have you bound the Audit Trail text box that displays the Audit Trail info to the memo field in your table. My Audit Trail sample has a text box in the form named "tbAuditTrail" and is bound to the table field named "AuditTrail".

My original Audit Trail sample that Chimp8471 is refering too is located @ AuditTrail.zip

HTH

 

[Chimp8471]

Thanks, i hope you don't mind me using your code, if you do, please just say and i will remove it.

i will in the meantime have a look at the setup of how i have done it in my database.

Cheers

Andy

 

[ghudson]

Chimp8471,

Not at all. I posted the audit trail sample to help anybody in need of adding the audit trail function to their application. I once had to add that function to an application I was designing and I had a difficult time adding Microsofts demo and any of the other demos I found. Glad to help!

HTH