Help with code (1 Viewer)

I have this code in a module for an audit trail, it is working fine for controls on my main forms but I cant get it to work for subforms on my main forms.

I have added the code to the subforms exactly the same way as I have to the main forms. If you open the sub form it works fine but when you make changes when the sub form is opened on the main form it doesnt record these changes.

Can anyone help? heres the code, I think it has something to do with the Set MyForm bit but not sure

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: " & MyForm.UserName 'You need to identify your users if you are not using Access security with workgroups.
' sUser = CurrentUser

'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.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

Try this instead


It now means that you have to call the function with Audit_Trail(Form)

Does this help? (I have not road tested this code though)

Thanks for the reply Fizzio

This is the code that I have used for the form, it was working before but since I have added (frmLicencesProcured) to the end of Call Audit_Trail it is giving me the error Ambiguous name detected. Have I done what you asked me to do Fizzio???

Private Sub Form_BeforeUpdate(Cancel As Integer)

On Error GoTo Form_BeforeUpdate_Err

Call Audit_Trail(frmLicencesProcured)

Form_BeforeUpdate_Exit:
Exit Sub

Form_BeforeUpdate_Err:
MsgBox Err.Number & " - " & Err.Description
Resume Form_BeforeUpdate_Exit

End Sub

This line

Call Audit_Trail(frmLicencesProcured) should be

Call Audit_Trail(Form) or Call Audit_Trail(Me) (I'm not sure which will work best)

Thanks!!!!!

Your a genious

I have tried this but it is still not working

It says cannot find tbaudittrail

please help

i have atached the db