I need to add feature which will document each time a change is made in patient data, with the userID, old value, new value, date and reason for the change will be storeed in a history table.
Anyone able to share some code and ideas on this?

Bob Ratner
[email protected]

Hi Bob,

One way to go that does'nt involve using a seperate table (the audit trail is stored in a memo field of each record) is to:

1. Add a new memo field to the table that contains the records you need an audit trail for. Name the field 'Updates.'

2. Add a new textbox to the form that you are doing your record editing in. Name it txtUpdates. Set its Control Source property to your new Updates memo field. Set its visible property to No if you want.

3. Open the form you are doing your record editing in in design view and place the following in the form's Before Update event:

   =AuditTrail()

4. Add the following code to a standard module:

~~~~Code~~~~

Function AuditTrail()
On Error GoTo Err_Handler

    Dim frm As Form, ctl As Control, xName As String
    Set frm = Screen.ActiveForm

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

    'If new record, record it in audit trail and exit sub.
    If frm.NewRecord = True Then
        frm!txtUpdates = frm!txtUpdates & Chr(13) & Chr(10) & _
        "New Record """
    End If

    'Check each data entry control for change and record
    'old value of Control.
    For Each ctl In frm.Controls

    'Only check data entry type controls.
    Select Case ctl.ControlType
        Case acTextBox, acComboBox, acListBox, acOptionGroup
            ' Skip txtUpdates field.
            If ctl.Name <> "txtUpdates" Then

            ' If control was previously Null, record "previous
            ' value was blank."
                If IsNull(ctl.OldValue) Or ctl.OldValue = "" Then
                    frm!txtUpdates = frm!txtUpdates & Chr(13) & _
                    Chr(10) & ctl.Name & "--previous value was blank"

                ' If control had previous value, record previous value.
                ElseIf ctl.Value <> ctl.OldValue Then
                    frm!txtUpdates = frm!txtUpdates & Chr(13) & Chr(10) & _
                    ctl.Name & "==previous value was " & ctl.OldValue
                End If
            End If
        End Select
    Next ctl

ExitHere:
    Exit Function

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

~~~~End Code~~~~

5. Edit a record a few times in the form. Find the record in its table. Place your cursor in the Update memo field and press F2 to view its contents. The output looks like:

Changes made on 7/28/2004 by Admin;
Region--previous value was blank
Changes made on 7/28/2004 by Admin;
City==previous value was New York
Region--previous value was blank
Changes made on 7/28/2004 by Admin;
Region--previous value was blank
Changes made on 7/28/2004 by Admin;
Region==previous value was New York
Changes made on 7/28/2004 by Admin;
City==previous value was Chicago

If you want to keep the audit trail in a seperate audit log table, see Allen Brown's modlue at:

http://members.iinet.net.au/~allenbrowne/AppAudit.html

Couple of ways to go anyway.

HTH,

Bob