Solved

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

Posted on 2010-09-02
29
489 Views
Last Modified: 2013-11-27
I need to learn how to capture the active directory account preferably or windows account username and write to a table.  Form is used for data entry.  Can someone point me to a tutorial?  Is this hard to do?  Thanks.
0
Comment
Question by:gadgetgal
  • 16
  • 13
29 Comments
 
LVL 28

Expert Comment

by:omgang
ID: 33587346
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

Open in new window

0
 
LVL 28

Expert Comment

by:omgang
ID: 33587359
The returned UserName will be the AD account if the current user logged in with AD credentials.
OM Gang
0
 

Author Comment

by:gadgetgal
ID: 33587543
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.
0
 
LVL 28

Expert Comment

by:omgang
ID: 33587769
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


0
 

Author Comment

by:gadgetgal
ID: 33587938
Thanks OM Gang.  Let me try to absorb this.  Thanks, again!
0
 

Author Comment

by:gadgetgal
ID: 33588169
Does this require a compile?
0
 

Author Comment

by:gadgetgal
ID: 33588468
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?
0
 
LVL 28

Expert Comment

by:omgang
ID: 33588497
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
0
 
LVL 28

Expert Comment

by:omgang
ID: 33588521
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
0
 

Author Comment

by:gadgetgal
ID: 33589747
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.


0
 

Author Comment

by:gadgetgal
ID: 33590260
OMgang  I am getting it to display on the form now; just not write to the table.
0
 
LVL 28

Expert Comment

by:omgang
ID: 33590381
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
0
 

Author Comment

by:gadgetgal
ID: 33590595
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.
0
 
LVL 28

Expert Comment

by:omgang
ID: 33590652
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
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 

Author Comment

by:gadgetgal
ID: 33590664
I am not opposed to changing the names of those fields.
0
 
LVL 28

Expert Comment

by:omgang
ID: 33590742
You don't need to change the names.  We just need to be sure you are populating the correct bound fields.
OM Gang
0
 

Author Comment

by:gadgetgal
ID: 33590745
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.
0
 

Author Comment

by:gadgetgal
ID: 33590832
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.
0
 

Author Comment

by:gadgetgal
ID: 33590844
Sorry I changed the names before I saw your post.  We'll leave them that way unless it is not a good practice.
0
 
LVL 28

Expert Comment

by:omgang
ID: 33590917
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
0
 

Author Comment

by:gadgetgal
ID: 33590936
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?
0
 
LVL 28

Expert Comment

by:omgang
ID: 33591012
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
0
 

Author Comment

by:gadgetgal
ID: 33591057
There is confidential data.  Let me make a pared down version.
0
 
LVL 28

Expert Comment

by:omgang
ID: 33591103
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
0
 

Author Comment

by:gadgetgal
ID: 33617470
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
0
 
LVL 28

Accepted Solution

by:
omgang earned 500 total points
ID: 33618817
A few of things.
The fields username and computername in the form header section are unbound.  Writing to these fields will not save the data values to your table.
You have two fields in the form detail section that are bound to the table fields ComputerName and UserName.
Form field Text49 is bound to ComputerName
Form field Text53 is bound to UserName
Two write to these form fields on the Form_Load event you need to refer to them by Name:
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
    Me.Text53 = strUser
    Me.Text49 = strComputer
     
End Sub

You have an AfterUpdate event on the form that calls a macro.  The macro does two things:
1) appends data from the table ATTENDANCE DATA SIMPLE WORK 1st 40_T to table Attendance Data Simple_T
2) Deletes all records from the table ATTENDANCE DATA SIMPLE WORK 1st 40_T

Table Attendance Data Simple_T doesn't have fields for ComputerName and UserName so these values are not recoded here.  Since you are deleting the records from the first table the ComputerName and UserName values are lost when the macro runs.

Perhaps you need to add ComputerName and UserName to the Attendance Data Simple_T table and include those fields in the append query?

Another thing, you have fields named [Date] in both tables.  Date is a reserved word in Access and VBA, e.g. you can set the value of a field or variable to todays date by simply calling the native Date function.  You should rename the field to something like transDate, or attDate, etc.

OM Gang
0
 

Author Comment

by:gadgetgal
ID: 33618939
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.
0
 

Author Closing Comment

by:gadgetgal
ID: 33619077
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!!!
0
 
LVL 28

Expert Comment

by:omgang
ID: 33619360
Excellent.  Good luck with the rest of your project.
Thanks,
OM Gang
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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…

707 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now