I got the following code from Richard Rensel to create an Audit Trail in Access 97. My problem is that my form contains a subform and changes made on the subform are not being recorded. How can I change the code to include the subform?
Thanks Liz
Private Sub Form_BeforeUpdate(Cancel As Integer)
On Err GoTo TryNextC
Dim MyForm As Form
Dim ctl As Control
Dim strUser As String
Set MyForm = Screen.ActiveForm
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 " & CurrentUser() & ";"
' 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 """
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" Or ctl.Name = "text72" Or ctl.Name = "text74" Or ctl.Name = "todaysdate" Or ctl.Name = "Time" 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
End Sub
Thanks Liz
Private Sub Form_BeforeUpdate(Cancel As Integer)
On Err GoTo TryNextC
Dim MyForm As Form
Dim ctl As Control
Dim strUser As String
Set MyForm = Screen.ActiveForm
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 " & CurrentUser() & ";"
' 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 """
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" Or ctl.Name = "text72" Or ctl.Name = "text74" Or ctl.Name = "todaysdate" Or ctl.Name = "Time" 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
End Sub
Code: