Audit Trail after validation - With Resolution

[yellowfin7]

Good Afternoon,

I am currently using Win7 ent. and Access 2007.

what I am trying to do is call an audit trail module that I came across on the Before Update action.

Currently I have validation that will occur if the user chooses to save their changes, the function that I am trying to add the ability to save an audit trail of changes if the user chooses to save their changes.


The error occurs when "Call AUDIT_TRAIL" is reached. At that point I receive an invalid use of property error. Below you will find the audit trail module

Code: Select all

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 = "bran"
    
    'If new record, record it in audit trail and exit function.
    If MyForm.NewRecord = True Then
        MyForm!AUDIT_TRAIL = 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!AUDIT_TRAIL = 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

[HansV]

In the On Click event procedure, change:
DoCmd.CancelEvent to Cancel = True (both occurrences)
Exit Function to Exit Sub
End Function to End Sub

Insert the following below both occurrences of Cancel = True:

Exit Sub

Does that help?

[yellowfin7]

That seems to have helped, now revealing another issue.

I am now experiencing an issue where it is unable to find my audit trail text box. It is sitting deep within

>Main Form
>tab
>subform
[text box]

the parent child relationships are correct. but I am not sure that the audit trail code is referring to the text box field correctly.

I'm not sure that

Code: Select all

 MyForm = Screen.ActiveForm

is correct.


Am I making any sense?

[HansV]

The AuditTrail function only works on the main form, not on subforms. You can solve this as follows:

1) Change the function so that you can pass the form as argument:

Code: Select all

Public Function AUDIT_TRAIL(MyForm As Form)
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 ctl As Control
    Dim sUser As String
'    sUser = "User: " & UsersID 'You need to identify your users if you are not using Access security with workgroups.
    sUser = "bran"
    
    'If new record, record it in audit trail and exit function.
    If MyForm.NewRecord = True Then
        MyForm!AUDIT_TRAIL = 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!AUDIT_TRAIL = 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

2. Wherever you call the function from a form, do it like this:

Code: Select all

    Call AUDIT_TRAIL(Me)

In the code behind a form, Me refers to the form, regardless of whether it's a main form or a subform.

[yellowfin7]

When passing the form as argument should I replace "MyForm" with the acutual name of the main form, the sub form, or ME. I'm getting a 424 - object required with

Code: Select all

subform!tbAuditTrail

where subform is the name of my subform

[HansV]

Use the code exactly as in my previous reply: the first line of AUDIT_TRAIL should be:

Code: Select all

Public Function AUDIT_TRAIL(MyForm As Form)

and the lines that call AUDIT_TRAIL should be

Code: Select all

    Call AUDIT_TRAIL(Me)

Don't substitute anything.

[yellowfin7]

Ok, access seems to like everything, though now that it runs through the code just fine it is not writing the audit trail to the

Code: Select all

AUDIT_TRAIL[\code] field

[HansV]

I don't know where you got the code from, but the original version at How to create an audit trail of record changes in a form in Access 2000 uses Updates as control name. You use AUDIT_TRAIL, tbAuditTrail and AuditTrail. That is not consistent. You should create one memo field in the table, one text box on the form bound to the memo field with the same name as the field, and use that name in the code.

Let's say that you name the field in the table and the text box on the form: AuditTrail. The code for the module with the function then becomes:

Code: Select all

Private Declare Function WNetGetUserA Lib "mpr.dll" _
    (ByVal lpszLocalName As String, ByVal lpszUserName As String, lpcchBuffer As Long) As Long

Private Function GetUser() As String
    Dim lpUserName As String, lpnLength As Long, lResult As Long
    'Create a buffer
    lpUserName = String(256, Chr$(0))
    'Get the network user
    lResult = WNetGetUserA(vbNullString, lpUserName, 256)
    If lResult = 0 Then
        GetUser = Left$(lpUserName, InStr(1, lpUserName, Chr$(0)) - 1)
    Else
        GetUser = "-unknown-"
    End If
End Function

Public Function AUDIT_TRAIL(MyForm As Form)
    'ACC2000: How to Create an Audit Trail of Record Changes in a Form
    'http://support.microsoft.com/kb/197592

    Dim ctl As Control
    Dim sUser As String
    On Error GoTo Err_Audit_Trail

    sUser = GetUser

    'If new record, record it in audit trail and exit function.
    If MyForm.NewRecord = True Then
        MyForm!AuditTrail = MyForm!AuditTrail & "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!AuditTrail & 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 = "AuditTrail" Then GoTo TryNextControl 'Skip AuditTrail field.
            'If new and old value do not equal
            If ctl.Value <> ctl.OldValue Then
                MyForm!AuditTrail = MyForm!AuditTrail & 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!AuditTrail & 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!AuditTrail & 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

Remark: the function now mentions the Windows login name of the user instead of the fixed name "bran".

I've tested it - this version works.

[yellowfin7]

A true maestro of code, you are correct. I had missed updating some of the fields on the version of the code that I posted.

Truely greatful for the user id grab, this will surely make my life easier when implementing the auditing function.

A peer suggested that it would be more efficient to follow the call audit trail with a couple of queries that append the audit information to a seperate audit table with each respective primary key.

Is it more efficient to hard code the audit data to write directly to the table or run the querys to append the data to the table?

[HansV]

I don't think efficiency is really important here - when users saves a record, they won't notice whether it takes 0.1 seconds more or less.
You should take a decision based on what works best for you: audit trail within each record of each table, or one giant table containing the audit trail for all records in all tables.

[yellowfin7]

Thank you Hans, your help is always appreciated!

I think I will append the audit data to a Audit Table and clear the [AuditTrail] field when doing so as it will be more convenient when reviewing the audit data for multiple sub forms.