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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

lucas911 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.
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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

How Blockchain Is Impacting Every Industry

Blockchain expert Alex Tapscott talks to Acronis VP Frank Jablonski about this revolutionary technology and how it's making inroads into other industries and facets of everyday life.

Question has a verified solution.

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

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Suggested Courses

752 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