Audit Trail / Changelog

[tdefreest]

Hello,
I have been asked to implement a changelog / audit trail for forecast comparison purposes. In my research I have found three methods of implementing this fix, see below.

• Method 1 & 2: http://www.fontstuff.com/access/acctut21.htm
• Method 3: http://allenbrowne.com/appaudit.html

Personally, I think I like Martin Greene's solution better; however, my situation is a bit different than the examples and I cannot get any of them to function correctly.

I have the following objects:

1. frmProposalInput
2. tblOpportunityTable
3. tblTransactionDetail

Users use the form to modify the data in the two tables, which are joined by a relationship between the two (ProposalNumber). The problem is that the audit trail will only record the change to one field at a time and create separate records, not the entire conjoined record, which most of my records are created using a query to search both tables. Then, I need to create a query/report that recreates the record using all these various audit trail records to create the "history" record. confused:my head is spinning...)

Is there a way to get this done with the current structure of the database? Or should I simply try to restructure the database in order to get this functionality?

Any help would be greatly appreciated.

 

[Cronk]

Quick and dirty method, create an audit table with fields from both your tables, together with a separate ID key field, timestamp, user name.

In the BeforeUpdate event for the form, save the previous values with an append for one table and an update for the other table.

Alternatively, create 2 audit tables, one for each of your tables and append previous values.

Comment, on reflection the Alternative method sounds easier and more efficient.

 

[tdefreest]

Edit: Fixed this issue by adding in wildcard asterisks to the LIKE SQL code.. *doh*. I'm still troubleshooting the rest of the script. Will post results soon.

---

So, I am experiencing a conflict with the BeforeUpdate method. I currently have a data validation code in my BeforeUpdate, which runs off the Tag "Required". Now, I expected that if I change the SQL from

If Nz(.Tag) Like "Required" Then

it would become a wildcard and then I could use both the tags in each field. Unfortunately, its not working.

 Private Sub Form_BeforeUpdate(Cancel As Integer)
'Cancels Update if required fields are missing.
'if fields are missing, It will  create a popup message explaining which fields need data
' Form is saved and closed if all required fields have data
 Dim strError As String
Dim ctl As Control
Dim blnIsValid As Boolean
 strError = "Validation failed for the following reasons: " & vbNewLine
blnIsValid = True
 For Each ctl In Me.Controls
    With ctl
        Select Case ctl.ControlType
            Case acTextBox, acComboBox, acListBox, acCheckBox, acOptionButton, acOptionGroup, acToggleButton
                If Nz(.Tag) Like "Required" Then
                If Nz(.Value) = "" Then
                    blnIsValid = False
                    strError = strError & "   - " & ctl.Name & " is required" & vbNewLine
                End If
                End If
        End Select
    End With
Next ctl
 If blnIsValid = False Then
    'Form data is NOT valid!
    Cancel = True 'Cancel the update
    MsgBox strError, vbInformation
End If
 If blnIsValid = True Then
    Me!EditedWhen = Now()
End If
 Set ctl = Nothing
 If Me.NewRecord Then
    'Calls modAudit function to record new records to Audit Trail
    Call AuditChanges("ProposalNumberID", "NEW")
 Else
    'Calls modAudit function to record edits to Audit Trail
    Call AuditChanges("ProposalNumberID", "EDIT")
 End If
     
End Sub

 

[tdefreest]

Woohoo! I was successful in implementing this audit log; however, it is only the first step.

I need to create a query/report that includes the value of every record as it was 1 year in the past, the current value of each record, and the difference between the two.

I'm not even sure something like this is possible.. I'm not sure where to where to start with this query. Maybe someone has done something like this in the past or can help me get started?

Thanks!

 

[jdraw]

You may get some insight and tips from this Allen Browne article.

Good luck.

 

[tdefreest]

I am working to implement Allen Browne's solution. I ran into an issue: Run-time error 3825 "SELECT * cannot be used in an INSERT INTO query when the source or destination table contains a multi-valued field."

This occurs at: "db.Execute sSQL, dbFailOnError" in the following section

 ' If this was not a new record, save the old values.
 If Not bWasNewRecord Then
        sSQL = "INSERT INTO " & sAudTmpTable & " ( audType, audDate, audUser ) " & _
            "SELECT 'EditFrom' AS Expr1, Now() AS Expr2, NetworkUserName() AS Expr3, " & sTable & ".* " & _
            "FROM " & sTable & " WHERE (" & sTable & "." & sKeyField & " = " & lngKeyValue & ");"
        db.Execute sSQL, dbFailOnError
    End If
    AuditEditBegin = True

I'm confused though.. because I do not use any multi-valued fields. Any thoughts?

 

[jdraw]

Instead of doing your
db.Execute sSQL, dbFailOnError

Make that a comment, then create this line

Debug.print sSQL

This will print whatever Access has rendered (substituted/calculated) from sSQL to the immediate window. That print out will likely show you a syntax or other error.
If that doesn't help, then post the results of the debug.print.

Always a good practice to do the debug.print before attempting to execute the code.

Good luck.