Results 1 to 6 of 6

Thread: Audit Trail

  1. #1
    Join Date
    Jan 2002
    Location
    At the Edge of America!
    Posts
    55

    Unanswered: Audit Trail

    Ok I need some help for you all out there. I have been doing this for awhile and need to know if someone has a solution for an audit trail on an unbound form.

    I have found many solutions on bound forms but nothing on unbound forms.

    Any help would be apperciated.

    Neadom

  2. #2
    Join Date
    Jun 2002
    Location
    Vienna, VA
    Posts
    53
    I use this in many of my databases - Hope this works for you. -- Leah

    On the form's before update event, put the following function

    =AuditTrail()


    Code follows: modAudit

    Option Compare Database
    Option Explicit

    '-- set form tag to table name.
    '-- set primary key tag to "RecordID"
    '-- set form before update = AuditTrail()
    '-- control name must equal field name for recording purposes

    Function AuditTrail()

    Dim MyForm As Form, C As Control
    Dim strRecord As String
    Dim strField As String
    Dim strOld As String
    Dim strNew As String
    Dim strTable As String

    Set MyForm = Screen.ActiveForm
    strTable = MyForm.Tag

    For Each C In MyForm.Controls


    '-- Record Primary Key
    If C.Tag = "RecordID" Then
    strRecord = C.Value
    End If
    Next C

    For Each C In MyForm.Controls
    '-- Only check data entry type controls.
    Select Case C.ControlType
    Case acTextBox, acComboBox, acListBox, _
    acOptionGroup, acCheckBox
    '-- forget fields we don't want to track any more
    If Not (C.ControlSource = "LChngDate") And _
    Not (C.ControlSource = "KEYER") Then
    '-- If control was previously Null, record "blank"
    If Not (IsNull(C.Value)) Then
    '-- old value is blank
    If IsNull(C.OldValue) Then
    strOld = "Blank"
    strNew = C.Value
    strField = C.Name
    AddAuditEntry strField, strRecord, strOld, strNew, strTable
    '-- If control had previous value, record previous value.
    ElseIf C.Value <> C.OldValue Then
    strOld = C.OldValue
    strNew = C.Value
    strField = C.Name
    AddAuditEntry strField, strRecord, strOld, strNew, strTable
    End If
    Else
    If Not (IsNull(C.OldValue)) Then
    strNew = "Blank"
    strOld = C.OldValue
    strField = C.Name
    AddAuditEntry strField, strRecord, strOld, strNew, strTable
    End If
    End If
    End If
    End Select

    Next C



    End Function


    Sub AddAuditEntry(strFieldname As String, strRecordID As String, strOld As String, _
    strNew As String, strTableName As String)

    Dim dbMine As DAO.Database

    Dim strInsert As String

    Set dbMine = CurrentDb

    strInsert = "INSERT INTO Audit"
    strInsert = strInsert & " (TableName, RecordID, FieldName, OldValue, NewValue, ChangedBy, DateChanged)"
    strInsert = strInsert & " VALUES ('" & strTableName & "','"
    strInsert = strInsert & strRecordID & "','"
    strInsert = strInsert & strFieldname & "','"
    strInsert = strInsert & strOld & "','"
    strInsert = strInsert & strNew & "','"
    strInsert = strInsert & strCurrentUser & "','"
    strInsert = strInsert & Format(Now(), "mm/dd/yyyy") & "')"

    dbMine.Execute (strInsert)


    End Sub

  3. #3
    Join Date
    Jan 2002
    Location
    At the Edge of America!
    Posts
    55
    You do this on Unbound Forms?

  4. #4
    Join Date
    Jun 2002
    Location
    Vienna, VA
    Posts
    53
    I use this everywhere. My audit tables are the same in each app.

    On unbound forms, you're returning the control name as strField. As long as you give your controls meaningful names, it works.

    On bound forms, I use C.ControlSource, which returns the field name itself.


    Leah

  5. #5
    Join Date
    Jan 2002
    Location
    At the Edge of America!
    Posts
    55
    But when I apply it to the before update event on the form there is no update. There is only a button that I push to execute the insert record command on my database.

  6. #6
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    The technique is straight forward:

    Loop through each control bearing data (you can use the Tag property, the Type property of each control, the Name of the control - especially if the names match the data table) and check the value on the screen against the value in the table.

    Here's my method:

    In the Original Table, I have included these 2 fields:
    UserAdded Text
    DateAdded Date/Time (default =Now())

    I use a table that is identical to the original table with 3 extra fields:
    AuditKey AutoNumber
    AuditUser Text
    AuditDate Date/Time (default =Now())

    I open a recordset to the original table. Since my field names in the table match the control names on the form, I simply loop through:
    Code:
    Sub AuditTrail(KeyField As String, OriginalTable As String, AuditTable As String, FormName As String)
    ' Written by todd_sutay@yahoo.com (please keep this line)
    
    ' This module checks the value of each filed against the table to see if it's changed
    ' If it has, the current values are placed into an Audit Trail and the new values placed
    ' in the table.
    
    ' This code requires DAO to be referenced.
    ' This assumes your audit table is IDENTICAL to the original table except for the addition of 3 fileds:
    ' AuditKey   AutoNumber
    ' AuditUser  Text
    ' AuditDate  Date/Time(Default = Now())
    
        Dim frm As Form
        Dim fld As DAO.Field
        Dim rsDat As DAO.Recordset
        Dim rsAudit As DAO.Recordset
        Dim blChange As Boolean
    
        blChange = False
        Set frm = Forms(FormName)
        Set rsDat = CurrentDb.OpenRecordset("Select * From " & OriginalTable & " Where " & KeyField & " = " & frm(KeyField))
        ' Loop through to see if any values have changed
        For Each fld In rsDat.Fields
        If fld.Value <> frm(fld.Name) Then blChange = True
        Next
        If blChange Then
            ' Create a record in the Audit Trail
            Set rsAudit = CurrentDb.OpenRecordset("Select * From " & AuditTable)
            rsAudit.AddNew
            rsAudit("AuditUser") = fnUserID  ' I use a fuction to grab the user ID from the registry
            ' Loop through each filed and add it to the Audit Table
            For Each fld In rsDat.Fields
                rsAudit(fld.Name) = fld.Value
            Next
            rsAudit.Update
            ' Update the new data to the original table
            ' (if using this on a Bound Form, drop this part)
            rsDat.Edit
            For Each fld In rsDat.Fields
                If fld.Value <> frm(fld.Name) Then fld.Value = frm(fld.Name)
            Next
            rsDat.Update
        End If
        Set rsAudit = Nothing
        Set rsDat = Nothing
        Set frm = Nothing
        Set fld = Nothing
    End Sub
    This is dynamic - you can add fields to the original table providing you add the same fields to the Audit table.

    If you create a query on the Audit table, selecting everything EXCEPT the 3 extra fields and then create a Union Query of the Audit Query and the Original Table, you create your report goruped by record ID with DateAdded descending and there's a complete audit trail of every record.

    Have fun - I generally charge for this kind of thing!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •