hiii all dears
I have three forms ,
- FrmA his recordsource is tbl1
- FrmB his recordsource is tbl2
- FrmC his recordsource is tbl2
FrmA is the Main Form
FrmB is sub form inside FrmA
FrmC is sub form inside FrmB
and i have this code to return Changes in the forms:
i call this code by : Form_BeforeUpdate event as :
i want this code to return the changes in the all three forms to the tbAuditTrail field that is in the Main Form(FrmA). ?
how to call this code in FrmB sub Form and FrmC sub sub form?
I have three forms ,
- FrmA his recordsource is tbl1
- FrmB his recordsource is tbl2
- FrmC his recordsource is tbl2
FrmA is the Main Form
FrmB is sub form inside FrmA
FrmC is sub form inside FrmB
and i have this code to return Changes in the forms:
Code:
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 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 " & vbCrLf & Now & vbCrLf & " 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 " & vbCrLf & Now & vbCrLf & " 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 i want to use the lables names use: ctl.Controls(0).Caption instead of ctl.name
If ctl.Value <> ctl.OldValue Then
MyForm!AuditTrail = MyForm!tbAuditTrail & vbCrLf & ctl.Name & vbCrLf & ": Changed From: " & vbCrLf & ctl.OldValue & vbCrLf & " 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" & vbCrLf & "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 & vbCrLf & ": Changed From: " & vbCrLf & ctl.OldValue & vbCrLf & " 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
Code:
Call Audit_Trail(Me)
how to call this code in FrmB sub Form and FrmC sub sub form?