Populate multiple columns in a Comob Box using ADO 2

[jmeckley]

This is my first project using ADO and I am having difficultly populating a combo box. I can populate 1 column, but i want to populate 2 columns. The first column would be the id number. the second column would be the name of the service. Here is the code I am currently using

    'Populate Service Combo Box
    Dim cboServiceSql As String
    Dim I As Integer
    Dim rsCboService As ADODB.Recordset
    
    cboServiceSql = "exec sp_services_s"
    Set rsCboService = New ADODB.Recordset
    rsCboService.Open cboServiceSql, cnnCWO(User, Pwd)
    cboService.Object.Clear
    
    For I = 1 To (rsCboService.RecordCount)
        cboService.Object.AddItem rsCboService.Fields("Service")
        rsCboService.MoveNext
    Next I
    
    rsCboService.Close
    Set rsCboService = Nothing

Once I get both columns populated I can setup the combo box to show the service, but capture the id number.

Thank you for your assistance

Jason Meckley
Database Analyst
WITF

 

[Trendsetter]

Hi Jason,
I have not used AddItem and therefore cannot comment about it's functionality but as an observation..
I would have thought that to populate a multi-column combo would require some from of extra dimension such as an array.
The code you are using moves to the next row value without going sideways>>>>>>>>>>.
I would have thought that there would be a code line for each column in the combo and THEN move to the next row.

 

[jmeckley]

I tried coding 2 add items statements and moving to the next item, but that still added the values in rows.

  for loop
    add "service"
    add "serviceID"
    move next
  next

This created results like this:
Service A
ID 1
Service B
ID 2
Service C
ID 3
Service D
ID 4

this is what I am looking for
Service A | ID 1
Service B | ID 2
Service C | ID 3
Service D | ID 4

How do you go about populating combo and list boxes?

Thank you for your assistance

Jason Meckley
Database Analyst
WITF

 

[Trendsetter]

I use good old queries either straight SQL or the query builder for graphic display of complicated queries.

I would guess that you need to specify 'before running' how many columns you are working with, I would guess this to be a property of AddItem()

 

[netbumbler]

Did you ever find a solution to the multi-column additem problem? Just wondering cause I've got the same problem!
Thanks,
Chris

 

[RoyVidar]

Item might be perceived as "row" here. To fill an item, containing more than one column, concatinate a string containing the values you need, with the delimiter, ex two columns, populated with two fields from the recordset rs:

[tt]Me!lstList.AddItem(rs!FirstField & ";" & rs!SecondField)[/tt]

Roy-Vidar

 

[lastout]

jason, shouldn't you be using the Column property?

 

[neecie]

The method used to add items depends on what combobox you are using.
MSOffice, forms, Access all have different comboboxes and require different variables.
It would help to know what library references you are using.

 

[dmreda]

Hi,

I think you need to use "additem" to put the very first item into a row, and then you can use "list" to populate other columns.

Example:

cboMyCombo.additem "in the first row"
cboMyCombo.list(0,1) = "in the first row and second column"

The numbering system runs from 0 to NumColumns-1, hence "1" is the index number of the 2nd column. (strange, I know...)

Note the "=" after list(0,1)...

I generally use variables such as iCurrentRow and iCurrentColumn, to manually keep track of what row and column I'm populating.

The first entry, though, needs to be done with the "additem" method. I don't know why. Maybe something about allocating memory...

Hope that helps.

-dmreda