1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Microsoft Access 2000: Record Last Edited By?

Discussion in 'Business Applications' started by keith10456, Apr 8, 2004.

Thread Status:
Not open for further replies.
Advertisement
  1. keith10456

    keith10456 Thread Starter

    Joined:
    Mar 17, 2004
    Messages:
    190
    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.
     
  2. MustBNuts

    MustBNuts

    Joined:
    Aug 21, 2003
    Messages:
    2,016
    Couldn't you use the same code to save the editor's name?

    MBN
     
  3. keith10456

    keith10456 Thread Starter

    Joined:
    Mar 17, 2004
    Messages:
    190
    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?
     
  4. MustBNuts

    MustBNuts

    Joined:
    Aug 21, 2003
    Messages:
    2,016
    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
     
  5. keith10456

    keith10456 Thread Starter

    Joined:
    Mar 17, 2004
    Messages:
    190
    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...
     
  6. MustBNuts

    MustBNuts

    Joined:
    Aug 21, 2003
    Messages:
    2,016
    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
     
  7. keith10456

    keith10456 Thread Starter

    Joined:
    Mar 17, 2004
    Messages:
    190
    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.
     
  8. downwitchyobadself

    downwitchyobadself

    Joined:
    Oct 13, 2000
    Messages:
    941
    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.)
     
  9. keith10456

    keith10456 Thread Starter

    Joined:
    Mar 17, 2004
    Messages:
    190
    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?
     
  10. Blue2

    Blue2

    Joined:
    Apr 23, 2004
    Messages:
    55
    ------------------------------------------------
    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
     
  11. keith10456

    keith10456 Thread Starter

    Joined:
    Mar 17, 2004
    Messages:
    190
    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".
     
  12. keith10456

    keith10456 Thread Starter

    Joined:
    Mar 17, 2004
    Messages:
    190
    Any other suggestions???
     
  13. keith10456

    keith10456 Thread Starter

    Joined:
    Mar 17, 2004
    Messages:
    190
    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!!!
     
  14. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/218478

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice