Solved

automatically assign user when adding a new record

Posted on 2014-01-28
5
1,828 Views
Last Modified: 2014-01-28
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
Comment
Question by:CptPicard
5 Comments
 
LVL 10

Expert Comment

by:Gozreh
Comment Utility
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
 
LVL 2

Expert Comment

by:Christopher Reed
Comment Utility
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
 
LVL 24

Expert Comment

by:chaau
Comment Utility
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
 
LVL 34

Accepted Solution

by:
PatHartman earned 500 total points
Comment Utility
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
 

Author Closing Comment

by:CptPicard
Comment Utility
This works perfectly!

Thanks for everyone's help with this!
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now