471,854 Members | 1,795 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,854 software developers and data experts.

Creating a "Last Updated By" field

I have created a login for my access database.
But I want, when I edit a form, for Access to automatically update the field based on the user that logged into the document.
Any ideas on how to achieve this?

I have a table that has the people that have a login for the document (if that helps at all).
Jul 23 '21 #1
6 4674
I created a login for an Access document.
I have a record that I want, for whoever that logs in, to update a "Last Updated By" field.
So, if someone edits a record the field will say who edited it (whoever is logged in to the document).

What would I put for the AfterUpdate code to make the form automatically update that specific field based on who logged into the document?

Also, not sure if this would help, I have a table with the people who have access to the document(the people who have a log in).
Expand|Select|Wrap|Line Numbers
  1. Private Sub Record_Name_AfterUpdate()
  3.     LastEditedBy = DLookup("User", "Logins", "UserName='" & User & "'")
  5. End Sub
The above code isn't working.
Jul 23 '21 #2
32,462 Expert Mod 16PB
Hi there :-)

But I want, when I edit a form, for Access to automatically update the field based on the user that logged into the document.
Any ideas on how to achieve this?
Try the following steps and see if that works for you. If not please respond with a clear explanation of why it doesn't and where it breaks.

To precis :
  1. Create a Field in the Table where you'd like to store the logged-on user data.
  2. Have a Control on the Form, where records for this Table are updated, that matches this Field but which is set as uneditable (Locked = True).
  3. In the Form's Form_AfterUpdate() event procedure (Not the Form_BeforeUpdate() one as that will cause the original update to fail - obviously.) add code to set the Control's Value to the value you have stored for the logged-on user.
  4. Trigger another save immediately. Bizarrely, in Access VBA, this is done by setting the Form's .Dirty property to False.
Jul 23 '21 #3
404 Expert Mod 256MB
Just to add in case its not already obvious.....
When a user logs in, save the username as a public string variable or TempVar so you can retrieve its value anywhere in the application such as the form being edited
Jul 23 '21 #4
32,462 Expert Mod 16PB
I merged this later post by the OP as it has some extra info in it.

@IslaDogs - Thanks for the report.

@HopkinsUni - Reposting the same issue in a separate thread isn't allowed but, as you can see, adding additional clarification posts in your original thread is. Hopefully you have your answer now anyway.
Jul 24 '21 #5
1 Bit
What I normally do is create a TempVars("Username") when a user logs into my database.

The user will create random records but when it needs to be edited, the record will be retrieved with the ID record (the automated ID number). I usually work with unbound forms since bound forms just save anything you do on it (there is a way for bound forms to stop automatic updating) and then include some code when the user clicks the save button. My knowledge of VBA is only fair but what I use works for me.

If you know how to work with RecordSets then just create a field in your table called LastEditedBy or UpdatedBy and add this code to the save button On Click event:

<Declare your recordset and strings first>

rs!UpdatedBy = Tempvars("Username")

It all depends on how your database is structured. If you are using bound forms, then I think a form After Update code will work such as above.
Aug 25 '21 #6
32,462 Expert Mod 16PB
Generally speaking I don't advise logging users into a database. As soon as you start down that road you become responsible for ensuring the bona fides of the user and that usually means storing passwords or hashes. Unless you're a genius that leaves you in the uncomfortable position of being responsible for security breaches rather than leaving such things to the O/S where a great deal more time and effort has been put into ensuring security and the passwords are stored outside of your database in the Active Directory.

This concept comes up a lot and I can say, without fear of contradiction, that all the other Access MVPs agree that to try to do it within Access is simple (and easily avoidable) folly.

Please don't see this as an attack on you personally. I would thank you for contributing your advice. I feel I would be remiss though, if I didn't warn you, and others that see that advice, that it is beyond unwise & actually quite dangerous.

As it's come up I suppose I should share how to use the easier / more reliable / certainly safer approach - Function to Return UserName (NT Login) of Current User. If you want to store that in a TempVar then feel free. Personally I'd store it in a Static variable within a Function Procedure which would only try to determine the value when it isn't already set. Something like :
Expand|Select|Wrap|Line Numbers
  1. Public Function GetOSUser()
  2.     Static strOSUser As String
  4.     If strOSUser = "" Then
  5.         'Code to get value from O/S and store in strOSUser.
  6.     End If
  7.     GetOSUser = strOSUser
  8. End Function
Aug 25 '21 #7

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

2 posts views Thread by abprules | last post: by
reply views Thread by YellowAndGreen | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.