Track who made last change

[BRZ-Ryan]

I'm getting reports of pieces of a record changing on it's own, and I want to confirm whether it's the db or user error. Is there a way to record who made a change? I can read the persons employee id into a variable just not sure how to write it when data is written to the record.

[ssanfu]

Here is an article by Allen Browne on "Creating an Audit Log" : http://allenbrowne.com/appaudit.html

Roger Carlson also has two example dBs on creating a simple audit trail table.
(intermediate) http://www.rogersaccesslibrary.com/forum/topic399.html
(advanced) http://www.rogersaccesslibrary.com/forum/topic398.html

[June7]

What you want to do maybe is an audit trail or log. Here is one http://allenbrowne.com/AppAudit.html

Otherwise, code in table BeforeUpdate data macro or form BeforeUpdate event procedure could populate a field in table with employee id.

[BRZ-Ryan]

This is for the event manager db that you helped me with in the past... I want to add an employee id and date field to each event to track the last update, even better, a separate table logging every change made to events. BeforeUpdate is called right before the db engine writes data I assume? And only for edits to existing records?

Thanks for the link, I will test this tomorrow. Have you ever used it? And do you know how to implement the access 2010 functionality that makes it work better as described in the first section?

[June7]

There are two replies to your original post. Who helped you before?

I've never built an audit log procedure. I have never used data macros but I expect the macro would call the VBA function code.

[BRZ-Ryan]

Nobody has helped me with this yet. I am referring to the data access macros, yes I agree they will probably call a VBA function, probably more efficiently.

[orange]

see this link for macro info for audit log

[BRZ-Ryan]

I am using the link that you provided June, but the code is throwing an error stating it cannot run a query on a multi-value field (which is stated in your link). I do not have any multi-value fields that I am aware of--each field holds a single value. Is there a property I need to set for my fields to set them as being single-value? Does it consider text fields as multi-value? I do have an attachments field in this table, could that be it?

[BRZ-Ryan]

I tried manually selecting fields to monitor and now I am getting an error when the SQL executes:

It looks like it is saying only insert ( audType, audDate, audUser ) and then the select statement asks for those 3 values, along with every other piece of info to record. Is this right?

Error="Number of query values and destination fields are not the same."
See my changes with prefix @@@

Function AuditEditBegin(sTable As String, sAudTmpTable As String, sKeyField As String, _
    lngKeyValue As Long, bWasNewRecord As Boolean) As Boolean

    Dim db As DAO.Database           ' Current database
    Dim sSQL As String
    'Remove any cancelled update still in the tmp table.
    Set db = DBEngine(0)(0)
    sSQL = "DELETE FROM " & sAudTmpTable & ";"
    db.Execute sSQL
    ' If this was not a new record, save the old values.
    If Not bWasNewRecord Then
        sSQL = "INSERT INTO " & sAudTmpTable & " ( audType, audDate, audUser ) " & _

            '@@@THIS LINE WAS
            '@@@"SELECT 'EditFrom' AS Expr1, Now() AS Expr2, NetworkUserName() AS Expr3, " & sTable & ".*" & _

            'Modified to exclude attachments field
            "SELECT 'EditFrom' AS Expr1, Now() AS Expr2, NetworkUserName() AS Expr3, " & _
            sTable & ".ID, " & _
            sTable & ".CompanyID, " & _
            sTable & ".EventDate, " & _
            sTable & ".EventStartTime, " & _
            sTable & ".EventEndTime, " & _
            sTable & ".EventType, " & _
            sTable & ".ScheduledBy, " & _
            sTable & ".Description, " & _
            sTable & ".NumAutoQuotes, " & _
            sTable & ".NumHomeQuotes, " & _
            sTable & ".NumBrochures, " & _
            sTable & ".Comments, " & _
            sTable & ".WasEmailSent, " & _
            sTable & ".WasMeetingSent " & _
            "FROM " & sTable & " WHERE (" & sTable & "." & sKeyField & " = " & lngKeyValue & ");"
       
        db.Execute sSQL, dbFailOnError

[BRZ-Ryan]

Disregard, fixed this way:

INSERT INTO " & sAudTmpTable & " ( audType, audDate, audUser ) 
SELECT 'EditFrom' AS Expr1, Now() AS Expr2, NetworkUserName() AS Expr3, (FIELDS HERE)

to

INSERT INTO " & sAudTable 
SELECT 'EditTo' AS audType, Now() AS audDate, NetworkUserName() AS audUser, (FIELDS HERE)