ListBox Value List Separator

Posted on 2006-04-15
Last Modified: 2008-02-01
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
Question by:Barry Cunney
    LVL 54

    Accepted Solution

    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.

    LVL 38

    Expert Comment

    by:Jim P.
    Why don't you have a table that you dynamically fill and then set listbox to that table?
    LVL 44

    Expert Comment

    by:Leigh Purvis
    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.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    In the article entitled Working with Objects – Part 1 (, you learned the basics of working with objects, properties, methods, and events. In Work…
    Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
    Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
    In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

    758 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    7 Experts available now in Live!

    Get 1:1 Help Now