Populating an Access 2003 ListBox with VB6 and solving the comma bug

• Download database - 32.2 KB

Introduction

This article provides several ways to populate a list box control, I will explain the problems with commas inside a string value, and other limitations you should consider when using a ListBox control. The attached file includes a small application that demonstrates the different methods and their implementation in code.

Populate methods

A list box can be populated with the following methods:

• Manually
• User-defined function
• Record Set
• External file

Manually

You can manually populate the control by using the ListBox AddItem method. The method accepts a String parameter and adds it to the end of the list.

List1.AddItem "111;222;333"

In the case of a multicolumn ListBox, you can specify a string with a semicolon (;) delimiter that will transform the input into a multicolumn structure.

Consider this code:

Private Sub Command1_Click()
    ' clear the listbox
    List1.RowSource = ""
    ' set the rowsorce to a value list type
    List1.RowSourceType = "Value List"
    ' set the number of colums
    List1.ColumnCount = 3
    ' set the columns width
    List1.ColumnWidths = "500;500;500"
    ' the data
    List1.AddItem "111;222;333"
    List1.AddItem "444;555;666"
End Sub

When you run this code on a form with a ListBox control named List1 and a command button named command1, you will be able to see a nicely formed three column list box. However, if you add this extra line:

List1.AddItem "4,4,4;555;666"

You will notice that the 4,4,4 spreads out into three columns instead of one. VB Help does not indicate that a comma is a delimiter character. In the help page that is being provided when clicking on the F1 key while selecting the AddItem method inside the Access VB Editor, it states the following: "For multiple-column lists, use semicolons to delimit the strings for each column (for example, "1010;red;large" for a three-column list)." So, even though not specified, the comma is treated as a delimiter character. There are two ways to solve this issue: the first is with the old and messy Replace method where you replace one character with another, in our case replacing the "," with "@" for example. However, you will need to clean up later by replacing them back to ",". A much more useful trick is to just wrap the value in quotations; this will prevent the AddItem method from translating the comma as a delimiter and will treat the string as a whole. Example:

List1.AddItem """5,0,0"";""500"";""500"""

User defined function

Also known as a callback function, it provides a set of rules and values the control uses to build itself. Each time a cell inside the control needs to be populated, the user defined function is being called and returns a value to the caller (the control). In order to implement this solution, we need to declare the source type for the ListBox row as the new user function.

Please note that the function needs to comply with MS VB rules as to how many parameters and their types are being passed to the function, and also, it needs to include a specific parameter with a specific name convention. In the attached file, there is the example for all the different methods in this article of how to populate a ListBox, including this one.

Steps in implementing this approach:

1. In a page level scope, create a multi-dimensional array that will contain your data:

Dim arr(0 To 1, 0 To 2) As String ' array with two rows and two columns

2. On the OnClick event, populate the array with your data:

populate the array
arr(0, 0) = "111"
arr(0, 1) = "222"
arr(0, 2) = "3,3,3" 

arr(1, 0) = "4,4,4" 
arr(1, 1) = "555"
arr(1, 2) = "666"

3. Initialize the ListBox control:

List1.RowSource = ""        ' clear the list box ctr
List1.RowSourceType = "ListFill1"    ' your function name
List1.ColumnCount = 3        ' specify the number of columns
List1.ColumnWidths = "500;500;500"    ' specify the columns width

The important line is where we define the rowSourceType. In this line, we actually tell the system to go to our function "ListFill1" and get the instruction of how to build the control.

4. The user-defined function

The system expects to find a function with a specific format and a specific set of parameters. This is why you shouldn't change the naming convention in the following function.

Private Function ListFill1( _
 ctl As Control, varId As Variant, lngRow As Long, _
 lngCol As Long, intCode As Integer)
    
    Select Case intCode
        Case acLBInitialize
              ListFill1 = True ' initialize
        Case acLBOpen
             ListFill1 = Timer  ' row key
        Case acLBGetRowCount
            ListFill1 = 2 ' record count
        Case acLBGetColumnCount
            ListFill1 = 3 ' column count
        Case acLBGetColumnWidth
            ' columns size !! strarts from zero?
            'If lngCol = 1 Then ListFill1 = 0
        Case acLBGetFormat
           ' column format
        Case acLBGetValue
            ' the data
            ListFill1 = arr(lngRow, lngCol)
        Case acLBEnd
            ' Just clean up, if necessary.
    End Select
End Function

Record Set

With the use of the record set object, you can bind the control directly without the need to loop through each record. In order to implement this solution, you will need to create a disconnected record set object and bind it to the control. Please note that by working with record set, you have the ability to populate the record set object from different sources: database, arrays, external files ...

Dim columnCount As Integer  ' number of fields/columns in the recordset
' set the list record source type to table and query
pObj.RowSourceType = "Table/Query"
' clear the listbox
pObj.RowSource = ""
' set columns width
pObj.ColumnWidths = pcolumnsWidth ' "500;500;500;500"
' set the number of columns
pObj.columnCount = UBound(Split(pcolumnsWidth, ";"))
Dim rss As New ADODB.Recordset
rss.CursorLocation = adUseClient
Dim connec As New ADODB.Connection
connec.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _ 
"Data Source=C:\Documents and   Settings\XXXXyourLocation\Desktop\listbox.mdb;"
rss.ActiveConnection = Nothing
rss.Open ssql, connec, adOpenKeyset, adLockBatchOptimistic
rss.ActiveConnection = Nothing

Set pObj.Recordset = rss

rss.Close
Set rss = Nothing
Set connec = Nothing

External file

In this method, we are using a delimited text format with or without a schema.ini file as the source of the control. The delimited text file is a file with a txt extension that contains rows of data with a delimited symbol, usually a comma between the different values. However, if you need to use other delimiters, you need to have the schema.ini file that contains the definition for the delimiter symbol.

pObj.RowSourceType = "Table/Query"
' clear the listbox
pObj.RowSource = ""
' set columns width
pObj.ColumnWidths = pcolumnsWidth
' set the number of columns
pObj.columnCount = UBound(Split(pcolumnsWidth, ";"))
    
Dim connCSV As New ADODB.Connection
Dim rsTest As New ADODB.Recordset
Dim adcomm As New ADODB.Command
Dim path As String

path = p_filePath
'This is connection for a text file without Header

If p_firsLineHeaders Then
    connCSV.Open "Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=" _
       & path & ";Extensions=asc,csv,tab,txt;HDR=NO;Persist Security Info=False"
Else
    connCSV.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" _
    & path & ";Extended Properties='text;HDR=NO;FMT=Delimited'"
End If

rsTest.Open "Select * From list.txt", _
connCSV, adOpenStatic, adLockReadOnly, adCmdText

Set pObj.Recordset = rsTest

rsTest.Close
Set rsTest = Nothing
Set connCSV = Nothing

The "list.txt" text file content for this example is:

"1111";"2222";"3333"
"11,11";"2222";"2222"
"1111";"2222";"3333"

The schema file needs to be in the same directory as the text file, and the file must be named "Schema.ini".

[list.txt] 
ColNameHeader=False 
Format=Delimited(;)

This example, as all the others, is located in the attached file.

Other limitations of the ListBox control

The ListBox control can hold only 255 characters for each sub item (column). Iin most cases, you don't use a ListBox to present long strings of data, but in cases you need to hold large data in a hidden column, beware of this limitation: strings longer than 255 will be truncated.

The attached files

There are three file types: MDB, txt, and ini. Place them together in whatever directory you choose and change the code inside the MDB file to the new directory. Just replace xxx_yourLoation.

Good luck.