Audit table function

When using databases to track progress on a workflow, it is useful to be able to write actions to a table, and therefore provide an audit trail of the actions undertaken. I have used this function in many databases.

This requires a table to be set up as follows – PARM_ID is the unique ID related to the entity being tracked, this could be a customer ID, a product ID or any other identifier:

It also calls the user name function to obtain the ID of the person doing the action.

Function AddToAuditTable(PARM_ID As Integer, Activity As String) As Boolean

    'Adds description of activity on a case to the audit table

    Dim strSQL As String

    Dim UserID As String

       UserID = fOSUserName()

    strSQL = "INSERT INTO Tbl_AuditTrail (PARM_ID, Activity, DateOccurred, UserID) VALUES (" & PARM_ID & ", '" & Activity & "', #" & Format(Now, "dd-mmm-yyyy hh:nn:ss") & "#, '" & UserID & "');"

    CurrentDb.Execute strSQL

   End Function


You can then call this function as follows:

AddToAuditTable ObjectID, “Added new date to object”

Leave a Reply

Your email address will not be published. Required fields are marked *