2 tricky questions about listboxes

[Henrik B.]

Hi All

I am trying to create a sort of query-builder, where the user selects the
elements from a database to put in the query.

I have a listbox called Listbox1 - this listbox is filled with data from my
accessdatabase.

I then want to put the elements that has been selected in listbox1 into
another listbox called listbox2

I found a code-example at the Ms Word MVP Faq Site that finds out what
elements in a multi-select listbox that is selected. I changed this code so
that it would put the elements from one listbox into another but, and this
is my question #1 When I run my code (se code below) it puts the number of
the elements selected into listbox2 instead of the actual data. I've been
looking at the way the listboxes is declared and can't find any thing wrong!
So the question is how do I put the actual data into listbox2 instead of the
elementnumber?

(This is the code used)

For I = 0 To Listbox1.ListCount - 1 'Loop thru elements in listbox

If Listbox1.Selected(I) Then 'If element selected then

Listbox2.AddItem Listbox1.List(I) 'Add the selected items from listbox1 to
listbox2

End If

Next I

When the listbox2 is filled the right way and the user presses a
commandbutton the data from listbox2 would be used in a sql-like
query-statement. So I guess that I have to put all the elements in listbox2
at the time of the activation of the commandbutton into a textstring so that
I can use it in the statement - but how do I do that? (That was my final
question)

Any help and suggestions would be greatly appreciated.

Henrik

 

[Jonathan West]

Hi Henrik,

Hi All

I am trying to create a sort of query-builder, where the user selects the
elements from a database to put in the query.

I have a listbox called Listbox1 - this listbox is filled with data from my
accessdatabase.

I then want to put the elements that has been selected in listbox1 into
another listbox called listbox2

I found a code-example at the Ms Word MVP Faq Site that finds out what
elements in a multi-select listbox that is selected. I changed this code so
that it would put the elements from one listbox into another but, and this
is my question #1 When I run my code (se code below) it puts the number of
the elements selected into listbox2 instead of the actual data. I've been
looking at the way the listboxes is declared and can't find any thing wrong!
So the question is how do I put the actual data into listbox2 instead of the
elementnumber?

(This is the code used)

For I = 0 To Listbox1.ListCount - 1 'Loop thru elements in listbox

If Listbox1.Selected(I) Then 'If element selected then

Listbox2.AddItem Listbox1.List(I) 'Add the selected items from listbox1 to
listbox2

The line above is your problem. The List property of a ListBox is a Variant
containing a 2-dimensional array, even if the list has only one column.
Therefore that line should be

Listbox2.AddItem Listbox1.List(I, 0)

End If

Next I

When the listbox2 is filled the right way and the user presses a
commandbutton the data from listbox2 would be used in a sql-like
query-statement. So I guess that I have to put all the elements in listbox2
at the time of the activation of the commandbutton into a textstring so that
I can use it in the statement - but how do I do that? (That was my final
question)

Of course, the List property of ListBox2 is also a 2-d array. This means
that you can't use the Join function to return a string with all the
elements, as Join only works with one-dimension arrays. I think that you
will simply have to do something like this.

Dim strList as String
Dim j as Long
For j = 0 to ListBox2.ListCount - 1
strList = strList & ListBox2.List(j, 0) & " "
Next j


You may want to use a different separator instead of space.

--
Regards
Jonathan West - Word MVP
MultiLinker - Automated generation of hyperlinks in Word
Conversion to PDF & HTML
http://www.multilinker.com