asked on

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.

Thank

Rey Obrero (Capricorn1)

you use this to get the username
dim strUser as string
strUser=environ("username")

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 Fye

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:

=GetUserName()

This way, their name will automatically be added to that field when a new record is created.

PatHartman

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.

Gjdalton

ASKER

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.

ASKER CERTIFIED SOLUTION

PatHartman

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Gjdalton

ASKER

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!

Thanks

PatHartman

You are welcome:)