Solved

Audit trail for subforms

Posted on 2003-11-05
8
1,730 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
ID: 9690416
kipper could you paste your code here

thanks

syntnx
0
 
LVL 2

Expert Comment

by:pilby
ID: 9691252
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
ID: 9693609
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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

Author Comment

by:Kipper_L
ID: 9696450
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
 
LVL 2

Accepted Solution

by:
pilby earned 125 total points
ID: 9696879
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
ID: 9696892
oh wait, i guess you want that in the BeforeUpdate (like the MS page says), not in AfterUpdate.
0
 

Author Comment

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

Expert Comment

by:psk1
ID: 13988029
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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
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.

685 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