Multi Select List Box into VBA Array

[jon_sg]

Happy New Year folks,

I have a form showing a certain source type. On this form I have a sub form which shows all categories available to this source type.

The sub form is set up with two list boxes one showing available categories but not yet assigned to this source type(lst1); the other list box shows categories currently assigned to this source type(lst2).

There are two command Buttons between the list boxes; the user selects an item in lst1 clicks the assign button and the category becomes assigned and moves to lst2 and is removed from lst1 and vice versa.
This all works perfectly for single selections, I assign the selection key field to a VBA variable and use an ADODB command + SQL statement to add this directly to the tbl and requery lst1 and lst2.

My problem is acheiving the same with multi selections. I think I need to assign these to an array with the number of elements governed by the ItemsSelected property of the multi select lst and each key an element of the array.

Can any one help me with the code to set up this array, assign the Cat Key field to array elements and loop through this to create an ADODB recordset object or command of the Items selected in the list box.
It is only this step of in the code that is causing me problems - going from multi selections to a recordset - once into a rst the rest is simple.

Thanks for your help

Jon (VBA Novice)
BTW (using Access 2002)

 

[WayneRyan]

Jon,

Sounds like you have most of it under control. All you need to
do is loop through the list box doing an insert for each one
that is selected.

There are examples here. I know that Pat Hartman did one.
Look in search here for "Multi-select" and that would be a good
starting point.

Wayne

 

[WayneRyan]

Jon,

Here it is:

http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=54924

Wayne

 

[jon_sg]

Top Job - Works Perfectly

Thanks Wayne

I tried searching but on VBA Arrays
Even better than I wanted, I have taken it out of the form module and made it a public function so I can use it on other list boxes too!

Jon

 

[WayneRyan]

Jon,

You won't find much on arrays here. They don't seem to be
a tool that is often used with Access. There are plenty of examples around here, you just have to know the right words.

Hope to see you around here again.

Off to New Years Party!

Happy New Year,
Wayne