Results 1 to 5 of 5
  1. #1
    bigroo's Avatar
    bigroo is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Jan 2012
    Location
    Austin, Texas
    Posts
    101

    Audit Trail

    Hi All,

    I found this simple explaination regarding creating an "Audit Trail" for your database. I have been able to modifiy to suit my needs by adding date/time record was added/update, but I wish to go one step further and not to sure how to do this.

    Function WriteChanges()
    Dim f As Form
    Dim c As Control
    Dim frm As String
    Dim user As String
    Dim sql As String
    Dim changes As String
    Dim RecordID As String
    Dim DateofChange As Date
    Dim db As DAO.Database
    Set f = Screen.ActiveForm
    Set db = CurrentDb
    frm = Screen.ActiveForm.Name
    user = Application.CurrentUser
    DateofChange = Now()
    changes = ""
    sql = "INSERT INTO AuditTrail " & _
    "([DateofChange], [FormName], [User], [RecordID], [ChangesMade]) " & _
    "VALUES ('" & DateofChange & "', '" & frm & "', '" & user & "', '" & RecordID & "', "
    For Each c In f.Controls
    Select Case c.ControlType
    Case acTextBox, acComboBox, acListBox, acOptionGroup
    If IsNull(c.OldValue) And Not IsNull(c.Value) Then
    changes = changes & _
    c.Name & "--" & "BLANK" & "--" & c.Value & _
    vbCrLf
    ElseIf IsNull(c.Value) And Not IsNull(c.OldValue) Then
    changes = changes & _
    c.Name & "--" & c.OldValue & "--" & "BLANK" & _
    vbCrLf
    ElseIf c.Value <> c.OldValue Then
    changes = changes & _
    c.Name & "--" & c.OldValue & "--" & c.Value & _
    vbCrLf
    End If
    End Select
    Next c
    sql = sql & "'" & changes & "');"


    db.Execute sql, dbFailOnError
    Set f = Nothing
    Set db = Nothing
    End Function
    If I add/update a field in a form, I would like to add the recordID value to my "Audit Trail" table. As you can see I add Dim RecordID as String, this is not right or I am missing something.

    The article is here http://www.databasedev.co.uk/audit_trail.html

    Can someone offer assistance here?

  2. #2
    John_G is offline VIP
    Windows XP Access 2003
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,608
    Hi -

    what is [RecordID] ? Is it a control on the form or field in the recordsource of the form, in which case this should work. But you have declared it as a string - is it? I see what you are trying to do, that is to identify not only what the changes were, but in which record. This will only work if every form (or the recordsource) has a control/field called RecordID.

    Am I on the right track?

    John

  3. #3
    bigroo's Avatar
    bigroo is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Jan 2012
    Location
    Austin, Texas
    Posts
    101
    Hi John_G,

    Yes, you are on the right track!!
    I want to return the auto number value for each change, but the problem is every table has a different name for the autonumber field!
    I am just playing around with the Northwind DB at present trying to make this work as in the webpage tutorial, then adapt it for my own database, but again I will run into the same issue there, every autonumber field in each table is named different.

    Should this be declared as a string or number for it to work?
    Any suggestions will help!, I am wondering if I am barking up the wrong tree, it may not work because of the field names all different in each table.

  4. #4
    John_G is offline VIP
    Windows XP Access 2003
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,608
    This might actually be easy. Assuming that you are calling WriteChanges in the Before Update event of your forms, pass the RecordID value to WriteChanges as a parameter.

    - Change the first line to : Function WriteChanges(RecordID as Long)
    - Delete Dim RecordID as String
    - Change this line:
    VALUES ('" & DateofChange & "', '" & frm & "', '" & user & "', '" & RecordID & "', "
    to
    VALUES ('" & DateofChange & "', '" & frm & "', '" & user & "', " & RecordID & ", "

    The change is just removing the quote marks around RecordID in the values list.

    Use it in the Before Update events as an event procedure; instead of =writechanges(), you would have

    WriteChanges(me!TableID) where TableID is the name of the form control containing the ID (it will be different in each form, as you said)

    Ensure that RecordID is type Long in your AuditTrail table, and give it a try.

    Let us know how you make out!

    John

  5. #5
    bigroo's Avatar
    bigroo is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Jan 2012
    Location
    Austin, Texas
    Posts
    101
    Hi John_G,

    Thanks for the reply!!
    I have all the changes you outlined done, but seem to be having issues with the portion..

    Use it in the Before Update events as an event procedure; instead of =writechanges(), you would have

    WriteChanges(me!TableID) where TableID is the name of the form control containing the ID (it will be different in each form, as you said)
    I may not understand or I am missing something, but I get an error when I try to make a change and it writes to the table.

    Ok, just worked it out as I was typing this note.
    On each form under the Before Update I entered =WriteChanges([ID]) and it performed just as I want on the Audit Table.

    Thanks John.

    Now let me try this on my real database and see if it runs smooth!!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. audit trail
    By slimjen in forum Forms
    Replies: 1
    Last Post: 10-13-2011, 01:52 PM
  2. Audit Trail for SubForms Not Working
    By PinkNinja in forum Access
    Replies: 6
    Last Post: 01-07-2011, 11:03 PM
  3. Audit trail not working on form with subform
    By jpkeller55 in forum Access
    Replies: 52
    Last Post: 01-07-2011, 12:39 PM
  4. Audit trail question #2
    By jpkeller55 in forum Access
    Replies: 9
    Last Post: 09-13-2010, 05:55 PM
  5. Audit trail question
    By jpkeller55 in forum Access
    Replies: 5
    Last Post: 09-13-2010, 04:12 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums