Lookup User Full Name based on Login Name

[Steve@trop]

Our company uses ID numbers instead of names as the Network Login ID. I have a user-defined function that uses the LoginID as the default in a field on my form. The code is below:

Public Function LoginName()

LoginName = CreateObject("wscript.network").UserName

End Function

I just put =LoginName() in the default and it does the trick. Unfortunately, that isn't as useful as it should be because it returns a number instead of a name. I have a table (OperatorT) with the FullName and UserID and I'd like to have the default be the full name instead of the number.

I've tried several ways of putting in the expression in the default field but it keeps telling me that the syntax is incorrect. I came close to what I want by putting this query in the RowSource:

SELECT OperatorT.FullName FROM OperatorT WHERE (((OperatorT.UserID)=LoginName()));

This puts the numerical ID in the field but if you click on it, the FullName appears in a drop-down when you click it. This makes me think I'm close but am missing something simple. I'd like to have it skip the ID and put in the name instead. Perhaps I need to put something in the Event tab?

Thanks,

Steve

 

[way2bord]

Replace this line:

LoginName = CreateObject("wscript.network").UserName

with these lines:

a = CreateObject("wscript.network").UserName
LoginName = dlookup("Fullname","OperatorT","OperatorT.UserID = " & a)

 

[Solo712]

Replace this line:


with these lines:

[B]a = CreateObject("wscript.network").UserName[/B]
LoginName = dlookup("Fullname","OperatorT","OperatorT.UserID = " & a)

I'll be greatly surprised if you do not get Error 424 or some such on the bolded line.

Best,
Jiri

 

[way2bord]

I'll be greatly surprised if you do not get Error 424 or some such on the bolded line.

Best,
Jiri

Prepare to be AMAZED!
Note: the return is a string, it's not necessary to set a reference pointer to the object.

 

[Steve@trop]

I tried the suggested change and it didn't work. I'm working on a different way to accomplish this but I'm stuck again....

I have the following query:

SELECT OperatorT.UserID, OperatorT.FullName
FROM OperatorT
WHERE (((OperatorT.UserID)=LoginName()));

It works fine as a query by itself. It pulls up two fields that are part of one record in the OperatorT table. =LoginName() is a public function that returns the Network Login ID of the person logged in to the computer. I want to use it to look up the full name based on the Login ID because they use numbers for Login ID's here.

How can I change this so that I can put it as the default value of control on a form? When I try it keeps saying that the subquery needs parentheses. I probably need to isolate the function somehow. Is that correct? I tried putting quotes and brackets around the function but it still didn't work. I'll get the hang of punctuation in SQL and VBA some day!

Thanks,

Steve

 

[Steve@trop]

I thought I'd post a screen shot of what I'm getting now with this issue. The Login ID number appears in the combo box and the username is there when you click the drop-down. The wierd thing is that each one of those is from a different field of the table (OperatorT.username and OperatorT.FullName). Is there a way to get this to show the fullname so the user doesn't need to click the drop-down? Having it be right there under the drop-down makes me believe that I'm really close to the solution but I've so far been unable to figure it out.

Thanks,

Steve

 

[Steve@trop]

I just figured out that the LoginID that is appearing first is from the default value that I entered. When I remove the default from the field it appears blank until I click the drop-down and then the full name appears. That simplifies things. I know the query in the rowsource is working correctly then. Now the trick is to get the fullname to appear in the combo box without having to click the drop-down.

I'd use a textbox instead of a combobox but there isn't a place for the rowsource in a textbox.

 

[Galaxiom]

Rather then translating the number into a name in your database, you could use an LDAP query to get any of the information recorded for the user in Active Directory.

 

[Steve@trop]

Thanks Galaxiom,Could you post some info or perhaps a link on how to do an LDAP query? Thanks,Steve

 

[Solo712]

I just figured out that the LoginID that is appearing first is from the default value that I entered. When I remove the default from the field it appears blank until I click the drop-down and then the full name appears. That simplifies things. I know the query in the rowsource is working correctly then. Now the trick is to get the fullname to appear in the combo box without having to click the drop-down.

I'd use a textbox instead of a combobox but there isn't a place for the rowsource in a textbox.

IIUC you do not really need a combobox at all. The value in the "Operator" is the name of the person logged into the network on that particular machine. Right ? It will not vary, ergo what you need to do is ...


[CAUTION: AIRCODE !]

'On top of form's module:
 
Public glbOperator as String
 
'in OnLoad
 
Dim LogInfo as Object
Dim a as String (or whatever the OperatorT.UserID datatype is)
:
Set LogInfo = CreateObject("wscript.network")
a = LoginInfo.UserName
 
' note that if OperatorT.UserID is not a string, need to remove the inner quotes 
glbOperator = Nz(Dlookup("Fullname","OperatorT","OperatorT.UserID = '" & a & "'"))
:
Set LogInfo = Nothing

then in OnCurrent event presumably when Me.NewRecord = True
assign the glbOperator to the operator textbox (field can be bound or unbound), ie Me!MyOpField = glbOperator

I hope that is all there is to it.

Best,
Jiri

 

[Steve@trop]

Thanks Jiri,

What's AIRCODE? I haven't heard that term before.

Thanks,

Steve

 

[Solo712]

Thanks Jiri,

What's AIRCODE? I haven't heard that term before.

Thanks,

Steve

You are welcome. "Aircode" describes code that has not been tested. It's just a theoretical swag.

Best,
Jiri

 

