Audit Trail Problem

[KevCB226]

Got a problem with that audit trail that has been posted on here.

Basically I can get it to work on all put one subform, and I don't know why.

I have a main form with a tab control placed on it containing all of the subforms in my db. On the form it doesn't work on it seems to be inputting into the updates control incorrectly. With all the others it works fine, and displays the changes as follows in the updates control:

Record added on 26/10/05

Changes made on 26/10/05 10:23:55 by johns;
Date: was previously Null, New Value: 20/03/05

But using the subform that is not working, it shows something like:

Changes made on 26/10/05 10:23:55 by johns;Date: was previously Null, New Value: 20/03/05

Can anyone help with this, I'm not sure if it is because it is getting confused between the tab control and the main form.

Anyone any ideas, let me know if you want me to post the code. I can come back and post one that works, and the one that doesn't.

 

[Uncle Gizmo]

>>> Basically I can get it to work on all put one subform, and I don't know why <<<<

You imply that it is working OK on other sub forms. Am I correct in this assumption?

 

[KevCB226]

Yeah, that's right. It was a long day .

The code for one of the working subforms is as follows:

Option Compare Database
Option Explicit

Public Function Audit_Trail_WP_Details(frm 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 = fOSUserName
    
    'If new record, record it in audit trail and exit function.
    If frm.NewRecord = True Then
        Form_frmWork_Placement!Updates = Form_frmWork_Placement!Updates 
& "New Record added on " & Now & " by " & sUser & ";"
        Exit Function
    End If
    
    'Set date and current user if the form (current record) has been 
modified.
    Form_frmWork_Placement!Updates = Form_frmWork_Placement!Updates & 
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 frm.Controls
    
    'Only check data entry type controls.
    Select Case ctl.ControlType
    Case acTextBox, acComboBox, acListBox, acOptionGroup, acCheckBox
        If ctl.Enabled = True Then
        If ctl.Name = "Updates" Then GoTo TryNextControl 'Skip 
AuditTrail field.
            'If new and old value do not equal
            If ctl.Value <> ctl.OldValue Then
                Form_frmWork_Placement!Updates = 
Form_frmWork_Placement!Updates & 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
                Form_frmWork_Placement!Updates = 
Form_frmWork_Placement!Updates & 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
                Form_frmWork_Placement!Updates = 
Form_frmWork_Placement!Updates & vbCrLf & ctl.Name & ": Changed From: " & ctl.OldValue & ", 
To: Null"
            End If
        End If
    End Select
    
TryNextControl:
    Next ctl
    
' Add user and time updated to Organisation form
    If Not frm.NewRecord Then
    Form_frmWork_Placement.Last_Updated_By.Value = fOSUserName()
    End If
    
Exit_Audit_Trail_WP_Details:
    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_WP_Details
    
End Function

And the code for the subform that doesn't work is:

Option Compare Database
Option Explicit

Public Function Audit_Trail_Student(frm 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 = fOSUserName
    
    'If new record, record it in audit trail and exit function.
    If frm.NewRecord = True Then
        Form_frmWork_Placement!Updates = Form_frmWork_Placement!Updates 
& "New Student added on " & Now & " by " & sUser & ";"
        Exit Function
    End If
    
    'Set date and current user if the form (current record) has been 
modified.
    Form_frmWork_Placement!Updates = Form_frmWork_Placement!Updates & 
vbCrLf & vbLf & "Student removed on " & Now & " by " & sUser & ";"
    
    'Check each data entry control for change and record old value of 
the control.
    For Each ctl In frm.Controls
    
    'Only check data entry type controls.
    Select Case ctl.ControlType
    Case acTextBox, acComboBox, acListBox, acOptionGroup, acCheckBox
        If ctl.Enabled = True Then
        If ctl.Name = "Updates" Then GoTo TryNextControl 'Skip 
AuditTrail field.
            'If new and old value do not equal
            If ctl.Value <> ctl.OldValue Then
                Form_frmWork_Placement!Updates = 
Form_frmWork_Placement!Updates & 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
                Form_frmWork_Placement!Updates = 
Form_frmWork_Placement!Updates & 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
                Form_frmWork_Placement!Updates = 
Form_frmWork_Placement!Updates & vbCrLf & ctl.Name & ": Changed From: " & ctl.OldValue & ", 
To: Null"
            End If
        End If
    End Select
    
TryNextControl:
    Next ctl
    
' Add user and time updated to Organisation form
    If Not frm.NewRecord Then
    Form_frmWork_Placement.Last_Updated_By.Value = fOSUserName()
    End If
    
Exit_Audit_Trail_Student:
    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_Student
    
End Function

These are modules, rather than stored within the actual form. It seems to be when I add a new student it comes out like the following:

Changes made on 26/10/05 10:23:55 by johns;New student added on 26/10/05 10:23:55 by johns;

Rather than:

Changes made on 26/10/05 10:23:55 by johns;

New student added on 26/10/05 10:23:55 by johns;

The other subforms seem to work apart from this one, and I'm trying to work out why as the code is pretty much the same. Was hoping any one on here could see a problem. Not sure if it is because of the tab control on the form or something else