Referencing last selected item in a list box

[Guest]

Is there a way to reference the last selected item in a list box?

For example... If I have a multiselect list box with three items selected
(Apples, Bananas, and Oranges respectively) Is there a way I can reference
the last selected item ( In this case being Oranges)?


The reason for this is regarding my last post, I was trying to create a SQL
Statement using a for loop so it would look something like:


For Each varItem In lstProgCode.ItemsSelected
strProgCode = "'" & lst.Column(1, varItem) & "'"
If the item selected is not the last item then
strSQL = strSQL & " & strProgCode & " & "Or "
Else
strSQL = strSQL & " & strProgCode & "
Next varItem

strSQL = "SELECT * FROM tblTable1 WHERE Table1.ProgCode = " & strSQL & "


I know this needs a little work but any help would be greatly appreciated.

Thanks,
Chad

 

[Douglas J. Steele]

Rather than fussing whether or not it's the last element, just remove the
last " OR " at the end of the loop. In actual fact, your code wouldn't
produce valid SQL: it would generate something like

Table1.ProgCode = value1 OR value2 OR value3

Assuming ProgCode is a text field, try the following:

For Each varItem In lstProgCode.ItemsSelected
strSQL = strSQL & "'" & lst.Column(1, varItem) & "', "
Next varItem

If Len(strSQL) > 0 then
strSQL = Left$(strSQL, Len(strSQL) - 2)
End If

strSQL = "SELECT * FROM tblTable1 WHERE Table1.ProgCode IN (" & strSQL &
")"

(If ProgCode is numeric, change the one line to strSQL = strSQL &
lst.Column(1, varItem) & ", ")

 

[Dan Artuso]

Hi,
The way to do this is to simply build your string and then chop off the last Or.
I can't tell from your code exactly what kind of string you're building, but if it ends with:
" Or "
Just get rid of it:
strSql = Left(strSql,Len(strSql) - 4)
by chopping off the last 4 characters.

 

[George Nicholson]

Chad:

Douglas and Dan have already pointed you to a better way to do what you are
trying to do.

I am going to be an anarchist and answer the other question you asked, but
you have to promise you will only use this knowledge for good.

Is there a way to reference the last selected item in a list box?

Depends on what you mean by "last selected". 1) Last item the user clicked
on or 2) the "Bottom-most" item selected?

1) The ListIndex property gives you the zero-based number of the last item
selected by the user, which may or may not be the "bottom-most" item in a
multi-select box. Therefore, if you have a multiselect Listbox, this may
not do you a lot of good. In your example, with Apples, Bananas, and
Oranges selected, *any* of the 3 could be last selected. You can't assume
the user will always work top-down.

On the other hand, if a programatically inserted (All) is the first
selection in your list box, ListIndex will tell you if that was just
selected and you can deselect everything else (i.e., synchronize the box
visually) via code. The inverse is also true: If (All) is selected but it
wasn't the last item selected by the user, you need to deselect (All) via
code.

2) If, when looping through the ItemsSelected, you want to do something
(like exit the loop) once you have "handled" all the selected items, you
could test ItemsSelected.Count, using a structure like this:

i = 0
For Each varItem In lstProgCode.ItemsSelected
If varItem.Selected Then
i = i + 1
' Do something creative
If i = lstProgCode.ItemsSelected.Count
' Bail out: There are no more "selections" to be creative with
Exit For
End If
Else
' Ignore: non-selected varItem
End If
Next varItem