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

    Sorting Information in a RecordSet (Word 2003 VBA)

    Hi, I have a recordset that I pull in from ActiveDirectory into a combobox. This works great now, but it doesn't come in in alphabetical order. There is a .Sort property which I thought would work, and is used in some sample code on MSDN, but when I try to compile it I get an error indicating Invalid Property. Following is the code I am using:

    Private Sub GetUserList()
    Dim conn As New ADODB.Connection
    Dim rs As New 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:

    Set oRoot = GetObject("LDAP://rootDSE")
    'Work in the default domain
    sDomain = oRoot.Get("defaultNamingContext")
    Set oDomain = GetObject("LDAP://" & sDomain)
    sBase = "<" & oDomain.ADsPath & ">"
    'Get record set
    sFilter = "(&(objectCategory=person)(objectClass=user)(exten sionattribute1=*))"
    sAttribs = "extensionattribute1,Description"
    sDepth = ADS_SCOPE_SUBTREE

    sQuery = sBase & ";" & sFilter & ";" & sAttribs & ";" & sDepth

    conn.Open "Data Source=Active Directory Provider;Provider=ADsDSOObject"

    Set rs = conn.Execute(sQuery)

    rs.CursorLocation = adUseClient
    rs.Sort "extensionattribute1" '(ASC is the default, which is what I want here)

    On Error Resume Next
    rs.MoveFirst
    While Not rs.EOF
    cboAuthor.AddItem rs.Fields(0).Value
    rs.MoveNext
    Wend

    RoutineExit:
    Exit Sub

    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

    Resume RoutineExit

    End Sub

    Any ideas why VBA won't take the property which it even brings up using its AutoComplete feature? Arrgghh!

  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: Sorting Information in a RecordSet (Word 2003

    <P ID="edit" class=small>(Edited by jscher2000 on 07-Sep-04 21:34. Fixed something dumb.)</P>Try changing your sequence just a bit:

    Replace this:
    <UL>Set rs = conn.Execute(sQuery)

    rs.CursorLocation = adUseClient
    rs.Sort "extensionattribute1" '(ASC is the default, which is what I want here)[/list]With this:
    <UL>With rs
    .CursorLocation = adUseClient
    .Open sQuery, conn, adOpenStatic, adLockReadOnly, adCmdText
    .Sort "extensionattribute1" '(ASC is the default, which is what I want here)
    End with[/list]Does it help?

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

    Re: Sorting Information in a RecordSet (Word 2003

    Bummer, no, I tried it and it still stops on .Sort and gives me the 'Invalid use of property' error when I compile. Is it possible that I need a reference of some sort? I didn't think so, but I'm grasping at straws now!

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Sorting Information in a RecordSet (Word 2003

    Hi,
    As Sort is a property and not a method, you need to use:
    rs.Sort = "extensionattribute1"
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

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

    Re: Sorting Information in a RecordSet (Word 2003

    Elvis (aka Rory) you're beautiful!!! <img src=/S/kiss.gif border=0 alt=kiss width=34 height=15>
    Thank you SO much! It works wonderfully!

Posting Permissions

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