MS Access Capture Active Directory of Windows Username write to table field

Here's a function that I use in many of my apps
OM Gang

Public Function GetUserInfo(strUserOrComputer As String) As String
'returns logged in userID or computername depending on which argument is passed
'pass user OR pass computer
On Error GoTo Err_GetLoggedInUser

    Dim objNet As Object
    Dim strResult As String

    Set objNet = CreateObject("WScript.Network")
    
        'check value of passed argument and retrieve value accordingly
    Select Case strUserOrComputer
        Case "user"     'retrieve UserName of logged in user
            strResult = objNet.UserName
            
        Case "computer" 'retrieve ComputerName of local machine
            strResult = objNet.ComputerName
            
        Case Else       'do nothing
        
    End Select

Exit_GetLoggedInUser:
        'destroy object variable
    Set objNet = Nothing
        'function return value
    GetUserInfo = strResult
    Exit Function

Err_GetLoggedInUser:
    MsgBox Err.Number & ", " & Err.Description, , "Error in Function GetLoggedInUser of Module basGlobalProcedures"
    Resume Exit_GetLoggedInUser

End Function

Select allOpen in new window

The returned UserName will be the AD account if the current user logged in with AD credentials.
OM Gang

omgang, thank you for quick response.  Do I put this as event code or a module?  I am familiar with event codes but still need to learn modules.  Sorry for dumb question. Can't wait to try this.  Thansk.

Place it in a standard module.
Create a new module.  You can name it whatever you want (Module1 is the default).
Now, whenever you want to retrieve the logged in username or computer name you can call it from a form event procedure (or in a query, etc.).

Say you have a form and you want to get the username at the time the form is opened/loaded.  Set an event procedure for the Form Load event.

Private Sub Form_Load()

    Dim strUser As String, strComputer As String

        'call public function to retrieve username and computer name
    strUser = GetUserInfo("user")
    strComputer = GetUserInfo("computer")

End Sub


And if you want to display the username and computer name in text boxes on your form you can do something like

Private Sub Form_Load()

    Dim strUser As String, strComputer As String

        'call public function to retrieve username and computer name
    strUser = GetUserInfo("user")
    strComputer = GetUserInfo("computer")

        'populate form controls with the username and computername
    Me.txtUserName = strUser
    Me.txtComputerName = strComputer

End Sub

If you have a query and you want to filter for records only belonging to the logged in user you can do somehting like
SELECT PositionLog.*, PositionLog.TransUserID
FROM PositionLog
WHERE (((PositionLog.TransUserID)=GetUserInfo("user")));

OM Gang

Thanks OM Gang.  Let me try to absorb this.  Thanks, again!

Does this require a compile?

If I want to store the user and computer values in the table based on the form do I need to something to the individual fields on the form?

You should compile your VBA project after code changes.  Also a good idea to add
Option Explicit
to each module (both form modules and standard modules) immediately after the
Option Compare Database statement
this will make sure you have all variables declared.
OM Gang

Is the form based upon the table you want to store the values in?
If so, the example I gave above will write the values to the form fields and save the values in the table.
OM Gang

I copied your function literally into a new module by clicking on modules, then new, paste.  I named it  "whoDidit".  I then went to my form which is based on the table I want to write to and on the form's On Load property I copied the text below into the Event Code Builder

"Private Sub Form_Load()

    Dim strUser As String, strComputer As String

        'call public function to retrieve username and computer name
    strUser = GetUserInfo("user")
    strComputer = GetUserInfo("computer")

End Sub"

I compiled the database.  
I open the form key in a transaction but do not see the information capture in the table.

Table fields I want to capture into are:  TranEnterBy and Computer.

Forgive me, I have not done but one module years ago.  This is something I really need to do and want to learn more about modules.  I appreciate  your help.  If there is a tutorial I should explore just let me know.  I think  you are pointing me in the direction of a solution but I just am having trouble implementing.  Thanks.

OMgang  I am getting it to display on the form now; just not write to the table.

Are the form controls you are writing the username and/or computername to bound to table fields?  To write the values to the table the form controls will either need to be bound to the table fields or you'll have to create a process/procedure for writing the values to the table.

What is the form name?
Is the form unbound or is it bound to a table/query?
If the form is bound to a table is it the same table you want to write the username and/or computername to?
OM Gang

