Combo Box error when populating records

[microb0x]

I have a combo box with 3 columns visible and one hidden. When
populating the cbx with records from a table I'm getting the following
error:

The setting for this property is too long.
Error Number: 2176

This is happening at the 676th record. I was under the impression a
combobox could hold way more rows than that. Can anyone shed some
light on this problem?

Here is the code from the sub that is populating this cbx:


Public Sub subFillAccountCBX()
On Error GoTo ErrHandle

sqlWHERE = "SELECT [Customer Name],[Property or
Casualty],[Office],[ID] " & _
"FROM [2006 Key Target List] ORDER BY [Customer Name]"

Set con1 = CurrentProject.Connection

Set recSet1 = New ADODB.Recordset
recSet1.Open sqlWHERE, con1, adOpenKeyset, adLockOptimistic

Do Until recSet1.EOF
'Add each record in recset1 to cbx on frmIntroPage
Form_frmIntroPage.cbxExistingAccount.AddItem ("""" & recSet1(0)
& """" & ";" & """" & recSet1(1) & """" & ";" & """" & recSet1(2) &
"""" & ";" & recSet1(3))
recSet1.MoveNext
Loop

recSet1.Close
con1.Close
Set con1 = Nothing
Set recSet1 = Nothing

ExitSub:
Exit Sub

ErrHandle:
MsgBox Err.Description & vbNewLine & vbNewLine & "Error Number: " &
Err.Number
Resume ExitSub

End Sub

 

[Ron2005]

It looks as if you are trying to load the data from the query into the
value list for the combobox.

Instead, use the query itself as the rowsource.

 

[microb0x]

Is there a different limit for the combo box based off populating a
value list or setting a query as the rowsource? Can I even set the
cbx's rowsource to a query if I have to manually add a header row(which
is not a result of the query obviously)? I thought you cannot manually
add a row when the rowsource is set to a table/query instead of Value
list?

 

[Van T. Dinh]

There is a limit on the length of the Value List which I assume AddItem
uses.

AFAICS, you should be able to use the SQL String directly as the RowSource
(with RowSourceType "Table/Query") for the ComboBox rather than using
Recordset and AddItem.

 

[microb0x]

How can I get my header row in the ComboBox if the RowSourceType is
"Table/Query"? Adding my customized header row(the labels don't match
that of the ajoining fields in tabel) was the only reason I set it to
"Value List" in the first place, as that seems to be the only type that
allows you to add the header the way you want. Do you know of another
way?

I appreciate the assistance.

 

[Van T. Dinh]

If you need the Header row with Column Headings different from the Field
names then use Aliases in your SQL like:

SELECT Field1 As [Column Header 1], Field2 As ...

and "Column Header 1" will appear in the Header row.

 

[Ron2005]

and set the header property for the combo box to yes

 

[microb0x]

Thank you very much, this worked perfectly..