Audit trail For three forms to the main frm (1 Viewer)

[Falcon88]

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:

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

i call this code by : Form_BeforeUpdate event as :

Call Audit_Trail(Me)

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?

 

[static]

The code is in frmA which is the Parent of B and C, so, in B and C you would write

Parent.Audit_Trail Me

You only need to use Call if you use the function with brackets

Call Parent.Audit_Trail(Me)

 

[Falcon88]

The code is in frmA which is the Parent of B and C, so, in B and C you would write

Parent.Audit_Trail Me

You only need to use Call if you use the function with brackets

Call Parent.Audit_Trail(Me)

Ok Very thanks ,
i try that but gives error 2465

 

[June7]

The procedure is set up to reference a form object. Subforms are not opened as form objects, they are a container control on a form, therefore I don't think your function can be called by the subforms.

 

[Falcon88]

The procedure is set up to reference a form object. Subforms are not opened as form objects, they are a container control on a form, therefore I don't think your function can be called by the subforms.

Very thanks

Is there a method to change code to work with Main Forms and Sub Forms?

 

[static]

A public function is available to any other procedure and a child form has access to its container via parent.

What line do you get an error on?

 

[static]

MyForm!AuditTrail

If that is on frmA it should reference frmA not MyForm

 

[Falcon88]

A public function is available to any other procedure and a child form has access to its container via parent.

What line do you get an error on?

it gives error not in that function
the error line is here:

Private Sub Form_BeforeUpdate(Cancel As Integer)
[B] Parent.Audit_Trail Me     [/B]
End Sub

 

[Falcon88]

MyForm!AuditTrail

If that is on frmA it should reference frmA not MyForm

i some times need to use that code in Forms (that not contains Subforms ) , and if i have a form with sub forms i need to returns changes in the main form for all the main form and the subforms .

 

[static]

can you post a sample without data?

 

[Falcon88]

can you post a sample without data?

this is like i want , try to change code in subform to
Parent.Audit_Trail Me

 

[Falcon88]

if bossible i want the code to return:
(if the form contains subforms)

The name of the form in which the changes or additions happend.

 

[static]

Can't look at this now but you are using memo fields which is not good.
Audit messages should all be in one table i.e. tAudit with a record for each change rather than appending to a memo field.

Also, Audit_Trail() is not in either form, it's in a standard module, so you don't need to use Parent.

 

[jdraw]

Falcon88,

I recently posted a revised Audit Trail sample database in response to another poster on this forum.
It deals with Forms and subforms. It is written using MSAccess 2010.
Not sure if it is relevant to your situation.
You can review the sample database and log.
Good luck.

 

[Falcon88]

Falcon88,

I recently posted a revised Audit Trail sample database in response to another poster on this forum.
It deals with Forms and subforms. It is written using MSAccess 2010.
Not sure if it is relevant to your situation.
You can review the sample database and log.
Good luck.

thanks

i try that but gives error (Db connection) i use Access 2007.

if possible to give some changes in my example .

 

[Falcon88]

Can't look at this now but you are using memo fields which is not good.
Audit messages should all be in one table i.e. tAudit with a record for each change rather than appending to a memo field.

Also, Audit_Trail() is not in either form, it's in a standard module, so you don't need to use Parent.

Thank you so much
I want to make it easier for the user to observe changes .

 

[jdraw]

i try that

What exactly did you try?
Did you run the sample application?

 

[Falcon88]

i try
https://www.access-programmers.co.uk/forums/showpost.php?p=1530693&postcount=43

your Example..

sorry a refernce error.

i add
ms activex data object 2.8 library .

and works good.

 

[Falcon88]

Falcon88,

I recently posted a revised Audit Trail sample database in response to another poster on this forum.
It deals with Forms and subforms. It is written using MSAccess 2010.
Not sure if it is relevant to your situation.
You can review the sample database and log.
Good luck.

Dear jdraw your Example is a good idea.
but if user needs to show his changes dates for the current record in main form or in sub form ??

 

[jdraw]

??? There is a word document describing the sample application and audit trail.

What exactly is the issue?