Get Items listbox into array

[Crookshanks]

Goodmorning,

I've got a small question and I am quite sure that you will be able to help me. I've got a listbox filled by a Query. Something like this:

listTest.rowType = "Table/Query"
listTest.rowsource = testSQL

Q: What is the easiest way to get the whole listbox in a 2 dimensional array? I now how to read selected items from a listbox but I am just struggling to retrieve alle items into the array. Any suggestions? Any help appreciated.

Regards,

 

[CautionMP]

Crookshanks,
Why?

Dim varList() As Variant
Dim lngRow As Long, lngColumn As Long
lngRow = Me.List0.ListCount - 1
lngColumn = Me.List0.ColumnCount - 1
ReDim varList(lngRow, lngColumn)
For lngRow = 0 To Me.List0.ListCount - 1
  For lngColumn = 0 To Me.List0.ColumnCount - 1
    varList(lngRow, lngColumn) = Me.List0.Column(lngColumn, lngRow)
  Next lngColumn
Next lngRow

Hope this helps,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[Crookshanks]

Thanks for the code. It will help me for sure. I will try to explain why, I want to do this.

I've got a tbl_Source and a tbl_Headwords. The tables have a many-to-many relationship and therefore I made an extra tabel in which the 'matches' are stored.

In two listboxes I want to attach and de-attach Headwords from Sources without using a bind form. Therefore I want to populate the listbox with an array.

As the user can 'walk' through all the Sources in another listbox I wanted the listbox initially filled by a query. That works of course without a problem.

As the user edits the 'matches' I use the array to register the changes. If the user presses 'Save' I will send back (after checking) the array information to the table. A bit hard to explain, but I am on the way reaching my goal.

Thanks again

 

[MajP]

You may want to look at the ADO/DAO method "getRows". This is the fastest way to populate a variant array from a recordset. Also I agree with CautionMP that no arrays are needed. You probably could use sql action queries (update, insert, delete) as you select unselect items in your list boxes.

 

[Crookshanks]

Thanks for your contribution. More learning purposes I will try to do it with action queries.