VBA - Unwanted Listcount Representation Changes Based On Selection

0
votes

I'm using Access 2003 to develop an application where a user can dynamically generate a query, and then browse through a listbox with the results of their GUI defined query. I allow the column heads to show which counts as an extra row in the results.

The amount of rows returned is limited by the listbox's physical limit of 65535. I also have a list box showing Row X Of Y Selected in a textbox.

The message is generated each time the user clicks in the listbox, the code in the event handler is:

Me!txtRowCount.Value = "Row " & (Me!listDynamicSearchResult.ListIndex + 1) & _ " Of " & (Me!listDynamicSearchResult.ListCount - 1) & " Selected"

Usually the code shows Row X of 65534 Selected, since I opt out counting the header in the listbox. As the user gets closer to the maximum value, the Y value, 65534, changes to 65535 and stays at that max number until I requery or clear the form.

So, why does the listcount property not show the appropriate value until I am closer to the maximum value?

1
vbams-accesslistboxms-access-2003
Also, the actual listcount value changes from 65535 to 65536. – Elias

1 Answers

1
votes

The behavior might be something similar to what happens in regard to recordsets and counting within recordsets

Before we get into the examples, you need to understand how RecordCount works. 
It does not tell you the number of records in a recordset. Rather, it tells you
the count of records accessed in the Recordset. They may or may not be the same
thing.

Source: http://www.baldyweb.com/RecordCounts.htm

A workaround might be to use DCount but I'm guessing that's potentially expensive to call on 64k lines