I need to track who is working in an access table for the duration of their time.

I have an access 2013 database with one form that users work in. The use this form to update one database.

The database has a login form that pulls from a users table to log them in and open the appropriate form for their level of access.

I want to put the username of whoever has logged into the database on that shift into each record they enter in the table.

I think I am close but making it harder than it is. I need to capture the userlogin variable and put it into the table they are using for every record they enter.

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:
you use this to get the username
dim strUser as string

or use the windows scripting function

Function GetUserName() As String
  Dim oName As Object
  Set oName = CreateObject("WScript.Network")
  GetUserName = oName.UserName
  Set oName = Nothing
End Function

Open in new window

Dale FyeOwner, Developing Solutions LLCCommented:
Then you need to add a field (UserID) to the table.  I would then add that field to your form (hidden) and set the ControlSource of that field to something like:


This way, their name will automatically be added to that field when a new record is created.
The GetUserName() function suggested by Rey gets the Windows login name.  If that isn't what you use to log on to your application, you need to get the name they entered on your login form.

Rather than closing the login form when you open the application form, simply hide it by setting its visible property to false.

Me.Visible = False

Then in the BeforeUpdate event of the form use something like:

Me.UpdateBy = Forms!yourloginformname!yourloginID
Me.UpdateDT = Now()

Don't forget to add "UpdateBy" and "UpdateDT" to the table.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

GjdaltonAuthor Commented:
PatHartman - I got the code in there but am getting a message about "Microsoft Access can't find the field "|1" referred to in your expression"

I looked it up and someone said i have to do a database lookup first on the database i want to put the data in. Hoping I don't have to do something like that but I also don't want to open the database exclusively to do this. I'm sure I can't update the table from the form and update the table from code as well.
Did you add the new columns to the table?
Did you include the new columns in the RecordSource query of the form where you put the code?
Did you put the code in the Form's BeforeUpdate event procedure?
Did you spell the column names correctly?  Using the Me. syntax gives you intellisense.  did you see it?

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
GjdaltonAuthor Commented:
It finally worked after some reworking. It was someone else's database that I had been charged with redoing. I just ended up rebuilding from the ground up and copying records. Completed it and tested your code and it worked!

You are welcome:)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.