How do you test for the last item selected in a multi-select list box?

[Jackie]

I use the code below to create the variables for the IN() function. My goal is to create a string with the following format "abc","def". This string will be used as a variable in the IN() function.

For each item selected the code below creates a string with beginning and ending quotes and a comma after the ending quote.

lst_applications is a multi-select list box. When the user makes a selection from the listbox, the selected items appear in a a textbox called txtAppSelected.

My problem is that once all the list items are converted to a string in "abc", "def", format an extra comma exists at the end of the string. This extra comma will create an error when the string is used in the IN() function.

How do you test for the last item selected in a multi-select list box?

Private Sub lst_applications_AfterUpdate()

' Print the list of selected items to the text
' box txtSelected.
Dim varItem As Variant
Dim strList As String
Dim strTemp As String
Dim strQuote As String
strQuote = Chr$(34)

With Me!lst_applications
If .MultiSelect = 0 Then
Me!txtAppSelected = .Value
Else
For Each varItem In .ItemsSelected
strTemp = strQuote & .Column(0, varItem) & strQuote & ","
strList = strList & strTemp
End If
Next varItem
Me!txtAppSelected = strList
End With
End Sub

Thank you in advance for your help.

 

[jebry]

Hi!

Try making this change:

Dim Counter as Long
Counter = 0

With Me!lst_applications
If .MultiSelect = 0 Then
Me!txtAppSelected = .Value
Else
For Each varItem In .ItemsSelected
Counter = Counter + 1
If Counter = .ItemsSelected.Count Then
strTemp = strQuote & .Column(0, varItem) & strQuote
Else
strTemp = strQuote & .Column(0, varItem) & strQuote & ","
End If
strList = strList & strTemp
Next varItem
End If
Me!txtAppSelected = strList
End With

Alternatively you can check for counter=0 and if it is set strTemp without the comma and increment the counter and if it isn't, then you set strTemp with the comma on the front of the string. Either method should work.

hth
Jeff Bridgham

 

[Jackie]

Jeff,

Thanks, I will try this. I knew that I was in the ballpark, but thought that there may be a VB command to use with the mult-select box.

I appreciate your help.

Jackie

 

[rbowes]

Another way you could do it is:

Private Sub lst_applications_AfterUpdate()

' Print the list of selected items to the text
' box txtSelected.
Dim varItem As Variant
Dim strList As String
Dim strTemp As String
Dim strQuote As String
strQuote = Chr$(34)


With Me!lst_applications
strTemp = "(
For Each varItem In .ItemsSelected
strTemp = strQuote & .Column(0, varItem) & strQuote & ","
strList = strList & strTemp
Next varItem
me!txtAppSelected = left(strList, len(strlist) -1)
End With
End Sub


I use this all of the time and works just fine.

Hope that helps!

BB

 

[Jackie]

The Left() function worked perfectly! Thank you!