I am having an odd issue with an audit trail I hope someone can help me with.
I am using the module below that I got off this website. It seems to work perfectly fine with my forms that have subforms, but on a normal form the audit trail is kinda screwy. I can see it on the form, but it does not dump the information into the table it is linked to. When I make changes on the form they are reflected in the table. When I bring the form back up and look at the record I just changed the audit trail reflects the change, but not when viewing the table. It appears that the audit trail is performing its function, but just not placing the information where it should go. I have loaded this on numerous forms, with subforms, and they all drop into the appropriate tables. What have I done??? Please someone help straighten this out. I have checked that the audittrail field is comming from the correct table and it is. I am just puzzled as to why the audit trail information seems to be stored with the form and not in the table?
Thanks,
CB
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 = Environ("UserName") 'get the users login name
sUser = CurrentUser '=Admin if you are not using Access security with user workgroups and permissions
'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.Enabled = True Then
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 & " by " & sUser & "; " & Now
'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 & " by " & sUser & "; " & Now
'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" & " by " & sUser & "; " & Now
End If
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
I am using the module below that I got off this website. It seems to work perfectly fine with my forms that have subforms, but on a normal form the audit trail is kinda screwy. I can see it on the form, but it does not dump the information into the table it is linked to. When I make changes on the form they are reflected in the table. When I bring the form back up and look at the record I just changed the audit trail reflects the change, but not when viewing the table. It appears that the audit trail is performing its function, but just not placing the information where it should go. I have loaded this on numerous forms, with subforms, and they all drop into the appropriate tables. What have I done??? Please someone help straighten this out. I have checked that the audittrail field is comming from the correct table and it is. I am just puzzled as to why the audit trail information seems to be stored with the form and not in the table?
Thanks,
CB
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 = Environ("UserName") 'get the users login name
sUser = CurrentUser '=Admin if you are not using Access security with user workgroups and permissions
'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.Enabled = True Then
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 & " by " & sUser & "; " & Now
'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 & " by " & sUser & "; " & Now
'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" & " by " & sUser & "; " & Now
End If
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
