Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

automatically assign user when adding a new record

Posted on 2014-01-28
5
Medium Priority
?
2,565 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 39

Accepted Solution

by:
PatHartman earned 2000 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

Enroll in October's Free Course of the Month

Do you work with and analyze data? Enroll in October's Course of the Month for 7+ hours of SQL training, allowing you to quickly and efficiently store or retrieve data. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

604 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