Solved

automatically assign user when adding a new record

Posted on 2014-01-28
5
2,266 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 37

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

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
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…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

688 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