?
Solved

ListBox Value List Separator

Posted on 2006-04-15
3
Medium Priority
?
1,031 Views
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

----------------------
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
0
Comment
Question by:Barry Cunney
3 Comments
 
LVL 54

Accepted Solution

by:
nico5038 earned 200 total points
ID: 16461740
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
 
LVL 38

Expert Comment

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

Expert Comment

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

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

807 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