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”