Results 1 to 4 of 4
  1. #1
    Join Date
    May 2005
    Posts
    1,191

    Unanswered: Jet UserRoster in Listbox

    I'm trying to put the results of the UserRoster into a listbox on a form for easy viewing. I've attached a picture of the form and the pertinent code while I'm trying to debug it. The sub is called on load and on a timer every 5 secs. Anyone know why it's not updating all the columns?
    Attached Thumbnails Attached Thumbnails AddItem.JPG  
    Last edited by nckdryr; 07-06-07 at 12:16.
    Me.Geek = True

  2. #2
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    I'm wondering what the non-displayable characters are in the string you have generated. I would suggest you use Trim(Nz(rs.Fields(0), "")) for each of the four fields in the rs recordset. The Trim() function should remove that non-displayable characters. It would also be good to know what error message you are receiving (if any).

  3. #3
    Join Date
    May 2005
    Posts
    1,191
    Hey Vic,

    I had tried the trim function, and it didn't make any change. Oddly enough, it didn't give me any error msgs. But you did give me an idea; so I used asc() to figure out what char was being a pain, then used
    Code:
    strItem = Trim(Nz(rs.Fields(0), "NULL")) & ";" & _
         Trim(Nz(rs.Fields(1), "NULL")) & ";" & _
         Trim(Nz(rs.Fields(2), "NULL")) & ";" & _
         Trim(Nz(rs.Fields(3), "NULL"))
    strItem = Replace(strItem, Chr(0), "")
    It worked! Thanks Vic.
    Me.Geek = True

  4. #4
    Join Date
    May 2005
    Posts
    1,191
    Oh yea, so the final code:
    Code:
    Private Sub ShowUsers()
    
        Dim cn As New ADODB.Connection
        Dim rs As New ADODB.Recordset
        Dim i, j As Long
        Dim strItem As String
    
        Set cn = CurrentProject.Connection
        Set rs = cn.OpenSchema(adSchemaProviderSpecific, , _
                 "{947bb102-5d43-11d1-bdbf-00c04fb92675}")
    
        LstBoxUsers.RowSource = ""
        strItem = rs.Fields(0).Name & ";" & _
                    rs.Fields(1).Name & ";" & _
                    rs.Fields(2).Name & ";" & _
                    rs.Fields(3).Name
        LstBoxUsers.AddItem (strItem)
        LstBoxUsers.AddItem (";;;")
        
        While Not rs.EOF
            strItem = Trim(Nz(rs.Fields(0), "NULL")) & ";" & _
                        Trim(Nz(rs.Fields(1), "NULL")) & ";" & _
                        Trim(Nz(rs.Fields(2), "NULL")) & ";" & _
                        Trim(Nz(rs.Fields(3), "NULL"))
            strItem = Replace(strItem, Chr(0), "")
            LstBoxUsers.AddItem (strItem)
            rs.MoveNext
        Wend
        
        rs.Close
        Set rs = Nothing
        cn.Close
        Set cn = Nothing
    
    End Sub
    Me.Geek = True

Posting Permissions

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