Number of rows in a combo?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

We have a combo box that holds the tickers of stocks. Recently the list grew
over 10,000, so some of them fall off the bottom and no longer appear. I
tried looking for the setting for the number of rows to fetch, but I can't
find it. I also tried modifying the lookup query to allow unlimited rows, but
that didn't work either.

Is there a setting for this? Or am I out of luck?

Maury
 
Dear Maury:

The maximum number of rows for a combo box is 65,535. Probably this isn't
the problem. Run the query separately and see. My guess is the problem is
there somewhere.

If you don't see and fix the problem doing this, please post the SQL of the
query here and explain what results you know are missing. It would be
helpful if you would also quote the source data that should generate the
things you say are missing, at least in part.

Tom Ellison
 
Tom Ellison said:
The maximum number of rows for a combo box is 65,535. Probably this isn't
the problem. Run the query separately and see. My guess is the problem is
there somewhere.
Click to expand...

Ok. Well what I tried was opening the query that is embedded in the
Properties by hitting the ... button. It opens into the editor, which I
flipped over the datasheet view, and sure enough, it cuts off at the same
point (in the z's). If I then hit the "extend list" button, the ->* one, the
list expands out to 10300 entries, and the missing rows appear. So it really
does seem like there's a 10000 row limit in place.

When opening the combo and scrolling to the end, the last entry is zzPMT.
This is the 10000th row returned by the query. The missing data is everything
past that point.

It's definitely a setting somewhere!

Maury
 
Maury

When you say "some of them fall off the bottom" do you mean that when you
try to scroll down the list you never get to the last record. If so then try
entering the following code in the Form_Load event of the form containing the
combo box like so :-

Private Sub Form_Load()

Dim vDummy

ComboBoxName.Requery
vDummy = ComboBoxName.ListCount

End Sub

where ComboBoxName is the name of your combo box.
 
Peter Hibbs said:
ComboBoxName.Requery
vDummy = ComboBoxName.ListCount
Click to expand...

I msgboxed it ... 10,000!

So then I did the query in Query Analyzer, literally cut and pasted the
code... 10056!

The I got a brainstorm and set the recordcount on the FORM to 0, but that
didn't help either.

Maury
 
Dear Maury:

I asked you to post the SQL so we can see it.

I do not believe there is a 10,000 row limit. The 65, 535 row limit is in
the combo box. Queries can show millions of rows.

Tom Ellison
 
Maury

You have a form that has to load over 10000 rows in a combo box?! That
might take a bit...

Here's a link to something Allen Browne worked out for just such a
situation...

http://allenbrowne.com/ser-32.html

Good luck

Jeff Boyce
Microsoft Office/Access MVP
 
Are you using ADP or ODBC Pass-Through Query by any chance?

I think both default to max 10,000 rows retrieved (initially?).

If you use ODBC Pass-Through Query, you can change the "Max Records" of the
Query. I have no ideas about Views in ADP.
 
I think you're on the right track here, Van. In an ADP, forms have a
MaxRecords property that defaults to 10,000. A quick test indicates that a
combo box seems to be governed by the MaxRecords property of its parent
form. Here's my test code. The table 'tblTest' contains 20,000 records ...

Private Sub Combo0_DblClick(Cancel As Integer)

Debug.Print Me.Combo0.ListCount
Me.MaxRecords = 20000
Debug.Print Me.Combo0.ListCount
Me.Combo0.Requery
Debug.Print Me.Combo0.ListCount

End Sub

And here is the result ...

10000
10000
20000

In other words, it was necessary a) to set the MaxRecords property to a
sufficiently high value and b) to requery the combo box.
 
Brendan Reynolds said:
I think you're on the right track here, Van. In an ADP, forms have a
MaxRecords property that defaults to 10,000. A quick test indicates that a
combo box seems to be governed by the MaxRecords property of its parent
form. Here's my test code. The table 'tblTest' contains 20,000 records ...
Click to expand...

THANK YOU!

The trick here is twofold. Since my main form consists of a bunch of buttons
and controls, and does not have a recordset of its own, setting the property
in the form does not seem to do anything. However, setting it in code as you
did in your example fixed it up.

Wow, what a weird edge case!

Maury
 
Back
Top