"Running diary"? You could include a memo field and keep a text record,
appending the information to the end of the information already in the Memo
Field in the Before Update event -- IF you limit all updates to be done
through Forms in a controlled application environment. (Updates via query
done in the applications code could also pick up the user id.) You could, I
suppose, add a related table to any pertinent tables just for showing the
updating user. I'm not sure having it all in text will be as useful as it
might seem, particularly if you do not also record _what_ the user modified.
However, if you allow users any direct access to Tables or to the Query
features, you cannot enforce this with Microsoft Access using the default
Jet database engine. It does not have a method for ensuring execution of
SQL, macros, nor code on a manual or query-driven update (in many server
databases, that would be done with a "trigger" event, but Jet does not
support "triggers").
To do what you want, you may have to move from Jet to an Access front-end to
a server database. The Desktop Edition of MS SQL Server is included with
Access, but must be installed separately. It will handle multiple users, the
number depending on factors including how many will be simultaneously
accessing the data. If you outgrow it, however, the Access front-end and
your data can immediately work with the commercial versions of Microsoft SQL
Server.
If you have applied Access security and forced user login, the CurrentUser
builtin function returns the UserID. If you want to use the network login
for a UserID, you'll need to use the API -- there is information and
examples at
http://www.mvps.org/access/api/api0008.htm and, for using the
computer name, at
http://www.mvps.org/access/api/api0009.htm.
Larry Linson
Microsoft Access MVP
falroy said:
Hello,
I am a new Access user/developer. My team at work has asked me to come up
a way to automatically paste a user ID into a field after the user has left
the record. We need a field that keeps a running diary of who has updated
the field, and retains all previous updates. Is there some VBA code that
would address this?
