Determining Position In ItemsSelected Collection

[Steve Edwards]

I have a form with a list box, and the items selected from that list box are
used to add values to the WHERE clause of a query that is the data source
for a report. For each item chosen from the list box, the item is added to
a string, and that string becomes part of the WHERE clause. I need to
identify the first item chosen, since the string that it is put into is
slightly different than for the subsequent items. The problem I'm having is
figuring what to look at that tells me it is the first one.

I'm using the ItemsSelected collection, and my code I'm using to start out
is:

Dim frm As Form, ctl As Control
Dim varItm As Variant

Set frm = Forms!frmGCSBanksNotSurveyedReport
Set ctl = frm!lstHost
For Each varItm In ctl.ItemsSelected
Debug.Print "varItm = " & varItm
Next varItm

What happens is that for some reason varItm seems to be the index of the
chosen item in the original list box, not its index in the ItemsSelected
collection. What can I look at to determine the position of the item in the
ItemsSelected collection instead of the original list?

Thanks.

Steve

[Pieter Linden]

how about something like this:

Private Sub Command2_Click()
'Const cDELIMITER As String = "#" 'Date
'Const cDELIMITER As String = "'" 'Text
'Const cDELIMITER As String = "" 'Numeric

Dim lbx As ListBox
Dim varItem As Variant
Dim strList As String

Set lbx = Me.List0
For Each varItem In lbx.ItemsSelected
strList = strList & ", " & cDELIMITER & lbx.ItemData(varItem)
& cDELIMITER
Next varItem
strList = Right$(strList, Len(strList) - 2)
MsgBox strList
End Sub

I would create a function or something to return the selected items in
the listbox and then just include the function as the criteria for my
query or something. Just make sure to delimit your values. You'd
just have to wrap the individual values in the proper delimiter... Or
you could just modify the SQL syntax on the fly... whatever. THere's
a good example of playing with listboxes on www.mvps.org/access in the
forms section.... Happy reading.