User Update Monitoring (Access 97)

Topic

New Reply

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

Reply | Quote

Replies

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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?

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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?

Reply | Quote

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

Reply | Quote