Topic: Filling ListBox ` and from an array a cycle

Dear friends of kind day.
Came up against the following situation - I try to fill ListBox in the form of EXCEL. Business went perfectly until, yet it was not required to fill ListBox from an array which in turn obtains the data from SQL request sorted out in a cycle. The problem that each new iteration overwrites previous value in shape and deduces only given received by the last iteration.
Question essence - how to deduce the data ? Without nullifying at each iteration former values ListBox?
Thankful in advance for answers.
Current state of affairs (LBHistory is that ListBox):

Dim Form As HistoryFRM, ARR (), i, ArrHistory ()
' we open the history form
Set Form = New HistoryFRM
With Form
.Show vbModeless
.LBHistory. ColumnCount = 6
For i = 0 To UBound (ARR)
ArrHistory = SQL_Editor ("SELECT * FROM [the Registration of changes] WHERE [ID] = '" and ARR (i) and "';")
.LBHistory. Column = ArrHistory
Next i
End With


Re: Filling ListBox ` and from an array a cycle

I will a little describe as it was possible to solve the given problem.
Output from a current situation was two. Briefly about everyone:
1. The given decision came from experts from the English-speaking resource more known as "Stack overflow". The link to my question more low:
[url =] https://stackoverflow.com/questions/487 … 1#48789801 [/url]
For those who does not cut in import, I will a little explain an essence of sentences  experts (by the way thanks separate by it for an operating variant). The problem is connected by that methods List and Count overwrite the information in ListBox come earlier and leave the information only from the last iteration, therefore for adding of the new information it is necessary to use method AddItem (that in my opinion not so conveniently). Therefore speech went about reorganization of operation of algorithm of the program herein at which by the time of filling ListBox the information already has been entirely combined.
It was at first offered to arrive as follows -

Option Explicit
Dim Form As HistoryFRM, ARR () As Variant, ArrHistory () As Variant
Dim i As Long, j As Long
Set Form = New HistoryFRM
With Form
.Show vbModeless
.ColumnCount = 6
For i = 0 To UBound (ARR)
ArrHistory = SQL_Editor ("SELECT * FROM [Table] WHERE [ID] = '" AND ARR (i) AND "';")
For j = LBound (ArrHistory) To UBound (ArrHistory)
.AddItem ArrHistory (j)
End With
End With

The given method works, but not in my case as at request in a database from  the one-dimensional array arrives not... Certainly that the system started to swear for the line (.AddItem ArrHistory (j)) and was necessary to refuse the specified method. However to my pleasure the following variant which soon arrived is deprived the above-stated lack -

Dim arr ()
Dim lb As ListBox
Dim numCols As Long
Dim rowCount As Long, colCount As Long
Dim numNewRecs As Long, newRecCount As Long
Set lb = Me. ListBox1
' You need to know how many new records are coming in
' Substitute this determination here:
numNewRecs = 2
numCols = lb. ColumnCount - 1
' Dimension the array for the current list plus the new records
ReDim arr (lb. ListCount - 1 + numNewRecs, numCols)
' Get the current list
For rowCount = 0 To lb. ListCount - 1
For colCount = 0 To numCols
arr (rowCount, colCount) = lb. List (rowCount, colCount)
' Append the new records
For newRecCount = rowCount To rowCount + numNewRecs - 1
For colCount = 0 To numCols
arr (newRecCount, colCount) = "New data" AND CStr (newRecCount)
' Populate the ListBox
lb. List = arr ()

The method essence is reduced to filling separately the created array by old values (from the previous iterations) + adding of new lines and to their subsequent output in ListBox. It is the a bit bulky decision, but at due  the method works.
2. The decision which has been applied as transitive at me in system.
I came on the other hand and tried  SQL request to basis. The text variable (ForSQL) which has been created imbibed in itself all possible [ID] in a type - ("' 123 ' or ID = ' 234 '... or ID = ' N '") that allowed to refuse a cycle of copying ListBox ` an and to fill with its already ready information from basis obtaining at once all data from SQL request. While works without failures, we look as leads at great volumes of the information.
Total rewrote a line in the following edition:

For i = 0 To UBound (ARR)
ArrHistory = SQL_Editor ("SELECT * FROM [the Registration of changes] WHERE [ID] = '" and ARR (i) and "';")
.LBHistory. Column = ArrHistory
Next i


ArrHistory = SQL_Editor ("SELECT * FROM [the Registration of changes] WHERE [ID] =" and ForSQL and ";")
If Not IsEmpty (ArrHistory) Then.LBHistory. Column = ArrHistory

Filling of a text variable () looks as follows

The beginning of a cycle of search of possible variants
If i = 0 Then
ForSQL = "'" and here value ID and "'" is located
ForSQL = ForSQL and "or [ID] = '" value ID and "'" here is located
End If
The following iteration

It is not difficult to guess that is made through IF as in request SQL - the first value after WHERE differs from the subsequent.
Somehow so. If someone has still ideas on optimization - ask to express. Thankful in advance!