[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

How do you populate a multi-column combo box dynamically

Posted on 1999-12-05
4
Medium Priority
?
188 Views
Last Modified: 2012-05-04
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:
1
2
3
4
5
6



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

0
Comment
Question by:geowilli
  • 2
4 Comments
 
LVL 6

Expert Comment

by:simonbennett
ID: 2256600
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)

Next

Let me nknow if you need more info.

HTH

Simon
0
 
LVL 9

Accepted Solution

by:
BrianWren earned 400 total points
ID: 2259384
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
0
 
LVL 1

Author Comment

by:geowilli
ID: 2259782
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.

0
 
LVL 9

Expert Comment

by:BrianWren
ID: 2259828
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.

EG

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...

Brian
0

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

Question has a verified solution.

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

Beware when using the ListIndex and the Column() properties of a listbox in Access 2007.  A bug has been identified in the Access 2007 listbox code which can cause the .ListIndex property to return a -1, and the .Columns(#) property to return a NULL…
Audit trails are very important in any system to hold people responsible for certain transactions and hold them to take ownership of their actions. This article is dedicated to all novice "Microsoft Access" developers.
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…
Get the source code for a fully functional Access application shell with several popular security features that Access VBA application developers desire, but find difficult or impossible to figure out how to code. You get the source code for managi…

590 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