Solved

Audit trail for subforms

Posted on 2003-11-05
8
1,652 Views
Last Modified: 2011-01-12
I have created an audit trail module to capture details of changed fields. This is based on the Microsoft  example (197592) which enumerates through all the controls on the active screen (Screen.ActiveForm). This works fine for changes to the main form but doesn't work for changes to a subform even though the before update event on the subform runs the module. The Screen.ActiveForm does not seem to recognise the subform. Can anyone tell me how to cycle through the controls on the subform or provide an audit trail example which works for subforms.
Thanks
0
Comment
Question by:Kipper_L
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 3

Expert Comment

by:syntnx
Comment Utility
kipper could you paste your code here

thanks

syntnx
0
 
LVL 2

Expert Comment

by:pilby
Comment Utility
well, if the code is being run from the subform, rather than using Screen.ActiveForm, just use "Me".  that will refer to the subform.  then you can refer to the fields in that subform: Me.control1, etc or:

With Me.
   tempVar = control1

...

etc.

if you want it to refer to controls on a form that maybe isn't the active form, just refer to the form name explicitly:
Forms!FormName.ControlName

weston
0
 
LVL 3

Expert Comment

by:syntnx
Comment Utility
Kipper/weston,

I did play with the code a bit last night from the ms kba. I had the same thought as weston but still was getting an error. Give it try and let us know. Im curous myself because I think the code could be very useful.

I personally would like to take it to the next step. Maybe comapre before and after values and only write the actual changes. Ive done audit/transactions records before but in such an elegant way.

Ill try a few other things today (have to do some data imports first)

syntnx
0
 

Author Comment

by:Kipper_L
Comment Utility
Weston,

I've never used  

With me

but will give it a try and let you know if you can then cycle through all the controls. I don't want to reference the controls by name as I want to use one peice of code for all my forms (and subforms).

Thanks
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 2

Accepted Solution

by:
pilby earned 125 total points
Comment Utility
i guess this is the code you're using......  i got it from http://support.microsoft.com/?kbid=197592

make a few changes, though:


___

Function AuditTrail(MyForm As Form)
On Error GoTo Err_Handler
   
    Dim C As Control, xName As String

    'Set date and current user if form has been updated.
    MyForm!Updates = MyForm!Updates & Chr(13) & Chr(10) & _
    "Changes made on " & Date & " by " & CurrentUser() & ";"

    'If new record, record it in audit trail and exit sub.
    If MyForm.NewRecord = True Then
        MyForm!Updates = MyForm!Updates & Chr(13) & Chr(10) & _
        "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 previously Null, record "previous
            ' value was blank."
                If IsNull(C.OldValue) Or C.OldValue = "" Then
                    MyForm!Updates = MyForm!Updates & Chr(13) & _
                    Chr(10) & C.Name & "--previous value was blank"
                   
                ' If control had previous value, record previous value.
                ElseIf C.Value <> C.OldValue Then
                    MyForm!Updates = MyForm!Updates & Chr(13) & Chr(10) & _
                    C.Name & "==previous value was " & C.OldValue
                End If
            End If
        End Select
    Next C

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

basically, just pass MyForm as a variable instead of having it assigned as the active form.  that way, the code that calls this function can tell it what form to deal with.

so then, on the subform, in AfterUpdate, put:

AuditTrail Me

that way, whatever form (or subform) that calls that function will pass itself as the form to audit (which is what you want).  i didn't look at the rest of the code too close, but you'll need a field called Updates in every form where you're using this (if you don't have it already).

the first advice i gave was based on skimpy information.  after looking up the code, i realized what the real problem is.

the reason you can't use the advice i first gave (using "me" instead of Screen.ActiveForm) is that you call AuditTrail from the form, but AuditTrail itself doesn't know what "Me" is.  that's why you have to tell it (like i just showed you).

let me know if you need more.

weston
0
 
LVL 2

Expert Comment

by:pilby
Comment Utility
oh wait, i guess you want that in the BeforeUpdate (like the MS page says), not in AfterUpdate.
0
 

Author Comment

by:Kipper_L
Comment Utility
Thanks for your help - it works fine now for any form.
0
 
LVL 2

Expert Comment

by:psk1
Comment Utility
Thanks guys.  This helped me out.

Just for future viewers, here's the code I'm using (based on above modified MS code):


Function didChange(v1, v2) As Boolean

    didChange = False
    If IsNull(v1) And Not IsNull(v2) Then didChange = True
    If Not IsNull(v1) And IsNull(v2) Then didChange = True
    If didChange = True Then Exit Function
    If v1 <> v2 Then didChange = True

End Function

Function recordAuditTrail(frm As Form)
   
    Dim c As Control, xName As String
    Dim sSql1 As String, sSql2 As String
    Dim sSql3 As String, sSql4 As String
   
    If frm.Name = "" Then Exit Function
   
    sSql1 = "INSERT INTO [Reference_Audit_Trail] ( [chgType], [chgDate], [chgUser], [Row]"
    sSql2 = "SELECT 'OLD', #" & Now() & "#, '" & Environ("username") & "', " & frm.Controls.Item("Row").Value
   
    sSql3 = "INSERT INTO [Reference_Audit_Trail] ( [chgType], [chgDate], [chgUser], [Row]"
    sSql4 = "SELECT 'NEW', #" & Now() & "#, '" & Environ("username") & "', " & frm.Controls.Item("Row").Value
   
    For Each c In frm.Controls
      Select Case c.ControlType
      Case acTextBox, acComboBox, acListBox, acOptionGroup
        If LCase(Left(c.Name, 7)) <> "comment" And c.ControlSource <> "Row" Then 'fields to exclude from verification
          If didChange(c.Value, c.OldValue) Then
            sSql1 = sSql1 & ", [" & c.ControlSource & "]"
            sSql2 = sSql2 & ", '" & c.OldValue & "'"
            sSql3 = sSql3 & ", [" & c.ControlSource & "]"
            sSql4 = sSql4 & ", '" & c.Value & "'"
          End If
        End If
      End Select
    Next c
   
    DoCmd.SetWarnings False
    DoCmd.RunSQL sSql1 & " ) " & sSql2 & ";"
    DoCmd.RunSQL sSql3 & " ) " & sSql4 & ";"
    DoCmd.SetWarnings True
   
End Function


I had wanted to store the audit trail in a separate table, not in a field in the form on a by-record basis.  I copied the structure of the table on which my form was based (so that the field names could be referred to by the controlsource of each control in the form) and added the fields chgType(text), chgUser(text), chgDate(date) to create the new Audit table.  [Row] is the unique record identifier in the source table (it was Autonumber so I had to make it Number in the new Audit table).

The code is stored in a module and called from the BeforeUpdate event of the form.

Hope it's helpful to someone.
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

771 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now