473,320 Members | 1,848 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 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 4909
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()
  2.  
  3.     LastEditedBy = DLookup("User", "Logins", "UserName='" & User & "'")
  4.  
  5. End Sub
The above code isn't working.
Jul 23 '21 #2
NeoPa
32,556 Expert Mod 16PB
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_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
isladogs
455 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
NeoPa
32,556 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
vanzie
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.Edit
rs!UpdatedBy = Tempvars("Username")
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.
Aug 25 '21 #6
NeoPa
32,556 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
  3.  
  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

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

Similar topics

1
by: Galina | last post by:
Hello I am trying to create a table, which includes a field type LONG. The wizard hasn't allowed me to enter storage settings for this table. It set the initial size is 64K and not available to...
3
by: BlackFireNova | last post by:
This concerns an Access 2002 (XP) database. There are two fields, "Notes" (Memo Field) and "Notes Update" on a form (among others) which I am concerned with here. Problem: I need to be able...
3
by: Dave | last post by:
I've run across a strange error when creating a simple ADO.Net connection in VB.Net 2003 To replicate an error: Create an Access database (2002) with table named "Student" and fields SID...
0
by: Eduardo78 | last post by:
Ok, I will try to explain first the escenario. I am trying to concatenate 6 fields so they can be in the same row using a cross-tab Report. The thing is that two of these six fields are numeric...
2
by: abprules | last post by:
Is there a way that I can create a "Last Updated by" field in MS Access. I know how to do the Date Last Updated but can't seem to find out how to record the user who last updated the info. ...
8
by: Keith | last post by:
I try to learn SQL by figuring out things. I want to make a listing of all records that were changed in the last... 1, 6, 12 hours/days. I have a field called 'updated' managed like: UPDATE...
2
by: thedjnikki | last post by:
Hello all! I have created an inventory database on Access and I want to create a crystal report that shows an Invoice of only one set of fields. One item with its price, quantity, etc. I placed the...
4
by: kcopson | last post by:
I need a routine to add a new field to an existing table in a database that is in another town. Situation: I maintain a database with 10 copies in different offices. Everytime we want to make a...
1
by: teneesh | last post by:
We have a table that stores data from an evaluation. The table was created kind of funky. The evaluation data has 4 questions. So normally a table design like this would go as follows. One record...
17
by: Michael R | last post by:
Hi. I'm using Access 2010 x86 I'm trying to create a Multi Value Field in a table using DAO, with the following code: Sub Test1() Dim db As dao.Database, tdf As dao.TableDef, fld As Field Set...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

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.