You are here:

Using MS Access/Adding data to field, not replacing


Hello Scott,

I found a function on this site for retrieving the computer user's signon name, it works great and I use it to see who entered records. I want to use it to see who modifies records also. When the user exits a text box the following code executes;

Private Sub Revision_Exit(Cancel As Integer)
   [QCUserName] = fOSUserName  'fOSUserName is function for user name
End Sub

which puts the user's name in the table field QCUserName. On another form where the record can be revised when the user exits the text box I want to have the second user's name added to the field, not replace the user name that is already there. The field would like this;

At initial data entry field would have - ScottGem
At revision field would have - ScottGem, RichardRost

and would keep adding new user names as many times as record is revised. I would appreciate some help as how to do this.

Thank you, Ed

I'll answer your question, then tell you why I wouldn't do it that way.

Private Sub Revision_Exit(Cancel As Integer)
  [QCUserName] = [QCUsername] & ", " & fOSUserName  
End Sub

This will add the current user name to whatever is currently in the control on your form.

Now what happens if the user tabs in and out of the control (you don't have fields on a form, you have controls that may or may not be bound to a field in a table)? You have a false reading. Or a user tabs out, then realizes he forgot to change something and goes in and out again.

If you want an audit trail of who edited a record, then create a complete audit trail. My blog has instructions on how to setup an audit trail on the table level. There are many resources available that show how to create an audit trail on the form level.

But at the very least, you should have a separate table that should record the PK of the edited record, the username of the editor and the data and time it was edited.

The following will do that:

Private Sub Revision_Exit(Cancel As Integer)
Dim strSQL As String

strSQL = "INSERT INTO tblEdits (RecordID, Username, EditedWhen) " & _
        "VALUES(" & Me.ID & ", '" & fOSUsername() & ", #" & Now() &"#);"
CurrentDB.Execute strSQL

End Sub

Me.ID is the name of the control bound to your Primary Key. You could expand this to serve multiple tables, by adding a table name.

Hope this helps,
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA

Using MS Access

All Answers

Answers by Expert:

Ask Experts




I can answer almost all types of questions relating to Microsoft Access usage and application design. My strengths are database and interface design.


I've been designing databases for over 25 years working with dBase, FoxPro, Approach and Access.

Author of Microsoft Office Access 2007 VBA
Techncial Editor for Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports & Queries From Que Publishing

Brooklyn College BA

©2017 All rights reserved.