Results 1 to 9 of 9
  1. #1
    2 Star Lounger
    Join Date
    Aug 2001
    Location
    Boston, Massachusetts, USA
    Posts
    167
    Thanks
    1
    Thanked 0 Times in 0 Posts

    User Update Monitoring (Access 97)

    I've tried using the CurrentUser function and I'm either doing something wrong or I've got the wrong function.

    I need to add 2 additional fields to each of my tables. The first field would identify the user that last updated the record. The second field would identify the date/time that user updated the record.

    Even though I have locked my database down with as much security as I could figure out (Full workgroup security has been setup for admin, mgr, and user groups with different access for each table, form, report, and query in the file), I still have the occasional user that's sneaky and getting to the background of the file. I've even removed access to the toolbars. I want to be able to find out who's doing it. If I can add this directly into the tables, I can continually monitor the records.

    thanks
    christine
    thanks
    christine

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: User Update Monitoring (Access 97)

    Access doesn't have events or triggers at the table level. You can set the default value of the first field to CurrentUser(), so that you know who has added a new record. But you can only keep track of who modifies a record if it is modified through a form.

    If you really need to implement this at the table level, migrate the data to SQLServer.

  3. #3
    2 Star Lounger
    Join Date
    Aug 2001
    Location
    Boston, Massachusetts, USA
    Posts
    167
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: User Update Monitoring (Access 97)

    All of my data is updated through forms. I use a switchboard that pops up at startup and only forms from there. How would I track it? Do I create a hidden expression on the form that populates the table's field with user and date/time?

    I know, I know......Believe me, I know there are better ways to do a lot of what I'm doing. The problem is when you learn from books and trial and error, you do thing the hard way, ALOT! One of these days, I will actually go to Access and SQL classes and learn this stuff. Until then, I just try to keep this dinosaur I created from dying!

    Thanks for the help Hans!

    christine
    thanks
    christine

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: User Update Monitoring (Access 97)

    Hi Christine,

    You can use the Before Update event of each form to set the name of the user and the date/time of modification. This will be fine for 99.99% of all modifications to the tables. Only if somebody manages to get direct access to a table or query will this be bypassed.

    Private Sub Form_BeforeUpdate(Cancel As Integer)
    Me.ModifiedBy = CurrentUser
    Me.ModifiedOn = Now
    End Sub

  5. #5
    2 Star Lounger
    Join Date
    Aug 2001
    Location
    Boston, Massachusetts, USA
    Posts
    167
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: User Update Monitoring (Access 97)

    Thank works GREAT!!!! Thanks, Hans!!!!

    Now, of course to push a little further.....Can I take the same logic and create another scenario which would record the user and date/time, if someone modifies the record?
    thanks
    christine

  6. #6
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: User Update Monitoring (Access 97)

    What Hans gave you should track the changes people make at the form level - you may also want to track who adds a record, and that can be done at the table level by setting default values for fields. If you are really concerned about locking users out of the table (and it's generally recommended), you probably want to turn off the ability to start the database but bypass the startup settings. That is one of the options that people often don't use - you as a developer need to be able to do that however, so often a hidden object on the start-up form is used in conjunction with a password. Unfortunately Access doesn't have any facility for tracking changes at the record level - there have been some attempts to do that but they weren't very successful. That's why Hans recommended SQL Server - it has that sort of tracking built in using something called triggers. Hope this helps.
    Wendell

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: User Update Monitoring (Access 97)

    As Wendell noted, that's precisely what the code I proposed does: each time somebody modifies (or adds) a record through the form, the user name and the date/time will be written into the record. The previous user name and date/time will be overwritten; if you want to preserve an audit trail, you would need to store this information in a separate table, but that might get out of hand quite soon in Access.

  8. #8
    2 Star Lounger
    Join Date
    Aug 2001
    Location
    Boston, Massachusetts, USA
    Posts
    167
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: User Update Monitoring (Access 97)

    Hello again. Sorry to keep dredging, but.....
    I followed your steps exactly and am getting a compile error message: Method or data member not found. I inserted the code on the BeforeUpdate event of the form and now I can't figure out why it's not working. I don't need to create an audit trail. I just need a record of the most recent person who changed the data on the form and when. Did I miss something?
    thanks
    christine

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: User Update Monitoring (Access 97)

    The code I provided

    Me.ModifiedBy = CurrentUser
    Me.ModifiedOn = Now

    assumes that you have added fields ModifiedBy and ModifiedOn to the table to keep track of by whom and when a record is modified. ModifiedBy should be a string field, and ModifiedOn a date/time field. If you have added similar fields, but have given them different names, use those names instead of ModifiedBy and ModifiedOn

Posting Permissions

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