Solved: Get The Query Results in a Array

Hi,

I want to execute a query which has been already created & put the results in array.

The query will return only one column hence I can use single dimensional array.

Also the query should take the parameter as per the selection made in the ListBx1.

Please guide.

Thanks
Sudhir

You can do this by using a DAO (Data Access Object) connection to the current database. Then open a recordset using the SQL from your query, and then use a Do...Loop to load the recordset values into an array. Example:

[vba]Dim arrResults() As Variant
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim i As Integer

Set db = CurrentDb
Set rst = db.OpenRecordset("***Insert your SQL here***")

i = 0

rst.MoveFirst
Do Until rst.EOF
arrResults(i) = rst.Fields(0)
rst.MoveNext
i = i + 1
Loop

rst.Close
Set rst = Nothing
db.Close
Set db = Nothing[/vba]

Thanks Randy

But I have the query built into the RowSource of the listbox21

[VBA]SELECT TrainingPlanner.TrainingName FROM TrainingPlanner WHERE (((TrainingPlanner.Name)=Forms!UserForm!CsNameList) And ((TrainingPlanner.Attended)="No")) GROUP BY TrainingPlanner.TrainingName; [/VBA]

I requery the above using OnClick event of my "CSNameList" List Box.
The results are automatically displayed in Listbox21.

Now I just want to capture the results in an array also.

Thanks
Sudhir

Google hasn't turned up anything promising when it comes to pulling individual row values out of a listbox- which is what you would need to do in order to populate an array.

Why can't you use both? Have your OnClick event set to requerry the listbox, and also rerun the code to populate your array?

Thanks Randy,

I was able to this, I am posting the Code below.

[vba]ReDim arrTrainAtt(0 To List17.ListCount)

J = 0
For i = 0 To List17.ListCount - 1
arrTrainAtt(J) = (List17.ItemData(i))

J = J + 1
Next i[/vba]

Also I do not know how to rerun that query to populate my array...!!!!!!!!!!!!!!!!

Thanks
Sudhir

Well I am in the second part of my problem. This Time I have merge two dynamic arrays.

Any Guidance in this.

While I google the above problem I got teh below code.
I am posting it as someone else could also make use of it.

[VBA]Function ArrayUnion(ByVal va1 As Variant, ByVal va2 As Variant) As Variant
Dim i As Long, Upper As Long
If TypeName(va1) = “Empty” Then
va1 = va2
Else
Upper = UBound(va1)
If LBound(va2) = 0 Then Upper = Upper + 1
ReDim Preserve va1(LBound(va1) To UBound(va1) + UBound(va2) - LBound(va2) + 1)
For i = LBound(va2) To UBound(va2)
va1(Upper + i) = va2(i)
Next i
End If
ArrayUnion = va1
End Function[/VBA]