Audit Trail not working in subform...

[hrprabhu]

Hi All,

I found this brilliant sample database posted on this fourm. It is working fine as for as the main form is concerned.

Does not do the updates in the subform. How do I do it?

In this case if I make any changes in fEmployees it is working. If I make change in fEmpPartner, it is putting a string in the main table "Changes made on 7/05/2011 1:21:53 PM by Admin;" Nothing more.

Thanks...

Raghu

[ADezii]

I think the problem lies in Code Line #5 whereas even if the Sub-Form has the Focus, ActiveForm will still refer to the Main Form. That being said, in Code Line #20, only Controls in the Main Form will be processed in the For...Each Loop.

Dim MyForm As Form
Dim ctl As Control
Dim sUser As String
 
Set MyForm = Screen.ActiveForm
 
'sUser = "User: " & UsersID '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
      MsgBox ctl.Name & " | " & ctl.OldValue & " | " & ctl.Value
       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
'Code intentionally omitted

[hrprabhu]

Thanks.

will try to work around it and see if I can come up with some thing.

Raghu

[hrprabhu]

Found the solution. All I did was to make MyForm an input parameter for the sub "Audit_Trail" making it "Audit_Trail(MyForm As Form)"

Then in the code module of each form I changed the "Call Audit_Trail" to "Audit_Trail Me"

What is happening is that when the Form_BeforeUpdate event fires the reference to the form is passed to the Audit_Trail routine by using the reserved word "Me".

[mrbenner]

Where did you find your sample database on this site?