I need help using a two column List Box in Access VBA

Hi Experts,
I am using the following code to add values to my 2 column listbox.  The problem is that, it only displays the first column and the values in the second column. I have my Row Source Type set to Value List and column count = 2.  I have included my code below.  What do I need to do to display both columns in my List Box?

Thanks in advance,
Private Sub Form_Load()
    Dim adoRS As New ADODB.Recordset
    Dim adoCN As New ADODB.Connection
    Dim adoCmd As New ADODB.Command
    Dim strItems As String
    Dim strConn As String
    '******* Get the assocaite info from the db *******
    strConn = modConnTools.fL50_GetConnString("FAEAGLE")
    With adoCN
        .ConnectionString = strConn
    End With
    With adoCmd
        .CommandText = "dbo.u_FASST_STTF_ListOfApps_ssp"
        .CommandType = adCmdStoredProc
        .ActiveConnection = adoCN
    End With

    'Set the cursor location to client
    With adoRS
        .CursorLocation = adUseClient
        .CursorType = adOpenStatic
        .LockType = adLockBatchOptimistic
        Set .Source = adoCmd
    End With
    'Disconnect the recordset
    Set adoRS.ActiveConnection = Nothing
    If adoCN.State = adStateOpen Then adoCN.Close
    Me.lstAvailableValues = ""
    'Now load the new data
    strItems = ""
    If Not (adoRS.EOF And adoRS.BOF) Then
        Do Until adoRS.EOF
            strItems = strItems & adoRS(0) & ";" & adoRS(1) & ";"
        Me.lstAvailableValues.RowSource = strItems
    End If
End Sub

Open in new window

Who is Participating?
an alt. way to add items to a list is to use additem

        Do Until adors.EOF
            Me.lstAvailableValues.AddItem adors(0) & ";" & arors(i)

What are the column widths? Check that in form design
Dim strArray(1 To 3, 1 To 2) As Variant
strArray(1, 1) = "Value 1"
strArray(1, 2) = "Value 2"
strArray(2, 1) = "Value 3"
strArray(2, 2) = "Value 4"
strArray(3, 1) = "Value 5"
strArray(3, 2) = "Value 6"

With ListBox2
.ColumnCount = 2
.List = strArray
End With

I'm surprised you are getting anything, since you close the connection before you iterate the recordset.
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.