UtterAccess HomeUtterAccess Wiki

Welcome Guest ( Log In | Register )

Custom Search
Edit Discussion
> SelectedItemsList    


Synopsis

This function returns a comma delimited list of the items selected in a List Box, and is suitable for use in an SQL In clause. Program logic detects if items are to be treated as stings.

CODE
' SelectedItemsList
' http://www.utteraccess.com/wiki/SelectedItemsList
' Code courtesy of UtterAccess Wiki
' Licensed under Creative Commons License
' http://creativecommons.org/licenses/by-sa/3.0/
'
' You are free to use this code in any application,
' provided this notice is left unchanged.
'
' rev  date                          brief descripton
' 1.0  2015-10-14                    
'
'**********************************************
'SelectedItemsList
'  build list of selected items in a List Box
' ARGUMENTS
'  ListBoxIn - a list box control
'  IsString - Optional, True forces items to be treated as strings, the default action
'     has the program determining whether strings are to be used
' RETURNS
'  A comma delimited string of selected items: 1,2,...
'     suitable for use om an In clause: [field name] In ( 1,2, ... )
'  Any item in list being non-numeric forces all items to be treated
'     as strings (i.e., quotes are place around all items), setting IsString to true
'     forces items to be treated as strings, even if all are numeric
'  An empty string if the list box is empty or no items are selected.
'  Modified 2015-10-17 by AzOlder, Omits Error message if no items selected/in list
Public Function SelectedItemsList(ListBoxIn As ListBox, Optional IsString As Boolean = False) As String
     
  Dim str As String
  Dim i As Long
 
  With ListBoxIn
'       AzOlder commented out  If no items are selected an empty string is returned
'      'test for selected items and empty list
'      If .ItemsSelected.Count < 1 Then
'         MsgBox "There are no selected items in ListBox " & .Name & ".", vbCritical
'         Exit Function
'      ElseIf .ListCount = 0 Then
'         MsgBox "No items in ListBox " & .Name & ".", vbCritical
'         Exit Function
'      End If

     'increment through list, building comma delimited string
     For i = 0 To .ListCount
        If .Selected(i) Then
           'any item being a string means ALL are strings
           If Not IsNumeric(.ItemData(i)) Then isString = True
           str = str & "," & .ItemData(i)
        End If
     Next
     If isString Then      
        'add quotes to items
        str = Replace(str, ",", """,""")
        str = Mid(str, 3) & """" 'strip leading ", and add trailing "
     Else
        str = Mid(str, 2) 'strip leading ,
     End If
  End With
 
  SelectedItemsList = str
 
End Function

Creative Commons License
SelectedItemsList by UtterAccess Wiki is licensed under a Creative Commons Attribution-ShareAlike 3.0 Unported License.
Editing and revision of the content is freely encouraged; for details, see Expected Usage.

Edit Discussion
Custom Search
Thank you for your support!
This page has been accessed 1,152 times.  This page was last modified 06:44, 17 October 2015 by azolder.   Disclaimers