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

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
' 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%)
                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
    PopulateListBoxUsingArray = False
    MsgBox "Function:  PopulateListBoxUsingArray - " & Err.Number & " " & Err.Description, , "Client Database"
    Resume Exit_Function
End Function
LVL 17
Barry CunneyAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jim P.Commented:
Why don't you have a table that you dynamically fill and then set listbox to that table?
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.