Solved

Audit trail for subforms

Posted on 2003-11-05
8
1,720 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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 

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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
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…
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.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

860 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