[Galaxiom]

Thanks Galaxiom,Could you post some info or perhaps a link on how to do an LDAP query? Thanks,Steve

I had to get information from multiple locations and experiment a lot before I worked it out. It wasn't well documented online when I first wrote the code in 2010.

Once the connection to the LDAP server is established the general syntax of a simple LDAP query is:

SELECT somefield FROM 'LDAP://ServerName' WHERE objectCategory= 'somecategory' AND someattribute='somevalue'

ServerName can be substituted with the Domain's Default Naming context. This can be looked up for the current user. I have used this technique in the code below.

Note that SELECT * is not supported in LDAP. Each field name to be returned must be specified individually.

The following function should return the Active Directory name information from the LoginName assuming they are on the same domain as the computer where the code is running.

I have slightly modified the code from what I know works with Access 2007 on Windows XP but I am not on a domain right now so I can't retest it. I will check it when I am back at work. (Tested and spelling of adCmdText constant fixed.)

I have used Late Binding throughout so no additional references should be required.

If no LoginName is provided the current user will be looked up.

Public Function UserFullName(Optional ByVal LoginName As String) As String
 
Const adCmdText = 1
 
Dim ADConn As Object         'ADODB Connection
Dim ADCommand As Object   'ADODB Command
Dim ADUser As Object
Dim DomainDN as String
 
If LoginName = vbNullString Then
    LoginName = CreateObject("wscript.network").UserName
End If
 
DomainDN = CreateObject("LDAP://rootDSE").Get("defaultNamingContext")
 
Set ADConn = CreateObject("ADODB.Connection")
    With ADConn
        .Provider = "ADsDSOObject"
        .Open "Active Directory Provider"
    End With
 
Set ADCommand = CreateObject("ADODB.Command")
    With ADCommand
        .ActiveConnection = ADConn
        .CommandType = adCmdText
        .CommandText = "SELECT givenName, SN, CN FROM 'LDAP://" & DomainDN & "' WHERE objectCategory='User' AND sAMAccountName ='" & LoginName & "'"
    End With
 
Set ADUser = ADCommand.Execute
 
    With ADUser
        UserFullName = !givenName & " " & !SN & " - " & !CN
    End With
 
    ADConn.Close
 
Set ADConn = Nothing
Set ADCommand = Nothing
Set ADUser = Nothing
 
End Function

The code is easily modified to return other attributes by changing the fields returned by the command and the assignment to the returned string.

However, Active Directory holds some attributes such as Group memberships as multivalue fields. This adds further complexity to to the task. I have the code to return group memberships too if anyone is interested but I would like to tidy it up and test it before posting.

This page had good introductory information about the most common attributes and how they relate to Active Directory settings:
http://www.computerperformance.co.uk/Logon/LDAP_attributes_active_directory.htm#LDAP_Attribute_

This page has a comprehensive list of LDAP user attributes.
http://www.kouti.com/tables/userattributes.htm

 

[Steve@trop]

Thanks Galaxiom,

I appreciate your hard work on this. It's one of those things that I believe should be alot easier to accomplish!

I pasted the funciton straight from your posting and didn't make any changes. Then I tried it out in a new form with a textbox. I put the =UserFullName() function in as the default value. Was I supposed to enter anything relating to this domain?

I got a runtime error:

Run-time error '3001':

Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.


When I hit "Debug" this line is in yellow:

.CommandType = adCmdText

Beyond that I don't know how to troubleshoot this.

Steve

 

[Steve@trop]

I found a workaround that I can use until and unless I can get it to look up the full name with the LDAP query.

I have an operator table with the LoginID's and full names of the people that will be entering data. I have the combo box set up so that it uses the LoginID to find the full name of the Operator. The problem I was having with this approach was that the user had to click the drop-down to choose their name (it was the only choice though). I found that I can force the drop-down to choose that first (and only in this case) item in the drop-down using Me.cboOperator = Me.cboOperator.ItemData(0).

The problem with doing it this way is that someone is going to have to keep that operator table up to date in order for it to work properly. If I can do it with LDAP, nobody is going to need to do anything to keep it up to date.

Thanks,

Steve

 

[Galaxiom]

I have corrected the declaration of the adCmdText constant and tested.

Works fine for me on Windows 7. Domain controllers are Server 2003 R2.

The function will return the current user when no arguments are provided. Adding the user login name as a string will look it up for that user.

 

[Steve@trop]

That works like a charm! Thanks!

Now I don't have to worry about the lookup table. It pulls the full name from AD.

Thanks again!

Steve

 

[Steve@trop]

I started a new database project, copied the module into it and when I use it as the default in a textbox I get #Name? instead of the username. I'm thinking I'm forgetting something basic as it works fine in my other database. Perhaps it has something to do with security settings? I'm doing this from the same machine with the same copy of Access. Anybody have any ideas why this doesn't work?

 

[Galaxiom]

The textbox's ControlSource will need to start with an equal sign. Otherwise it looks for a field in the form's recordset.

I assume you worked out how to edit the function return to get just the attributes what you wanted.

I included the firstname, lastname and fullname in the function as examples.

 

[Steve@trop]

I figured it out! I named the module itself "UserFullName". In the database where it worked, the module was named "UserID". I deleted the module, recreated it and named it "UserID" and now it works! I think it didn't like that the name of the module was the same as the name of the public function.

Yes, I did figure out how to edit the function to get the attributes I want.

Thanks again for your help.

Steve