[uzymedphys]

Hi

I have a simple audit trail in my access database which was adapted from the TechRepublic site (Ref 'ARTICLE': "A simple solution for tracking changes to Access data - by Susan Sales Harkins; 2001-6240-0).

Problem: The current 'audit trail' code only accomodates changes to the database but not new entries to the database.

Question: Can anyone assist me with the complete code that will audit ALL EDITS to the database - CODE which can apply to all tables at once (like my original code and it was very short too)?

Cheers

Usman

[slurpee55]

Just so others who code can see what you are dealing with right now, this is what is in the article - please note any changes you have made.

Const cDQ As String = """"

Sub AuditTrail(frm As Form, recordid As Control)
  'Track changes to data.
  'recordid identifies the pk field's corresponding
  'control in frm, in order to id record.
  Dim ctl As Control
  Dim varBefore As Variant
  Dim varAfter As Variant
  Dim strControlName As String
  Dim strSQL As String
  On Error GoTo ErrHandler
  'Get changed values.
  For Each ctl In frm.Controls
    With ctl
    'Avoid labels and other controls with Value property.
    If .ControlType = acTextBox Then
      If .Value <> .OldValue Then
        varBefore = .OldValue
        varAfter = .Value
        strControlName = .Name
        'Build INSERT INTO statement.
        strSQL = "INSERT INTO " _
           & "Audit (EditDate, User, RecordID, SourceTable, " _
           & " SourceField, BeforeValue, AfterValue) " _
           & "VALUES (Now()," _
           & cDQ & Environ("username") & cDQ & ", " _
           & cDQ & recordid.Value & cDQ & ", " _
           & cDQ & frm.RecordSource & cDQ & ", " _
           & cDQ & .Name & cDQ & ", " _
           & cDQ & varBefore & cDQ & ", " _
           & cDQ & varAfter & cDQ & ")"
        'View evaluated statement in Immediate window.
        Debug.Print strSQL
        DoCmd.SetWarnings False
        DoCmd.RunSQL strSQL
        DoCmd.SetWarnings True
      End If
    End If
    End With
  Next
  Set ctl = Nothing
  Exit Sub

ErrHandler:
  MsgBox Err.Description & vbNewLine _
   & Err.Number, vbOKOnly, "Error"
End Sub

 

Save the module as basAuditTrail and close the VBE.

You can call the subprocedure from any data entry form. We used the AutoForm wizard to create an example form based on the Shippers table in Northwind (the sample database that comes with Access). You can use any bound form, as long as it allows edits. To follow our example, open the Shippers form's module in the VBE and enter the following statement to call the auditing subprocedure:

Private Sub Form_BeforeUpdate(Cancel As Integer)
  Call AuditTrail(Me, ShipperID)
End Sub

Is that it essentially?

[uzymedphys]

Yes - that is it.

Its very simple - but the problem is it is very limited.

If I change a value in one field then it will record both the old and new values.

If I delete a value - it won't record it.

If I input a new value (in an empty field) it won't record that either.

And if I have a new entry altogether - it won't record that either.

I am looking to record all edits in any table.

Does anyone have any fancy code (that is efficient and easy to implement) that will cover these requirements?

Thanks

Usman

[slurpee55]

I will see if OBP is around - he is the best Access person I know.

[OBP]

I have the code, but it is not like the code you have.
The code that I use goes on the Data entry/edit form and when an item is changed or added it records the fact and what type of transaction has taken place, ie added, edited or deleted and who did it, based on the log on ID, but not the values of before and after.
I suppose it could be modified to do that, but when adding a new record would you want to record the entries for all of the fields?

[uzymedphys]

Hi

Thanks for your message OBP - and to Slurpee for informing OBP.

I prefer to log fields that have changed and a reference to the table name and related field ID would be suffient.

It is being used at the moment - and I am administrating the database i.e. making entries with a collegue double checking my entries to validate the database.

So once the audit trail is in place - I don't have to worry about who made the changes and when - as long as any two people are involved. I envisage that ultimately anyone with the authority can add data to the database - but without the audit trail, I really can't take the risk.

Once the audit trail is in place - I can then provide login access to responsible users - so I will know who has changed the database, when and possibly for what reasons too.

Hope this helps....thanks

Usman

[uzymedphys]

Yes - I would certainly like to log what values were present before the changes and values after the change.

Also, to log values from empty fields, deleted fields etc.

I think - probably to log all changes in any one session.

Usman

[OBP]

Can you supply a Blank Compacted & Repaired Zipped copy of the Database?

[uzymedphys]

can we exchange by email in that case?

Usman

[slurpee55]

Thanks for coming OBP
Way over me - I can't even think about this; makes my head hurt.

[uzymedphys]

Hi OBP

I am sure you posted your email - where did it go?

I can't seem to find it?

Thanks

Usman

[jimr381]

If you click on his name you can email him from the drop-down list.

[OBP]

I sent you my email address before in a Private message.
I am working on an audit trail for another database so I should have something for you in the near future. I haven't managed to do any programmign today due to a Hospital appointment and a visit from the Grand Children.

[uzymedphys]

I didn't write it down - as I thought I could come back to it once teh database is ready in the form you asked for....

could you display it again so I can jot it down? thanks

Usman

[slurpee55]

uzymedphys, a lot of people avoid posting their emails on here, or if they do, they do it in a form that spambots can't read (e.g. name-at-servername.com).
So I would suggest you follow jimr's advice and send OBP an email via the security of his profile.