Creating a "Last Updated By" field

[HopkinsUni]

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).

[HopkinsUni]

Creating a "Last Updated By" field

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).

Private Sub Record_Name_AfterUpdate()

    LastEditedBy = DLookup("User", "Logins", "UserName='" & User & "'")

End Sub

The above code isn't working.

[NeoPa]

Hi there :-)

HopkinsUni:
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_AfterUpdat e() event procedure (Not the Form_BeforeUpda te() 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.

[isladogs]

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

[NeoPa]

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.

[vanzie]

What I normally do is create a TempVars("Usern ame") 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.Edit
rs!UpdatedBy = Tempvars("Usern ame")
rs.Update

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.

[NeoPa]

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 :

Public Function GetOSUser()
    Static strOSUser As String

    If strOSUser = "" Then
        'Code to get value from O/S and store in strOSUser.
    End If
    GetOSUser = strOSUser
End Function