Results 1 to 6 of 6
  1. #1
    Lounger
    Join Date
    Jul 2003
    Location
    Burnaby, Br. Columbia, Canada
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Reading from Active Directory in a form (Word VBA)

    Hello, I'm writing some code that reads some user attributes out of Active Directory and populates a user form with the attribute values. Previously I have always done this by populating the user form with values from the Registry, so this is new to me. I have the code working to read all the attributes and just a particular attribute, and it runs just fine as long as it is not called from within the user form code. For example, the following sub works:

    Public Sub test()
    'gets the login ID of the logged in user, passes it to a public variable - works fine
    basUserADInfo.GetLoginUserName
    'Parameters are the public var for the login ID, and the AD attr name; it returns public var sAttr which contains the value of the AD attr
    basUserADInfo.GetUserAttr LoginUserName, "firstname"
    MsgBox sAttr 'displays the value just fine
    End Sub

    The code which gets the attribute - basUserADInfo.GetUserAttr - follows below:

    Public Function GetUserAttr(LoginName As String, sAttrib As String) As String
    'PURPOSE: Display information that is available in the Active Directory about a given user

    Dim conn As New ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim oRoot As IADs
    Dim oDomain As IADs
    Dim sBase As String
    Dim sFilter As String
    Dim sDomain As String
    Dim sAttribs As String
    Dim sDepth As String
    Dim sQuery As String
    Dim user As IADsUser

    On Error GoTo ErrHandler:

    'Get user Using LDAP/ADO
    Set oRoot = GetObject("LDAP://rootDSE")
    'Work in the default domain
    sDomain = oRoot.Get("defaultNamingContext")
    Set oDomain = GetObject("LDAP://" & sDomain)
    sBase = "<" & oDomain.ADsPath & ">"
    'Only get user name requested
    sFilter = "(&(objectCategory=person)(objectClass=user)(SAMac countName=" & LoginName & "))"
    sAttribs = "ADsPath"
    sDepth = "SubTree"
    sQuery = sBase & ";" & sFilter & ";" & sAttribs & ";" & sDepth
    conn.Open "Data Source=Active Directory Provider;Provider=ADsDSOObject"
    Set rs = conn.Execute(sQuery)

    If Not rs.EOF Then
    Set user = GetObject(rs("ADsPath"))
    With user
    sAttr = user.Get(sAttrib)
    End With
    End If

    ErrHandler:

    On Error Resume Next
    If Not rs Is Nothing Then
    If rs.State <> 0 Then rs.Close
    Set rs = Nothing
    End If

    If Not conn Is Nothing Then
    If conn.State <> 0 Then conn.Close
    Set conn = Nothing
    End If

    Set oRoot = Nothing
    Set oDomain = Nothing

    End Function

    Now, the code I am using to populate the user form is:

    Private Sub UserForm_Initialize()
    Dim ctlText As Control
    basUserADInfo.GetLoginUserName
    basUserADInfo.GetUserAttr LoginUserName, "firstname"
    txtFirstName.Value = sAttr
    End Sub

    I actually had this setup so the basUserADInfo.GetUserAttr procedure was a function that returned the attribute value, so I could run the following code, which cycles through all my tagged controls (which are named the same as the AD attributes) and populates them:

    For Each ctlText In Me.Controls
    If ctlText.Tag <> "" Then
    ctlText.Value = basUserADInfo.GetUserAttr(LoginUserName, ctlText.Tag)
    End If
    Next
    Set ctlText = Nothing

    But, when that didn't work I started simplifying the code (to what I have at the top of this post) in an effort to figure out what wasn't working. Finally, I got the code so darn simplified, as you can see, and it works like a charm unless I try to call it from the user form...so I am at a loss.

    I hope someone out there can point out what I am doing wrong. I just hate ending the day stuck!

    Thanks to all you gurus,
    Karina

  2. #2
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Reading from Active Directory in a form (Word

    First, you shouldn't need the name of the module pre-pended to the name of the function. I don't know whether it makes any difference to have it there, but try without it.

    Second, are you sure LoginUserName is the correct string to pass to the function? It's not clear to me where that is being assigned. Perhaps it is out of scope in the UserForm (i.e., private to your other code module)?

  3. #3
    Lounger
    Join Date
    Jul 2003
    Location
    Burnaby, Br. Columbia, Canada
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Reading from Active Directory in a form (Word

    Hi, the LoginUserName is a public variable, declared as such at the beginning of the module. If I had omitted 'Public' I could see it not being in scope for the user form, as it would only be a module-level declaration, but I did declare it as 'Public'. I pre-pended the function name with the module name out of habit only (a few people read my code later on and it helps them to easily recognize that as a function or procedure). I can try on Monday to run the code without it, but, as I always do this, I tend to agree that it 'shouldn't' make any difference. But it can't hurt to try!

    Any other ideas on what could be going on?

  4. #4
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Reading from Active Directory in a form (Word

    It's tough reading code on a mobile phone... what exactly is the problem/error when you run the code from inside a UserForm? If you are getting into the error handler, it would be useful to Debug.Print both the VBA err.number etc. and any ADO error numbers.

  5. #5
    Lounger
    Join Date
    Jul 2003
    Location
    Burnaby, Br. Columbia, Canada
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Reading from Active Directory in a form (Word

    Hiya again, I tried creating a brand new user form, with only one textbox in it, and the code I am calling from that user form is:

    Private Sub Test()
    basUserADInfo.GetLoginUserName
    basUserADInfo.GetUserAttr LoginUserName, "firstname" 'Which returns a public var 'sAttr' which should contain the attribute value
    txtName.Value = sAttr
    End Sub

    When I run this code, it doesn't generate an error message, it just displays the user form with no data in the textbox.

    However, if I run this code:

    Public Sub Test()
    basUserADInfo.GetLoginUserName
    basUserADInfo.GetUserAttr LoginUserName, "firstname"
    MsgBox sAttr
    End Sub

    From the module basUserAdInfo module, then the msgbox displays with the correct attribute value in it. So, I know the public var sAttr is getting the data, I just can't seem to call it and return the same data from a user form. I'll keep looking to see if something isn't in scope, but considering I am running the code, and just returning the one public variable, I can't imagine what that could be.

    As always, I appreciate your thoughts and ideas.

  6. #6
    Lounger
    Join Date
    Jul 2003
    Location
    Burnaby, Br. Columbia, Canada
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Reading from Active Directory in a form (Word

    Hiya again,

    I managed to get it working! I stripped all the code out of that user form and left in only the three lines of code I was trying to get to work...and voila it began to work. So, now begins the task of adding each piece of code back in bit by bit to find the offending code. But, at least I know the code I was trying to get to work, does in fact work. If I figure out what the conflict was, I'll post it here for info.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •