multiselect listbox to array

[erbrose]

Hey all, newbie to vb here. I've created a listbox (called lst_county) that gets populated from a Select * From Table in Oracle on load. I've set the MultiSelect to 2 -Extended. I've got some code that sends out the select rows from lst_country to a msgbox (all working fine). What i need to do now is to actually put those selected rows into an array that I can later parse into another Select * From Table Where < selected lst_country (i,0)> = some value or < selected lst_country (i,0)> = some value , etc until all selected rows have been parsed.
So can anyone at least help me take the selected values and put them into an array.
Here is my msgbox code.

Public Function lstbox_array()
 
    Dim i As Long
    Dim Msg As String
 
    With frm1.lst_country
        For i = 0 To frm1.lst_country.ListCount - 1
            If .Selected(i) Then
                Msg = Msg & frm1.lst_country.List(i) & vbCrLf
            End If
        Next i
    End With
 
    If Len(Msg) = 0 Then
        Msg = “No items selected”
    End If
 
    MsgBox Msg
 
End Function

and here is my first attempt

Public Function lstbox_array()
 
Dim i As Long
Dim Msg As String
'Dim listArray()
'Dim j As Long
'Dim vArray
'ReDim listArray(1 To frm1.lst_country.ListCount, 1 To 1)
 
    With frm1.lst_country
        For i = 0 To frm1.lst_country.ListCount - 1
            If frm1.lst_country.Selected(i) Then
                Msg = Msg & Left(frm1.lst_country.List(i), 3) & vbCrLf
                'j = j + 1: listArray(j, 1) = frm1.lst_country.List(i, 1)
                'vArray(i) = frm1.lst_country.List(i, 1)
                'listArray(i, 0) = Left(frm1.lst_country.List(i), 3)
            End If
        Next i
    End With
 
    If Len(Msg) = 0 Then
        Msg = "No items selected"
    End If
'Debug.Print listArray(i)
    MsgBox Msg
 
End Function

you'll notice alot of commented out code and that just cause I keep trying different things and see them fail..

i figure once i can get the selected items into a new array, it shouldn't be too much of a problem to parse through those and add them to the SQL WHERE statements.
Thank immensely ahead of time for anyone's help and or direction.
Cheers,
Eric

[janders468]

This may not be the best way but the way I would approach this would be:
1. For basic looping through the listbox

dim var as variant
for each var in listbox.ItemsSelected
     msgbox listbox.ItemData(var)
next var
 

It wouldn't be too much of a stretch to convert that to an array if you wanted to:

dim var as variant
dim n as integer
redim varArr(listbox.listcount-1)
for each var in listbox.ItemsSelected
   varArr(n) = listbox.ItemData(var)
    n = n + 1
next var
 

This is one way to do it any how

[erbrose]

I guess I need to learn more about arrays before continuing here.
When I try your first bit of code (after changing .ItemsSelected to just .Selected As I was getting a Compile error: Method or data member not found)

Dim var As Variant
    For Each var In frm1.lst_country.Selected
        MsgBox frm1.lst_country.ItemData(var)
    Next var
 

i get a Compile error: Argument not optional and it highlights line 2.

same with the second bit of code you posted if i changed it to this

Dim var As Variant
Dim n As Integer
ReDim varArr(frm1.lst_country.ListCount - 1)
For Each var In frm1.lst_country.Selected
   varArr(n) = frm1.lst_country.ItemData(var)
    n = n + 1
Next var
 

again it highlights line 4 and gives me the same error, so there is something with the Selected argument that is not working and confusing me.

[janders468]

I should have maybe asked some preliminary questions, sorry about that. What version of vb are you using?

[erbrose]

I managed to get it sorted out... after reading more about arrays.
here is what I did

Dim i As Integer
Dim MyArray() As String
Dim n As Integer
 
ReDim MyArray(frm1.lst_country.ListCount)
 
    If frm1.lst_country.ListIndex = -1 Then
        MsgBox "You didn't select any Countries"
    End If
    For i = 0 To frm1.lst_country.ListCount - 1
        If frm1.lst_country.Selected(i) = True Then
            MyArray(n) = Left(frm1.lst_country.List(i), 3)
            n = n + 1
        End If
    Next i
 
ReDim Preserve MyArray(n - 1)
 

I really appreciated your replies and code snippets!
Thanks
Eric
ps I will be sure to note next time I am working in VB6

[janders468]

Glad you got it figured out, and glad to help. For whatever it's worth the code I first supplied was for Access vba, which uses a different listbox object than does forms 2.0 (the library containing the listbox object in vb6 and vba for the other office products). I believe that is why the method was missing.