Solved

automatically assign user when adding a new record

Posted on 2014-01-28
5
2,150 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 10

Expert Comment

by:Gozreh
ID: 39816133
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
ID: 39816152
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 25

Expert Comment

by:chaau
ID: 39816168
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 36

Accepted Solution

by:
PatHartman earned 500 total points
ID: 39816194
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
ID: 39816271
This works perfectly!

Thanks for everyone's help with this!
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
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…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

726 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