Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2988
  • Last Modified:

automatically assign user when adding a new record

Using Access 2010.  Wondered if there's a way that when a user clicks the 'Add Record' button to add a new record, it could automatically place their name into a field called 'user' which is invisible to the user but is used so as to keep a record of which user is adding the data.  I could therefore create a report to show which users have added the data.  I'd rather it be automatic rather than the user having to select their own username each time they add a new record if that's at all possible.  I'm assuming you'd need a login form first where the user gets to choose their username (but no password).  It would have to some how remember their username for every form they access and add data too.  Is this possible?
0
CptPicard
Asked:
CptPicard
1 Solution
 
GozrehCommented:
You should create a table (in the front end) with field UserID (or UserName), so when the user will login you will update that

Then on each form BeforeUpdate if Me.NewRecord add the UserID to that record.
0
 
Christopher ReedLevel 2 Software Support EngineerCommented:
Yes, it is possible...

You are correct in the fact they would need a Login form so that Access recognizes the current user.  Here is something to get you started:

SELECT ID, [Employee Name] FROM [Employees Extended] ORDER BY [Employee Name];

So the field would initially show the selected user (from the Login form) and allow for your to choose any other employee from the table.

As far as hiding the field, it's just a matter for setting the value of "Visible" to 'No'.

It's been a while since I've used Access.  I'm going to go fire it up and see if I can get you more information.

-Chris
0
 
chaauCommented:
What about using the system username the user has logged on with? The username can be obtained using the Environ function:
Environ("username")

Open in new window

If domain is also required, you can use:
Environ("domainname") & "\" & Environ("username")

Open in new window

Create a new column, called UserName in your table. In the event that adds new record (button clicked event for 'Add Record' button) add this code (provided you have a RecordSet called rs):
rs.UserName = Environ("domainname") & "\" & Environ("username")

Open in new window

If you have troubles with that, send us the 'Add Record' function, and we will help
0
 
PatHartmanCommented:
There is no reason to open a recordset to do this.  Just include the ChangeBy and ChangeDT columns in the form's RecordSource.  They don't need to be bound to controls.  They just need to be included in the RecordSource query.

Add ChangeBy and ChangeDT to every table where you want to log the last change.  This isn't an audit log, it just tells you who made the last change and when.  An audit log is much more complicated.

Then in the FORM's BeforeUpdate event:

Me.ChangeBy = Environ("UserName")
Me.ChangeDT = Now()

If you have a login form, instead of closing the login form when you open your menu, just hide it:
Me.Visible = False
That way you can reference it from any place in the application at any time.  So instead of using the Environ() variable, you can use the ID from the login form.

Me.ChangeBy = Forms!frmLogin!txtUsername
0
 
CptPicardAuthor Commented:
This works perfectly!

Thanks for everyone's help with this!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now