[AccessD] Audit Trail Suggestions

[Rocky Smolin]

In an app I just created for a company doing a DNA mutation assay in which
every field in every form that's changed needs to be recorded in an audit
trail table, the following sub is called in the after update event of each
bound field.  There is only one input form but 7 tabs each with its own
form.  Since this is a one shot - the data will be collected once,
transmitted to another company, then the db discarded (sob) -  I took the
easy way and pasted the sub into the CBF of each input form once I got it
working in the first form.  

You could, I suppose, make it a Public sub and pass the form name and the
current control name, etc.

The caption (Name property) of the bound field is the same as the field
name.  For some people this is a huge no-no - for others, it's a yawn.  I
never had any trouble with that. 

HTH 

Rocky



Private Sub AuditTrail(Optional argOldValue, Optional argNewValue)

Dim ctlA As Control
Dim strCaption As String

    gstrActiveControl = Screen.ActiveControl.Name
    Set ctlA = Me(gstrActiveControl)
    strCaption = ctlA.Controls(0).Name
    
'    MsgBox "User: " & glngUserID & vbCrLf _
        & "Date: " & Date & vbCrLf _
        & "TAB NUMBER: 2" & vbCrLf _
        & "Label Name: " & ctlA.Controls(0).Name & vbCrLf _
        & "Label Content: " & Me(strCaption).Caption & vbCrLf _
        & "Control Source: " & Me(gstrActiveControl).ControlSource & vbCrLf
_
        & "Old Value: " & ctlA.OldValue & vbCrLf _
        & "New Value: " & Me(gstrActiveControl).Value
'Exit Sub

With rsAudit
    .AddNew
    !fldUserID = glngUserID
    !fldATDateTime = Now()
    !fldATTabNumber = 2
    !fldATLabelName = Me(strCaption).Caption
    
    If IsMissing(argOldValue) Then
        !fldATOldValue = ctlA.OldValue
    Else
        !fldATOldValue = argOldValue
    End If
    
    If IsMissing(argNewValue) Then
        !fldATNewValue = Me(gstrActiveControl).Value
    Else
        !fldATNewValue = argNewValue
    End If
    
    .Update
    Call cmdSave_Click
End With

-----Original Message-----
From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of
David Emerson
Sent: Friday, March 13, 2015 9:22 PM
To: AccessD
Subject: [AccessD] Audit Trail Suggestions

Hi Team,

Looking for ideas for implementation.

Access 2010 FE, SQL 2008 R2

A client wants to keep track of some fields in some tables when they are
changed.  There are over 50 tables involved and anywhere from 3 to 30 fields
per table.  The tables and fields are predefined.  Most of the data changes
will be done via Access screens but there are some fields that are changed
through code

When a field value is changed they want to store in a log table date, person
making change, old value, new value.

Looking for ideas of how others might have tackled this type of problem
before.

Regards

David Emerson
Dalyn Software Ltd
Wellington, New Zealand



--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com