By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,940 Members | 614 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,940 IT Pros & Developers. It's quick & easy.

Determining Position In ItemsSelected Collection

P: n/a
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

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?


Nov 12 '05 #1
Share this Question
Share on Google+
1 Reply

P: n/a
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)
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 in the
forms section.... Happy reading.
Nov 12 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.