[AccessD] Recording field changes

Hi David,

You could play with adding a memo field to your form's record source called 
"Updates", add a textbox control to your form thats bound to "Updates" (you 
can hide this field if you like), add =AuditTrailX() as a function call in 
your form's Before Update event, then add the following module to your 
project. Wish I could remember where this came from but i can't. The code 
iterates through all the data entry controls in your form's controls 
collection, recording old and new values for only those fields that have 
changed.

Function AuditTrailX()
On Error GoTo Err_Handler

    Dim MyForm As Form, C As Control, xName As String
    Set MyForm = Screen.ActiveForm

    '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


Bob



>From: David Emerson <davide at dalyn.co.nz>
>Reply-To: Access Developers discussion and problem 
>solving<accessd at databaseadvisors.com>
>To: accessd at databaseadvisors.com
>Subject: [AccessD] Recording field changes
>Date: Tue, 22 Jun 2004 08:43:34 +1200
>
>Group,
>
>I have a client that needs to record all changes to the data on a field by 
>field basis (they don't want to record whole records if there is a change - 
>just the fields that changed (old and new values)).  My initial though is 
>to put code in every field that records the value on entry, compares this 
>on exit and records the change if they values are different.
>
>Because there are several hundred fields involved I was wondering if there 
>was any easy way of doing the job without adding code to every field.
>
>Regards
>
>David Emerson
>Dalyn Software Ltd
>25 Cunliffe St, Churton Park
>Wellington, New Zealand
>Ph/Fax (04) 478-7456
>Mobile 027-280-9348
>
>--
>_______________________________________________
>AccessD mailing list
>AccessD at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/accessd
>Website: http://www.databaseadvisors.com

_________________________________________________________________
MSN Movies - Trailers, showtimes, DVD's, and the latest news from Hollywood! 
http://movies.msn.click-url.com/go/onm00200509ave/direct/01/