Results 1 to 9 of 9
  1. #1
    Join Date
    Aug 2006
    Posts
    559

    Unanswered: Track changes / updates

    Is there an easy way on like the "before update" or "after update" events on a form to track user changes?

    I want it to record the user name, date, time, the fields changed / added to and what was done (like if they added notes or whatnot)?

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Lots of ways; the term you want to search for is "audit". Here's one method:

    http://allenbrowne.com/AppAudit.html
    Paul

  3. #3
    Join Date
    Aug 2006
    Posts
    559
    Yeah, I figured an audit log. But I also am going to need it to output to the screen, when a record is opened, saying, "User XYZ modified this record on XX/XX/XXXX with changes to XXX fields."

    I wonder if that's possible?

  4. #4
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Sure, it's just a matter of tying how you display it to how you save it. I have a cab reservation/dispatch system. If you pull up a reservation, it will display a subform of its history at the bottom (reservation taken, dispatched, any audit data). The audit/history table includes reservation number, so the subform is tied to the main form via master/child links.
    Paul

  5. #5
    Join Date
    Aug 2006
    Posts
    559
    I found this audit trail example.

    I wonder if it'd be hard to add this into my db, BUT instead of having it the way that it is, make it like have a statement I did from my old db, this was the statement:

    DoCmd.RunSQL "INSERT INTO Audit (UserID, Action) VALUES ('" & pstrUserName & "', 'Record Update: " & strDocID & "');"

    I wonder if I can get it into mine but add in the user name, task id number, changes made.

    AND THEN...

    Make it have a pop-up on the form's 'on open' event which says, 'This task was last edited on XX/XX/XXX by user name" **This would just be like a dialog box that says the information and the user can just click 'ok' and it'll take them back to the task for them to edit it.

    **This ONLY shows when a task that is "old" rather than a brand new task.
    Attached Files Attached Files

  6. #6
    Join Date
    Aug 2006
    Posts
    559
    Ok, I decided to see if I could reconstruct the audit table from my last db, the one that got corrupted.

    I did, found the 'audit statement' to be the following:

    Code:
    DoCmd.RunSQL "INSERT INTO Audit (UserID, Action) VALUES ('"& pstrUserName &"', 'Record Update: " & strDocID & "');
    So...

    In my db, I changed this to be, after importing the table and renaming it, to be:

    Code:
    DoCmd.RunSQL "INSERT INTO tblAudit (User_Name, Action) VALUES ( '" & fOSUserName & "', 'Record Update: " & Task_ID & "')
    It works, some what, it puts in the date, time, user name.

    **It doesn't do the following which I need to have done**

    However, I'm trying to get it to put in the Task_ID number that was opened/changed/edited PLUS I'm trying to get it to record the changes that were made, like the individual changes (ie: date addition, notes added, status change, etc).

    Does anyone know what I'm doing wrong?

  7. #7
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    For starters, I don't see how that puts in "date, time, user name". It appears to put in user name and task ID. You're not doing anything wrong per se, you're simply not passing the additional information. You might look at the OldValue property of bound controls. Maybe this call to an audit function will give you some ideas:

    AuditUpdate Me.res_num, "Comments", Me.Comments.OldValue, Me.Comments.Value
    Paul

  8. #8
    Join Date
    Aug 2006
    Posts
    559
    Paul,

    The table that is the "Audit" has in it three fields:

    -Security ID (Autonumber) & (PK)
    -Date ( =Now )
    -Time ( =Now )

    That is why it shows the date and time, it's done automatically.

    Now I added : User_Name, Action, Task_ID

    There it puts in the actual user name (fOSUserName) and for Action it says " Record Update: 17 " with Task_ID being blank / a zero value

    So for the most part, it's ok. I just need to figure out how to capture the actual Task_ID number that is begun / edited and the actions taken within the task, or any / all of the controls on the form, frmTask, which are changed or edited.

    B

  9. #9
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Isn't 17 the task ID? Based on this it must be:

    ...Record Update: " & Task_ID...

    You're not populating the actual task ID field, so it would be blank.
    Paul

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •