Making audit trial work on subforms

Question

Hello, i know this is very common question however, since i am not an expert, i could not make it work after trying lots of ways suggested in some websites. I am using Martin Green's code. link here http://www.fontstuff.com/access/acctut21.htm .

below is the code and everything is as described on article.

Sub AuditChanges(IDField As String, UserAction As String)

    On Error GoTo AuditChanges_Err

    Dim cnn As ADODB.Connection

    Dim rst As ADODB.Recordset

    Dim ctl As Control

    Dim datTimeCheck As Date

    Dim strUserID As String

    Set cnn = CurrentProject.Connection

    Set rst = New ADODB.Recordset

    rst.Open "SELECT * FROM tblAuditTrail", cnn, adOpenDynamic, adLockOptimistic

    datTimeCheck = Now()

    strUserID = Environ("USERNAME")

    Select Case UserAction

        Case "EDIT"

            For Each ctl In Screen.ActiveForm.Controls

                If ctl.Tag = "Audit" Then

                    If Nz(ctl.Value) <> Nz(ctl.OldValue) Then

                        With rst

                            .AddNew

                            ![DateTime] = datTimeCheck

                            ![UserName] = strUserID

                            ![FormName] = Screen.ActiveForm.Name

                            ![Action] = UserAction

                            ![recordID] = Screen.ActiveForm.Controls(IDField).Value

                            ![FieldName] = ctl.ControlSource

                            ![OldValue] = ctl.OldValue

                            ![NewValue] = ctl.Value

                            .Update

                        End With

                    End If

                End If

            Next ctl

        Case Else

            With rst

                .AddNew

                ![DateTime] = datTimeCheck

                ![UserName] = strUserID

                ![FormName] = Screen.ActiveForm.Name

                ![Action] = UserAction

                ![recordID] = Screen.ActiveForm.Controls(IDField).Value

                .Update

            End With

    End Select

AuditChanges_Exit:

    On Error Resume Next

    rst.Close

    cnn.Close

    Set rst = Nothing

    Set cnn = Nothing

    Exit Sub

AuditChanges_Err:

    MsgBox Err.Description, vbCritical, "ERROR!"

    Resume AuditChanges_Exit

End Sub

I am sure it needs minor code adjustments to read the changes on subforms. Any help would be appreciated.

Answer 1

You would change the first parameter to a text string of the table name that field is from.

Answer 2

Thank you..

Last thing... if form is bound to a query what should i change? Do i need to have PK field on form itself? Or if it is in query, will it work?

Answer 3

1. Yes it will work on any form as it is Control based, not form based. It will pull the Recordsource from whatever from is active, so it doesn't matter whether its a subform or how deep it is buried.
2. You can use Environ("Username")
3. Yes because its control based, you have to place that line of code in the Before Update event of every control you want to Audit. The advantages of this are you only need to audit data that's important. For example, in my Employee database, I audit if an employee's DOB is changed, but not their address. changing a DOB can have consequences in benefit calculations, so we would need to know who changed it and when. But if an address is changed by mistake we just change it back. it is more cumbersome to place the code so many times, but its really a copy and paste. Once you set the line of code for one control its exactly the same for all other controls on the form.

Answer 4

Thank you Scott.

I have 3 questions.

1- Will it work on subform?

2- If i want strUser as current Computer log-in user what is the code.?

3- I have to place before update event of every control instead of form right?

Answer 5

I prefer to do my audits on the Control Level. The system I use has the following AutditTrails function:

Public Sub AuditTrail(strTable As String, strField As String, lngRecord As Long, varOld As Variant, varNew As Variant)

Dim strSQL As String, strUser As String

strUser = DLookup("[EmpID]", "tblParameters")

strSQL = "INSERT INTO tblAuditTrail (Tablename, Fieldname, RecordID, OldValue, NewValue, ChangeDate, ChangeBy) " & _

            "VALUES('" & strTable & "', '" & strField & "', " & lngRecord & ", '" & varOld & "', '" & varNew & "', #" & _

            Now() & "#, '" & strUser & "');"

CurrentDb.Execute strSQL, dbFailOnError

End Sub

You can replace the strUser line with however you identify the current user. 

I call this function in the Before Update event of any control I want to Audit:

Call AuditTrail(Screen.ActiveForm.RecordSource, Screen.ActiveControl.ControlSource, Me.PrimaryKey, Screen.ActiveControl.OldValue, Screen.ActiveControl)

The only thing that needs to be changed is the name of the control bound to your primary key. However, Screen.ActiveForm.Recordsource MAY need to be changed if the recordsource is not a table.  

I also have an Audit Trail that works on the Table level, described in my blog on Audit Trails with Data macros.