Tech Support Guy banner
  • Please post in our Community Feedback thread for help with the new forum software! If you are having trouble logging in, please Contact Us for assistance.
Status
Not open for further replies.
1 - 13 of 13 Posts

·
Registered
Joined
·
190 Posts
Discussion Starter · #1 ·
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.
 

·
Registered
Joined
·
190 Posts
Discussion Starter · #3 ·
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?
 

·
Registered
Joined
·
2,016 Posts
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
 

·
Registered
Joined
·
190 Posts
Discussion Starter · #5 ·
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...
 

·
Registered
Joined
·
2,016 Posts
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
 

·
Registered
Joined
·
190 Posts
Discussion Starter · #7 ·
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.
 

·
Registered
Joined
·
941 Posts
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.)
 

·
Registered
Joined
·
190 Posts
Discussion Starter · #9 ·
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?
 

·
Registered
Joined
·
55 Posts
keith10456 said:
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.
------------------------------------------------
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
 

·
Registered
Joined
·
190 Posts
Discussion Starter · #11 ·
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".
 

·
Registered
Joined
·
190 Posts
Discussion Starter · #13 ·
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!!!
 
1 - 13 of 13 Posts
Status
Not open for further replies.
Top