In my VBA code I have a query string that pulls all the records from the main table (currently 131,000 records) - I pasted it into SQL view to verify that it does, in fact, pull 131,000 records. I bind it to a listbox like this:

listbox1.RowSource = SQL

When I check the listbox1.ListCount property it is only 65, 534.

At this point I'm not actually sure how many records are showing in the listbox. In other words should I trust the listbox1.ListCount property? Or is it deceiving me?

Is there a limit to the number of records in a listbox?

Jal,

I don't have the specs handy, but 65,534 is one of those nice "power of 2"
numbers.

Would you really want a ListBox with 131,000 items in it. It'd be a bit hard
to use.

Isn't there any way that through some user input you could trim the list down
a bit?

Wayne

Jal,

I don't have the specs handy, but 65,534 is one of those nice "power of 2"
numbers.

Would you really want a ListBox with 131,000 items in it. It'd be a bit hard
to use.

Isn't there any way that through some user input you could trim the list down
a bit?

Wayne
Hmm..The form's Delete button applies to items he has selected for deletion. He can use shift-control-pagedown to select all the items. As it stands, he cannot delete all the items since the listbox isn't showing them all. On the other hand, I suppose he can do it in two passes.

You're right that workarounds are available. However, I was wondering if I maybe had overlooked an option to further expand the listbox capacity.

I would believe that the 65,534 is the limit as to the number of items in a combo and list box.

i have noticed that if you count the number of items in al ist box, an empty list box still seems to count as 1 - [listbox].[ListCount] returns 1 for an empty listbox

so this possibly explains why the limit is 65534 rather than 65535.

I agree with other postings

- there seems little point showing a listbox with 131000 items in it - surely its sufficient to count the items, and show the count - then provide a button to delete them all.

- listcount can take the header into account, but not always; it's different for tables/queries and value lists. you have to be careful when using listcount if the list actually has items in it and the header is on.
- there is an option under tools-options-edit/find (A2003) for showing the number of items in a list. i haven't looked into it in any detail.

Without wanting to seem overly disparaging - even the 65K limit is really OTT, and so 131K would be a terrible use of the control.

If you think about it - the user can't possibly determine that each of the rows they're about to delete is one they want to delete (unless they have a couple of days spare).
So they have loaded (possibly) an entire table's worth of data so that they can delete it without the requirement to see it.

If this is a single user, local database then you're not necessarily hurting anyone by doing so.
Under any other circumstances (and just as a good practice to adopt for future apps) you'd be hammering the network pointlessly - harming other users ability to use your database (and perhaps just databases on the same server on which yours resides).

If wiping out the contents of an entire table is a common functional requirement of your application then certainly you could offer a dedicated means of doing so as others have mentioned.
Even those records are selected by some means - then the user isn't going to be verifying them all anyway. Limit the numbers returned.

On a search form - it's common to offer users cart blanche and their entered criteria returns whatever it does. And if you offer a delete button from there so be it (the thought fills me with dread personally - but there you go :-).
You shouldn't find a subform to be noticeably tardy in rendering data (it's the fetch time which kills you) - and it can be similarly read only too of course.