Add a single record to a multiple-column listbox in a loop from array

[TomPC]

Hi All

Win7/2010

I have an array PeopleList(6,320) that contains

PersonID, FirstName, LastName, Email, Phone, Notes


What I'm trying to do
I have two separate requirements:

(1) To add the whole array to a listbox on form initialization - see Sub UserForm_Initialize()
(2) To clear the listbox and re-add only certain items based on what's typed in a textbox - see Sub txtSearchTerm_Change()

I have two errors:


Error 1 in UserForm_Initialize()
The listbox contents need transposing! It is displaying as

[COLOR=#333333]1           2           3           4           5[/COLOR]
Tom         Ben         Heidi       Julie       Mark
Smith       Jones       Evans       Simpson     Petersen
x@yo.com    a@bo.com    c@do.com    e@fo.com    g@ho.com
[COLOR=#333333]02071001022 02071001026 02071001027 02071001028 02071001029
[/COLOR]Friend

When it should be displaying as

[COLOR=#333333]Code:[/COLOR]
1   Tom Smith   x@yo.com    02071001022 Friend[COLOR=#333333]   
2   Ben Jones   a@bo.com    02071001026    [/COLOR]

Is there a way to transpose the array? (Alternatively, solving my error 2 would be helpful enough)


Error 2 in txtSearchTerm_Change()
I cannot find anywhere - even on MSDN - all the information I need how to correctly add a single record to a multiple-column listbox! What I'm trying is:

    For i = 0 To UBound(SearchList)        If InStr(1, SearchList(i), SearchTerm) <> 0 Then
            With lstPeople
                .AddItem
                For j = 0 To UBound(PeopleList, 1)
[COLOR=#ff0000]                    .List(c, j).Value = PeopleList(j, i)[/COLOR]
                Next j
            End With
            c = c + 1
        End If
    Next i

but .List(c, j).Value = PeopleList(j, i) is throwing a Runtime error 424 "Object Required"

How do I add a record to the listbox????


Full code for reference:

Option Explicit

Private PeopleList As Variant
Private SearchList As Variant


Private Sub UserForm_Initialize()
    
    Dim SQL As String
    Dim i As Long
    Dim j As Long
    
    'Get People list from tblPeople
    SQL = "SELECT PersonID, FirstName, LastName, Email, ContactNumber, Notes "
    SQL = SQL & "FROM tblPeople "
    SQL = SQL & "WHERE Category <> '[System]'"
    
    PeopleList = GetData(SQL)
    ReDim SearchList(UBound(PeopleList, 2))
    For i = 0 To UBound(PeopleList, 2)
        SearchList(i) = ""
        For j = 1 To UBound(PeopleList, 1)
            SearchList(i) = SearchList(i) & Replace(Trim(IIf(IsNull(PeopleList(j, i)), "", PeopleList(j, i))), " ", "")
        Next j
    Next i
       
    With frmSearchPeople
        .lstPeople.ColumnCount = UBound(PeopleList, 1) + 1
        .lstPeople.List = PeopleList
        .Show
    End With
    
End Sub


Private Sub txtSearchTerm_Change()


    Dim i As Long
    Dim j As Long
    Dim SearchTerm As String
    Dim c As Long
    
    lstPeople.Clear
    c = 0
    
    SearchTerm = Replace(Trim(txtSearchTerm.Text), " ", "")
    
    For i = 0 To UBound(SearchList)
        If InStr(1, SearchList(i), SearchTerm) <> 0 Then
            With lstPeople
                .AddItem
                For j = 0 To UBound(PeopleList, 1)
                    .List(c, j).Value = PeopleList(j, i)
                Next j
            End With
            c = c + 1
        End If
    Next i


End Sub

 

[ChrisM]

to solve #1 it's simply:

Application.Transpose(MyArray)

As far as issue #2, you can certainly add rows, but unfortunately I haven't done it in a long time and I don't have my old files in front of me right now to review. But don't give up!

 

[Norie]

When you add using list don't use Value.

 

[Kyle123]

Some pointers:

1. If you've got 6,000 records, search in the database, I'd guess it would be faster, so don't init with all the records - make the user type into the search box and return only the relevant records.
2. Don't ever, ever, ever use .AddItem for more than about 5 items, it's extremely slow. You can dump your array into the listbox simply with:

    With frmSearchPeople
        .lstPeople.ColumnCount = UBound(PeopleList, 1) + 1
        .lstPeople.Column = PeopleList
        PeopleList = .lstPeople.List 'Transpose our data
        .Show
    End With

Note the .Column this will automagically transpose the data.

I think the total code should look something like this (untested):

Option Explicit


Private PeopleList As Variant
Private SearchList As Variant


Private Sub UserForm_Initialize()
    
    Dim SQL As String
    Dim i As Long
    Dim j As Long
    
    'Get People list from tblPeople
    SQL = "SELECT PersonID, FirstName, LastName, Email, ContactNumber, Notes "
    SQL = SQL & "FROM tblPeople "
    SQL = SQL & "WHERE Category <> '[System]'"
    
    PeopleList = GetData(SQL)
    
    ReDim SearchList(UBound(PeopleList, 2))
    For i = 0 To UBound(PeopleList, 2)
        SearchList(i) = i & "~~"
        For j = 1 To UBound(PeopleList, 1)
            SearchList(i) = SearchList(i) & LCase(Replace(Trim(IIf(IsNull(PeopleList(j, i)), "", PeopleList(j, i))), " ", ""))
        Next j
    Next i
       
    With frmSearchPeople
        .lstPeople.ColumnCount = UBound(PeopleList, 1) + 1
        .lstPeople.Column = PeopleList
        PeopleList = .lstPeople.List 'Transpose our data
        .Show
    End With
    
    
    
End Sub




Private Sub txtSearchTerm_Change()




    Dim i As Long
    Dim j As Long
    Dim SearchTerm As String
    Dim c As Long
    Dim filteredArray
    Dim NewData() As Variant


    
    SearchTerm = Replace(Trim(txtSearchTerm.Text), " ", "")
    filteredArray = Filter(SearchList, SearchTerm)
    
    ReDim NewData(LBound(PeopleList) To UBound(filteredArray), LBound(PeopleList, 2) To UBound(PeopleList, 2))
    
    For i = LBound(filteredlist) To UBound(filteredlist)
        c = Val(filteredArray(i))
        For j = LBound(PeopleList, 2) To UBound(PeopleList, 2)
            NewData(i, j) = PeopleList(c, j)
        Next j
    Next i


    lstPeople.List = NewData




End Sub

 

[TomPC]

Thank you all for your replies: in particular, Kyle. I'll try it and let you know how I got on! Thanks, Tom.

 

[TomPC]

Perfect, Kyle. Thank you - another case closed.

TomPC