Microsoft Access 2000: Record Last Edited By?

Is there a way to indicate which user edited a record last?

I have my database setup so I can tell which user created a record (=CurrentUser()). However, records are often edited by other users after it was created... I would like to be able to see which user (users if possible) edited a record last.

Thank you in advance.

 

Couldn't you use the same code to save the editor's name?

MBN

 

How?

To save the creater of the record I create a field in the table with the name "SystemUser". I leave the type as "text".

In the form I add the "SystemUser" field, and make the "Default Value" "=CurrentUser()".

Access saves the name of the user that created the record... How do I go about making it save the name of the last person that edited it?

 

One option is instead of putting your =currentUser() in the Default Value, choose an Event.

Specifically, in the Properties of the form itself, choose AfterUpdate event and type in to look like the following:

Private Sub Form_AfterUpdate()
SystemUser = currentUser()
End Sub

Have you ever worked with the Code builder? That is what you want to use.

Let me know how you make out.

MBN

 

Sorry for taking so long to reply... I've been working in the field these last few weeks... In any event... Here is a reminder of my original question... In addition to knowing the person that created a record, I also want to know the last person that edited it.

In response to your post, I removed the "=CurrentUser()" from the Default Value of my "SystemUser" field [Which tells me the person that created the record] and added the code you listed in properties of the form (AfterUpdate). However, this didn't work. Access wouldn't save the record.

When that failed, I tried to leave the "=CurrentUser()" in the default value of my "SystemUser" field, then created a new field called "LastEditedUser" and I added the code you listed to the properties of the form... However, I changed "SystemUser" to "LastEditedUser". Unfortunately this didn't work either. I received the following message:

The expression After Update you entered as the event property setting produced the following error: Ambiguous name detected: Form_AfterUpdate

*The expression may not result in the name of a macro, the name of a user-defined function, or [Event Procedure].
*There may have been an error evaluating the function, event, or macro

Any other ideas? I hope so...

 

You got that error because I used the form name "Form" because I don't know what your form's name is...

Just replace the word Form in the line Private Sub Form_AfterUpdate() with the name of your form.

One question: do you need to save the information on BOTH who created the record and who edited it? If so, then make sure you have another field in your table called "LastEditedUser". If you don't need both but just need to know the user responsible the for latest activity, then you might want to go back and do it as I showed at the very beginning. (that example over-wrote the info on who created the record with who edited last).

MBN

 

I'm trying to keep both pieces of information... The person that created the record, and the last person to edit it.

In addition to the "=CurrentUser()" for the Default Value of my "SystemUser" field, the code for the last person to edit the record is listed below:

Private Sub VendorInvoices_AfterUpdate()
LastEditedUser = CurrentUser()
End Sub

The problem is, nothing happens. I don't get any error messages, but it doesn't show me the last person that edited the record either.

 

Two possible reasons, try both:

1. That should be the BeforeUpdate event--you want Access to insert the current event just before it saves the record, not after.

2. You added the code to the form, but didn't link it back to the form properties. In design view, select the grey box in the upper left-hand corner; open the properties window; make sure that the After Update (or Before Update) line is showing "[Event Procedure]"

You can test whether or not the procedure is running properly by putting a break line in it. select the code line that says "LastEditedUser = CurrentUser()", and hit F9. You'll see a red dot in the margin. Close the VBA editor, and run the procedure; if it hits that line, the VBA editor will reopen and stop there. (This is part of "stepping through" a procedure, which is invaluable to debugging, which is what you're now doing.)

 

I placed the code "Before Update" and "After Update" but I didn't see "[Event Procedure]".

I selected "LastEditedUser = CurrentUser()" and hit F9 as you suggested... The red dot appeared... However, you stated "Close teh VBA editor, and run the procedure..." I'm not sure I understand you... How do I "run the procedure" after I close the VBA editor?

 

------------------------------------------------
Should you not be able to FORCE a logon for each user each time they use the application and have the application use that info to fill in a field for a modified record? Or even a lookup for that matter?

JESSE

 

My database contains "User Level Security" so each user must login upon using the application. Likewise, the system uses the login info to record the user that created a record (SystemUser: =CurrentUser()).

Access is also able to record the user that "last edited a record" in addition to recording the user that "created the record".

I'm already recording the user that create a record... My problem is getting the system to record the last user that "edited a created record".

 

Any other suggestions???

 

Just wanted to give everyone a heads-up... I used the following code to record the last person that edited a record (at the same time, recording the person that created the specified record).

To record the end-user that created the record I used: =CurrentUser()

To record the end-user that last edited a specified record I used the following code (insert it as "BeforeUpdate" in the form properties):

---------------------------------------------------
Private Sub Form_BeforeUpdate(Cancel As Integer)
LastEditedUser.Value = CurrentUser()
End Sub
------------------------------------------------------

"LastEditedUser" is the name of my text field. Thanks everyone for your help!!!