The form field controls were unbound.  Fumbling around to figure it out.  I changed the form fields from bound to unbound.

Form name is Record Time 1st 40.
Form is bound to table.
It is the same table I want to write to.  I want to write username to TranEnterBy and computername to computer in table Attendance Data Simple Work 1st_40.

On your form, do you have controls/fields for TranEnterBy & computername?  These are the fields you should be populating.

Please post the event procedure you've created to populate the form fields.
OM Gang

I am not opposed to changing the names of those fields.

You don't need to change the names.  We just need to be sure you are populating the correct bound fields.
OM Gang

I have not gotten to that point.  I only have the procedure i copied from you on the form properties itself as I understood you.  I kept thinking I needed something done to the actual fields on the form but have not been able to get anything to work other than to display.  I have removed the procedures for display and have been trying to figure out how to write.  This is probably something simple I just don't get because it is definitely my first time.

In the Form OnLoad:

Private Sub Form_Load()
Dim strUser As String, strComputer As String

        'call public function to retrieve username and computer name
    strUser = GetUserInfo("user")
    strComputer = GetUserInfo("computer")
   
   'populate form controls with the username and computername
    Me.Username = strUser
    Me.ComputerName = strComputer
     
End Sub


Module:

Option Compare Database
Option Explicit
Public Function GetUserInfo(strUserOrComputer As String) As String
'returns logged in userID or computername depending on which argument is passed
'pass user OR pass computer
On Error GoTo Err_GetLoggedInUser

    Dim objNet As Object
    Dim strResult As String

    Set objNet = CreateObject("WScript.Network")
   
        'check value of passed argument and retrieve value accordingly
    Select Case strUserOrComputer
        Case "user"     'retrieve UserName of logged in user
            strResult = objNet.Username
           
        Case "computer" 'retrieve ComputerName of local machine
            strResult = objNet.ComputerName
           
        Case Else       'do nothing
       
    End Select

Exit_GetLoggedInUser:
        'destroy object variable
    Set objNet = Nothing
        'function return value
    GetUserInfo = strResult
    Exit Function

Err_GetLoggedInUser:
    MsgBox Err.Number & ", " & Err.Description, , "Error in Function GetLoggedInUser of Module basGlobalProcedures"
    Resume Exit_GetLoggedInUser

End Function

Nothing has been done to the properties of the bound fields on the form that are bound to the table, I believe.  I am displaying in the header of the form the username and computer name data in unbound txt controls I named "UserName" and "ComputerName".

I have now changed the table fields to username and computername but it didn't make any difference.  i will leave them as such, though.

Sorry I changed the names before I saw your post.  We'll leave them that way unless it is not a good practice.

Not a problem.  If you db doesn't have any confidential data you can upload/attach it.  I'll take a look, make the necessary changes, and I'll post it back so you can see what I did.
OM Gang

Do I need to do an event procedure on the form field control for my now named UserName field which is bound to the table?

No.  When the form loads is it always on a new record?  Or does it load with the first record in the table and the user can navigate through the records and make changes?  When do you want the username and computername to be written?  Only on creation of new records on the form?  Whenever the user edits an existing form record?
OM Gang

There is confidential data.  Let me make a pared down version.

Please do.  You can create a copy, empty the tables of all but a couple records each and make sure the records that remain have editied/made-up data.
OM Gang

Holiday is over :(

I have attached a concept db.  If you click on the new switchboard command and enter through the two prompts that come up you can enter a transaction for Clock Number 9000.  You will see the transaction post in the Attendance Data Simple_T table.  I had to pare down a very large database of confidential data quickly and didn't work through those two prompts that come up because they don't affect my end result here.  Can you work with this to help me?  Thanks.
db1.mdb

Thank you so much for your review.  I knew the fields in top were unbound and was trying to bind the other two.  I will try your code.  I was only playing with the date fields to test some formatting.  I actually did know they were reserved words.  However, if this code is posted you should point that out in case some reviews it with less experience in that area.  I will try your suggestions and provide feedback.  Thanks, again.

Thank you so much!!!  Works perfectly now.  I pretty much understood you but got all tripped up trying and kept changing things till it didn't work.  Who knows if I would have ever figured out what I had done.  Thanks!!!

Excellent.  Good luck with the rest of your project.
Thanks,
OM Gang