Call AuditTrail(Me) problem

Posted on 2005-02-25
Medium Priority
Last Modified: 2010-05-18
Below is the working module for my AuditTrail for forms, Subforms and subsubforms. I wish to modify it such that it can be called from any of the forms "Before Update Event".

At the moment the code that I am using below at the Before Update Event to call AuditTrail is not working

Call AuditTrail(Me)

'  *********************

Here is the complete code:

Option Compare Database

     Option Explicit

     Function AuditTrail(pSubformName, pLevel, Optional psub1)
     On Error GoTo Err_Handler

    Dim MyForm As Form, C As Control, xName As String
    Select Case pLevel
    Case 1: Set MyForm = Screen.ActiveForm
    Case 2: Set MyForm = Screen.ActiveForm(pSubformName).Form
    Case 3: Set MyForm = Screen.ActiveForm(psub1).Form(pSubformName).Form
    End Select

    'Set date and current user if form has been updated.
    MyForm!Updates = MyForm!Updates & vbCrLf & _
    Date & " " & CurrentUser() & ";"

    'If new record, record it in audit trail and exit sub.
    If MyForm.NewRecord = True Then
        MyForm!Updates = MyForm!Updates & vbCrLf & _
        "New Record """
    End If

    'Check each data entry control for change and record
    'old value of Control.
    For Each C In MyForm.Controls

    'Only check data entry type controls.
    Select Case C.ControlType
        Case acTextBox, acComboBox, acListBox, acOptionGroup
            ' Skip Updates field.
            If C.Name <> "Updates" Then
            ' If Control was previouslly filled and now is blank
            If Not IsNull(C.OldValue) And IsNull(C.Value) Then
            MyForm!Updates = MyForm!Updates & vbCrLf & _
            C.Name & " " & C.OldValue _
            & " was Deleted"

            ' If control was previously Null, now has a record
            ElseIf IsNull(C.OldValue) And Not IsNull(C.Value) Then
            MyForm!Updates = MyForm!Updates & vbCrLf & _
            C.Name & " " & C.Value _
            & " was Added"

            ' If control was previously Null, record "previous
            ' value was blank."
                ElseIf IsNull(C.OldValue) And C.OldValue = "" Then
                    MyForm!Updates = MyForm!Updates & vbCrLf & _
                    C.Name & "-previous value was blank"

            ' If control had previous value, record previous value.
                ElseIf C.Value <> C.OldValue Then
                    MyForm!Updates = MyForm!Updates & vbCrLf & _
                    C.Name & " Changed from " & C.OldValue & " to " & C.Value
                End If
              End If
             End Select
          Next C

         Exit Function

        If Err.Number <> 64535 Then
        MsgBox "Error #: " & Err.Number & vbCrLf & "Description: " & Err.Description
        End If
       'Resume TryNextC
       End Function

Question by:billcute
  • 4
  • 3
  • 2
  • +1
LVL 51

Expert Comment

by:Steve Bink
ID: 13406902
What is it doing or not doing?  What is it supposed to do?  Any error messages?  We need more information.
LVL 13

Assisted Solution

Lucas earned 200 total points
ID: 13407043
Try this one:

Call AuditTrail (Me.Name)

And one more thing you should declare variable types as well when creating a function.

Function AuditTrail(pSubformName as String, pLevel as Integer, Optional psub1 as string)

Just a suggestion :-)
LVL 51

Expert Comment

by:Steve Bink
ID: 13407088
Adding to lucas911, one of those parameters should probably be declared as a form object, since that is what you appear to be passing.

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

LVL 18

Assisted Solution

Billystyx earned 1800 total points
ID: 13408059
call AuditTrail(fname)


Author Comment

ID: 13410070
To All,
All recommendations seems to make sense but I am confused at configuring the suggested code in the Module.

What I am looking for is a general name I can use in the "Before Update Event" of "forms", "subform" and "subsubform" without having to sweat or struggle with names for forms /subform/subsubform.

I will appreciate it if you could post the complete function codes here based on your suggestions above otherwise I wouldn't understand what you were trying to say here.

Thanks for your contributions
LVL 51

Expert Comment

by:Steve Bink
ID: 13419742
>>> What I am looking for is a general name I can use in the "Before Update Event" of "forms", "subform" and "subsubform"

You want a general name to call a function from anywhere?

>>> Function AuditTrail(pSubformName, pLevel, Optional psub1)
As long as this function is PUBLIC, you can call it from anywhere by that name.  I do not understand where the confusion lies.  Perhaps if you gave a VERY detailed explanation of what you want, including an example of how you would like it to work, we can provide more information.

Author Comment

ID: 13428349
To All,
This sample illustrates one way to produce a simple audit trail table containing: login name,  record number, field name, original value, new value, and a date/time stamp. It tracks all changes made to a record in either the form or subform.

It also tracks when new records are inserted and when records are deleted and the values of the deleted fields.
To use it, the sample code given above is trored in a Module, then add a Memo field (AuditTrail) into a table and add this Memo field to your main form.

---> The Module above is to enable mee use the AuditTrail in multiple tables in the same DB with forms, sunforms and

Then call the AuditTrail in the "Before Update Events" For now, I have tried all your suggested codes above and I cant seem to get anywhere.

I am getting "Compile Error: Invalid Use of property"

LVL 18

Accepted Solution

Billystyx earned 1800 total points
ID: 13433487
There doesn't seem to be any need for the first select case, which utilises all the parameters passed to the function, if you call it from the before update event of each form and subform. So, as lucas suggested, use Me.Form as the parameter for calling, and change your initial select case to simply
set MyForm=pForm, and remove all but the form parameter from the function declaration.

Private Sub Form_BeforeUpdate(Cancel As Integer)
Call Audit_Trail(Me.Form)
End Sub

Public Function Audit_Trail(pForm As Form)

    Set MyForm = pForm

Also, this function or any other similiar function relies on the audit field being on the form itself - in this case - its is called Updates, and must be part of the table that is the recordsource of that form.

If you want one audit table for all forms and subforms, then you
will need to change the way it fills in the data, perhaps with dao, like:
strUpdate=MyForm!Updates & vbCrLf & _
                    C.Name & " Changed from " & C.OldValue & " to " & C.Value

dim rs as dao.recordset
set rs=currentdb.openrecordset("tblAudit",dbOpenDynaset)
rs!AuditTrail= strUpdate

Let me know what you think




Author Comment

ID: 13434963
The explanation is excellent. The additional codes for any future updates is commendable. Thanks

Author Comment

ID: 13482868
To all,
I have just placed a new post on this topic as I will like to explore it further.

Here is the link:

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

569 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question