How to limit records displayed in ListBox?

[deko]

Is there any way to limit the number of records loaded into a ListBox?

I looked at qdf.MaxRecords (to apply to the query that is the RowSource of
the ListBox) but that only applies to ODBC data sources. I also looked at
Tools > Options > Edit/Find and tried setting the "Don't display lists where
more than this number of records read:" property, but that doesn't help.

The List Box in question is supposed to allow scrolling/browsing of all
records in the database, but that gets to be a problem with tens of
thousands of records.

There are a number of options the user has to sort/filter/narrow the list to
find whatever is being searched for, so I'd like to limit the list to
something around 10,000 which would encourage the user to use the those
other features. Can this be done? Do I have to use something like SELECT
TOP 10000 [records] FROM tblData?

[Wayne Morgan]

The option on the Edit/Find tab is for the combo boxes when you go to
"filter by form".

One option, as you mentioned, would be to use a TOP query. Another option
would be to assign the row source to the list box via code. You could do a
count of the records returned in the code prior to assigning the query to
the row source. If the count is too great, popup a message to the user
telling them that too many records have been returned and to refine their
search. If the count is within reason, assign the query to the row source.

I think I remember reading that there is an option you can set in the
listbox or combo box when using an adp file to connect to SQL Server or
MSDE.

--
Wayne Morgan
MS Access MVP
"deko" <de**@nospam.com> wrote in message
news:HP********************@comcast.com...

Is there any way to limit the number of records loaded into a ListBox?

I looked at qdf.MaxRecords (to apply to the query that is the RowSource of
the ListBox) but that only applies to ODBC data sources. I also looked at
Tools > Options > Edit/Find and tried setting the "Don't display lists
where more than this number of records read:" property, but that doesn't
help.

The List Box in question is supposed to allow scrolling/browsing of all
records in the database, but that gets to be a problem with tens of
thousands of records.

There are a number of options the user has to sort/filter/narrow the list
to find whatever is being searched for, so I'd like to limit the list to
something around 10,000 which would encourage the user to use the those
other features. Can this be done? Do I have to use something like SELECT
TOP 10000 [records] FROM tblData?

[deko]

> One option, as you mentioned, would be to use a TOP query. Another option

would be to assign the row source to the list box via code. You could do a
count of the records returned in the code prior to assigning the query to
the row source. If the count is too great, popup a message to the user
telling them that too many records have been returned and to refine their
search. If the count is within reason, assign the query to the row source.

I think I remember reading that there is an option you can set in the
listbox or combo box when using an adp file to connect to SQL Server or
MSDE.

TOP it is. In the Preferences Form of the app I let the user set the limit.
Of course I now have to dynamically build the SQL string and assign it the
query used for the RowSource. What I don't like about TOP is that it
returns records with lower autonumbered PKs first. So instead of returning
the most recent 10000 records entered, it returns the oldest 10000. I could
use ORDER BY to reverse this, but that slows down the query to the point
where it negates any speed increase gained by the lower record count.

[Tom van Stiphout]

On Mon, 22 Aug 2005 20:13:41 -0700, "deko" <de**@nospam.com> wrote:

In my mind if you do Top, you must also do OrderBy.
Not doing so, would typically return the records in PK order, but if I
understand SQL correctly, that is NOT a guarantee. I would gladly
sacrifice some performance for predictability.

OrderBy is not slow if issued on an indexed column.

-Tom.

One option, as you mentioned, would be to use a TOP query. Another option
would be to assign the row source to the list box via code. You could do a
count of the records returned in the code prior to assigning the query to
the row source. If the count is too great, popup a message to the user
telling them that too many records have been returned and to refine their
search. If the count is within reason, assign the query to the row source.

I think I remember reading that there is an option you can set in the
listbox or combo box when using an adp file to connect to SQL Server or
MSDE.

TOP it is. In the Preferences Form of the app I let the user set the limit.
Of course I now have to dynamically build the SQL string and assign it the
query used for the RowSource. What I don't like about TOP is that it
returns records with lower autonumbered PKs first. So instead of returning
the most recent 10000 records entered, it returns the oldest 10000. I could
use ORDER BY to reverse this, but that slows down the query to the point
where it negates any speed increase gained by the lower record count.