combobox: Can't add this item, index is too large

[jamest85]

Hi:

I can add data to a combobox if there is only one colum in the combobox, now I am trying to add data to a two colums combobox using VBA code, but I keep getting this error: Run-time error "6015', can't add this item, the index is too large, here is the code:

Me.cmboCategory.ColumnCount = 2
Me.cmboCategory.ColumnWidths = "1cm; 2cm"
Me.cmboCategory.BoundColumn = 1

Do Until rst.EOF
Me.cmboCategory.AddItem rst!CategoryID, 1
Me.cmboCategory.AddItem rst!CategoryName, 2
rst.MoveNext
Loop

The error happens on line: Me.cmboCategory.AddItem rst!CategoryID, 1

Thank you very much.

Jt

 

[dkinley]

I know there is a maximum number of entries a combo box can have .. just can't recall. You can do a google search on combo box maximum and there is code out there to allow you to supercede this number.

Someone here might know off the top of their head.

-dK

 

[jamest85]

I know there is a maximum number of entries a combo box can have .. just can't recall. You can do a google search on combo box maximum and there is code out there to allow you to supercede this number.

Someone here might know off the top of their head.

-dK

I don't think my total record number excede the maximum number, because I can list all the category names (from Northwind) in a single column combo box, now I want to add CategoryID in second column.

I am pretty sure I have syntax error, but I just don't know where is the problem.

Thanks
jt

 

[dkinley]

How about ....

With rst
  Do Until .EOF
    Me.cmboCategory.AddItem .Fields("CategoryID").Value & _
       ";" & .Fields("CategoryName").Value
    .MoveNext
  Loop
End With

-dK

 

[jamest85]

How about ....

With rst
  Do Until .EOF
    Me.cmboCategory.AddItem .Fields("CategoryID").Value & _
       ";" & .Fields("CategoryName").Value
    .MoveNext
  Loop
End With

-dK

Hi: Dk
Thanks for your help.
However, it doesn't work, all I want is: add rst values to a 2 columns combobox by using vba.
Any other suggestion?
Thanks
JT

 

[dkinley]

Hmmm ... if replacing your ...

Do Until rst.EOF
Me.cmboCategory.AddItem rst!CategoryID, 1
Me.cmboCategory.AddItem rst!CategoryName, 2
rst.MoveNext
Loop

with my ...

With rst
  Do Until .EOF
    Me.cmboCategory.AddItem .Fields("CategoryID").Value & _
       ";" & .Fields("CategoryName").Value
    .MoveNext
  Loop
End With

didn't work ... going to have to ask if anyone can help you out - this is the way I've always done it.

My apologies it didn't work - anyone else?

-dK

 

[DCrake]

The most obvious way is to have the combobox set up as having 2 columns and the widths and bound columns set accordingly. Then use the RowSource Property to point to the underlying table or query or sql statement. This will work quicker as Access has to only validate the syntax and not the population of the combobox. This will be done when the combo is first opened.

CodeMaster:

 

[Wes]

AddItem adds a new row to the combobox so
Me.cmboCategory.AddItem rst!CategoryID, 1
Me.cmboCategory.AddItem rst!CategoryName, 2
adds CategoryID to row 1, column 1 and then CategoryName to row 2, column 1

You need to concantenate both items, seperated by a semi-colon at the time of the AddItem. I believe commas may also seperate columns.

I recommend creating a temporary string containing all column values for one row and passing that string to AddItem. Depending on the values in your columns you may also need to account for single quotes, commas and semicolons.

Here is a snippet of my code to do this. Notice the single quotes on both sides of the column value. This ensures that the semi-colon is recognized as the column seperator.
strTemp = "'" & rst.Fields(0) & "';'" & OneQuote(rst.Fields(1)) & "';'" _
& rst.Fields(2) & "'"
cmbMatter.AddItem strTemp

Public Function OneQuote(ByVal strTemp As String) As String
OneQuote = Replace(Trim(strTemp), "'", "''")
End Function

Hope this helps.