Limit of 150 rows in a combobox?

[mdupris]

I can't find an answer to this in the newgroup postings, but it seems
an obvious question so I'm probably missing something: I have an
Access 2003 SP3 ADP file. In one form there is a combo box whose
datasource is a SQL Server stored procedure. The stored procedure
returns 3 columns, 200 rows when executed on SQL Server. The combo box
on the form, however, shows just the first 150 rows in the scrolling
region. What do I need to do to have it show all 200 rows?
The "Tools- Options - Edit/Find" "Don't display lists..." is set to
1000,. The .ListCount for the combobox reports 150 on open, which is
consistent, though not correct. It almost seems that there is some
filter between the stored procedure on SQL Server and Access which
imposes this 150 row limit, but I can't imagine what it is or where it
is. Any advice on finding these missing rows would be most
appreciated!

= M =

 

[Jeff Boyce]

I'm not aware of such a limit...

What happens if you come up with a query from Access that returns the same
rows as your stored procedure, then use that query as the source?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[mdupris]

Jeff,

Thanks for your response, and my apologies for seeming to ignore it
for so long. Other projects interfered with this one and I'm now
getting back to it.
It seems the problem was indeed an Access setting. The Tools >
Options > Advanced > "Default max records" box was set to 150, which
is the 'filter' I suspected existed somewhere but didn’t know where.
Another day, a better search criteria, and this popped up as a
setting.
There are a couple MS knowledge base articles on this as well.
http://office.microsoft.com/en-us/access/HP052741321033.aspx covers
it, with several suggested solutions. Microsoft also addresses this
issue re:Access 2002 in this article:
http://support.microsoft.com/kb/287429

From what I've found, though, their 'Method 2' of putting 'SET
ROWCOUNT 0' within the stored procedures is ineffective. I still get
just 150 rows, even after setting the Tools / Options ... to 2000
(I didn't try the 'Method 3' in their article as it would be a fair re-
write for a problem easier solved elsewhere).

Since 150 is far less than the Access default, I suspect that
somebody, some time on this machine ran an Access application which
executed a line like:
Application.SetOption "Row Limit", "150"
and rudely forget to set it back to the default later Since this is an
application level setting, and persists across Access sessions, this
kind of oversight can have unexpected consequences down the line.
In any event, the current problem is solved. Whew!

= M =

 

[Jeff Boyce]

Thanks for the clarification and for posting back your solution. Other
folks in the future will benefit from what you found.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff,

Thanks for your response, and my apologies for seeming to ignore it
for so long. Other projects interfered with this one and I'm now
getting back to it.
It seems the problem was indeed an Access setting. The Tools >
Options > Advanced > "Default max records" box was set to 150, which
is the 'filter' I suspected existed somewhere but didn’t know where.
Another day, a better search criteria, and this popped up as a
setting.
There are a couple MS knowledge base articles on this as well.
http://office.microsoft.com/en-us/access/HP052741321033.aspx covers
it, with several suggested solutions. Microsoft also addresses this
issue re:Access 2002 in this article:
http://support.microsoft.com/kb/287429

From what I've found, though, their 'Method 2' of putting 'SET
ROWCOUNT 0' within the stored procedures is ineffective. I still get
just 150 rows, even after setting the Tools / Options ... to 2000
(I didn't try the 'Method 3' in their article as it would be a fair re-
write for a problem easier solved elsewhere).

Since 150 is far less than the Access default, I suspect that
somebody, some time on this machine ran an Access application which
executed a line like:
Application.SetOption "Row Limit", "150"
and rudely forget to set it back to the default later Since this is an
application level setting, and persists across Access sessions, this
kind of oversight can have unexpected consequences down the line.
In any event, the current problem is solved. Whew!

= M =