Audit Log on form with Tab Control

[KevinCB]

Could someone help me figure out how to get this audit log script to work on
a form with a tab control. The script works when using a single form, but
now I'm having difficulty getting it to run with subforms etc.

I would also like to know if it's possible to insert the data into a
seperate audit log table with the same fields.

This is my code:

'---------------------------------------------------------------------------------
' This code is used to create and audit trail of who made changes to
' the record. The code captures the new value and old value of each
' change along with the date, time and user and enters it
' into the Updates field in the table. Original code found on
' Microsoft Knowledge Base.
' Added by: Richard Rensel
' Modified by: Kevin Broomhall
' Date Added: 27 Mar 2000
' Date Modified: 22 Sep 2005
'----------------------------------------------------------------------------------
On Err GoTo TryNextC
Dim MyForm As Form
Dim ctl As Control
Dim strUser As String
Set MyForm = Form_frmVisits
strUser = fOSUserName


' Set date and current user if form has been updated.
MyForm!Updates = MyForm!Updates & Chr(13) & Chr(10) & "Changes made on "
& Now & " by " & strUser & ";"

' If new record, record it in audit trail and exit sub.
If MyForm.NewRecord = True Then
MyForm!Updates = MyForm!Updates & Chr(13) & Chr(10) & "New Record in
" & Form.Caption & " on " & MyForm!Emp_Name & ";"
Exit Sub
End If

' Check each data entry control for change and record old value of 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 = "Updates" Then GoTo TryNextC ' Skip Updates field.

If ctl.Value <> ctl.OldValue Then
MyForm!Updates = MyForm!Updates & Chr(13) & Chr(10) & "
" & ctl.Name & ": Changed from: " & ctl.OldValue & " to: " & ctl.Value
End If
End Select
TryNextC:
Next ctl

' Add user and time updated to Organisation form
If Not Me.NewRecord Then
Form_frmWork_Placement!Last_Updated_By.Value = fOSUserName()
End If
If Not Me.NewRecord Then
Form_frmWork_Placement!Date_Updated.Value = Date
End If

 

[Tim Ferguson]

Could someone help me figure out how to get this audit log script to
work on a form with a tab control. The script works when using a
single form, but now I'm having difficulty getting it to run with
subforms etc.

I don't see the problem. Presumably you'll need to iterate the Controls
collection of each Page (or other container) of the tab control.

It's a pretty naff routine though. If this represents the quality of
coding that goes into Office apps themselves, then it's no wonder they
need so many service packs...

If ctl.Name = "Updates" Then GoTo TryNextC ' Skip Updates field.

GoTo? In 2005? It's over ten years since I used a line like that.
The rest of the world has moved onto something like

If ctl Is Me!Updates Then
' do nothing

ElseIf next condition...

If ctl.Value <> ctl.OldValue Then

What about checking that the control has a valid ControlSource in the
first place? Nasty bugs in the future for this line.

I can't be bothered to go on reading this stuff, it's like a bad dream.

I would also like to know if it's possible to insert the data into a
seperate audit log table with the same fields.

Just like any other database update:

INSERT INTO MyLogTable (
DateUpdated, CurrentUser, TableName, PK, UpdateType, Description
)
VALUES (
#2005-10-12#,
'Tim',
'EmployeeSpouses',
'11098',
'Update',
'=1002, SpouseName=Jill, SpouseDOB=#1967-09-16#'
)


You could do this with a parameter query, or making up the command inline
inside your VBA routine, or even using a dynaset.

Hope that helps


Tim F