ListBox Value List Separator

Hi Guys,

I am trying to populate a five-column listbox in Access 2002 by setting the RowSourceType property to "value list" and dynamically creating a string variable that extracts the values for the listbox from an array.  This string variable is passed to ListBox.AddItem. Unfortunately, some of the underlying data includes commas.  All the help topics I've found in Access and VBA indicate only the semicolon should act as a separator, but the comma is acting as a separator too and so the list box thinks I am adding 6 columns of data ins some cases and the 6th field value gets pushed into teh next iten in the list box.
 Can I change any property or option in my project, form or listbox to make Access read only semicolins as separators?
Any advice appreciated

----------------------
Code
----------------------
Function PopulateListBoxUsingArray(ctlList As ListBox, sArray() As Variant, iStartIndex%, bHasColHeads As Boolean) As Boolean
'---------------------------------------------------------------------------------------------------------------
' This function populates the specified list box with contents of specified array
' PARAMETERS <-
' 1. List box to be populated - ListBox control object
' 2. Array of values - String
'---------------------------------------------------------------------------------------------------------------
On Error GoTo Err_Function

Dim iLoop%
Dim jLoop%
Dim sItem$

    ' First Clear out the list
    RemoveAllListBoxItems ctlList, bHasColHeads
     
    For iLoop% = iStartIndex% To UBound(sArray) + iStartIndex%
       sItem$ = vbNullString
       ' Build AddItem String
        For jLoop% = 0 To UBound(sArray(), 2)
            If jLoop% = UBound(sArray(), 2) Then
                sItem$ = sItem$ & sArray(iLoop% - iStartIndex%, jLoop%)
            Else
                sItem$ = sItem$ & sArray(iLoop% - iStartIndex%, jLoop%) & ";"
            End If
        Next jLoop%
       
       If Len(sItem$) > UBound(sArray(), 2) Then ctlList.AddItem Item:=sItem$
           
    Next iLoop%
   
   
Exit_Function:
    Exit Function
   
Err_Function:
    PopulateListBoxUsingArray = False
    MsgBox "Function:  PopulateListBoxUsingArray - " & Err.Number & " " & Err.Description, , "Client Database"
    Resume Exit_Function
   
'---------------------------------------------------------------------------------------------------------------
End Function
LVL 17
Barry CunneyAsked:
Who is Participating?
 
nico5038Commented:
You can use the Start/Settings/Control panel to activate the Regional and Language Settings.
There under the [Advanced] button the list separater is defined that Access uses.

Nic;o)
0
 
Jim P.Commented:
Why don't you have a table that you dynamically fill and then set listbox to that table?
0
 
Leigh PurvisDatabase DeveloperCommented:
Commas can separate entries in a list like this.
You can delimit your text entries in quote marks if you like though.  That will prevent the commas from having an effect.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.