How do you populate a multi-column combo box dynamically

First of all, I am using Access 97

I need to populate a multicolumn combo box dynamically from an array.  I am able to populate the combo box if  I use only 1 column by following the example given in the help for the 'RowSourceType' property of the combo box.  The following is an example of what need to do. This might help in solving my problem.

I can do this:

option base 1
dim SampleArray(6) as string

SampleArray(1) = "1"
SampleArray(2) = "2"
SampleArray(3) = "3"
SampleArray(4) = "4"
SampleArray(5) = "5"
SampleArray(6) = "6"

I can place this in the combo box so that it is displayed as follows:

This is what I really want to do

option base 1
dim SampleArray(6,2) as string

SampleArray(1,1) = "1"
SampleArray(1,2) = "One"
SampleArray(2,1) = "2"
SampleArray(2,2) = "Two
SampleArray(3,1) = "3"
SampleArray(3,2) = "Three"
SampleArray(4,1) = "4"
SampleArray(4,2) = "Four"
SampleArray(5,1) = "5"
SampleArray(5,2) = "Five"
SampleArray(6,1) = "6"
SampleArray(6,2) = "Six"

I need to be able to populate the combo box to display as follows:

1  One
2  Two
3  Three
4  Four
5  Five
6  Six

Who is Participating?
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.

Ok - you need to use the column property like so:

For intCount = 1 to ubound(SampleArray)

      Forms("<FormName>")("<ComboBoxName>").column(0) = SampleArray(intCount,1)
      Forms("<FormName>")("<ComboBoxName>").column(1) = SampleArray(intCount,2)


Let me nknow if you need more info.


Set the rowsource type to the name of your function, (see below), no parens, no ‘=’.

Function FillWithArray(ct As Control, id As Long,row As Long,col As Long,code As Integer) As Variant

    Select Case Code
        Case LB_INITIALIZE        ' Initialize.
            ' Non-zero if possible to fill the list
            FillWithArray = Timer

        Case LB_OPEN              ' Open.
            ' Non-zero if possible to fill the list
            FillWithArray = Timer

        Case LB_GETROWCOUNT       ' Number of rows.
            FillWithArray = Ubound(SampleArray(1))

        Case LB_GETCOLUMNCOUNT    ' Number of columns.
            FillWithArray = 2

        Case LB_GETCOLUMNWIDTH    ' Column width.
            FillWithArray = True

        Case LB_GETVALUE          ' Get the data.
            FillWithArray = SampleArray(row, col)

        Case LB_GETFORMAT        ' Get the data.
            ' Do nothing

        Case LB_END               ' End
            ' Do nothing again, (I think).

    End Select
End Function

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
geowilliAuthor Commented:
thanks BrianWren

I was missing the (row, col) arguments to the array while retrieving the data.  I am not sure why I did not get an error message.  Anyway, my problem is now solved.

BTW, I just had a thought, and tried it out, and it worked.

If your cbo is set to "Value List", (which you could cause to be the case through code), then you can set the RowSource to the same string that you might type into the property sheet.


Dim s as String
For i = 0 to UBound(ArrayName(1))
    s = s & ArrayName(i,1) & ";"
    s = s & """" & ArrayName(i,2) & """;
Next i
s = Left(s, Len(s) - 1) ' strip the trailing ';'.

Me!cbo.RowSource = s

It's a little less dynamic, but is also effective...

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.