Listbox only returning 495 rows, should be closer to 1000

[Krista H]

From what I can find my Listbox should return up to 65K rows. Any ideas why
mine is maxing out around 495 rows, the exact same query returns 1042 records
in a report.

I looked at all the properties on the listbox and cannot find anything that
would create this limit... not sure where else to look. I added .listcount to
the code to see the number of listbox rows returned. For another queries on
the same data set I get 492 of 1179 rows displayed in the listbox, again all
1179 show up in the report using the exact same query.

Thanks.

 

[Jeff Boyce]

Krista

Please provide the SQL statement that "feeds" your listbox.

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[Krista H]

Ok, I just realized there is nothing wrong with the SQL Statement, it is
returning the correct number of records. However I am passing the recordset
and listbox to a subroutine that appears to be the problem. One note, the sql
statement is complicated because it is for a user search form and is built
dynamically based on what the user enters in the search form.

---->Here is the SQL Statement:
SELECT DISTINCT tbl_Product.ProductNo, tbl_Product.ProductName,
tbl_WebOptions.WebOption, tbl_Product.SpecialOrder FROM tbl_WebOptions RIGHT
JOIN (((tbl_ProductCategories RIGHT JOIN tbl_Product ON
tbl_ProductCategories.ProductNo = tbl_Product.ProductNo) LEFT JOIN
tbl_ProductFeatures ON tbl_Product.ProductNo = tbl_ProductFeatures.ProductNo)
LEFT JOIN tbl_ProductMaterials ON tbl_Product.ProductNo =
tbl_ProductMaterials.ProductNo) ON tbl_WebOptions.WebOptionId =
tbl_Product.WebOption WHERE tbl_Product.WebOption= 2 ORDER BY
tbl_Product.ProductNo
-------------
(returns 1042 records, I check the .recordcount)

the results of the query are stored in a recordset variable rstResults

then I used the following code to call a populate list box subroutine
--------------------------------------
PopulateListFromRecordset lst_Results, rstResults, MyHeading, 4
--------------------------------------

Here is the subroutine, which is only displaying 495 of the rows.
-------------------------------------
Public Sub PopulateListFromRecordset(lstList As ListBox, rsRecordset As
Recordset, _
colHeading As String, intNumCols As Integer)

On Error GoTo Handle_Error

Dim intCounter As Integer
Dim strItem As String

With lstList
.RowSource = ""
.ColumnCount = intNumCols
.RowSourceType = "Value List"
End With


'add all of the values in the recordset to the list box
lstList.AddItem (colHeading)

Do Until rsRecordset.EOF
'for each item in the current record, build string
For intCounter = 0 To intNumCols - 1
strItem = strItem & rsRecordset(intCounter).Value & ";"

Next intCounter
strItem = Replace(strItem, ",", "")
lstList.AddItem (strItem)
strItem = ""
rsRecordset.MoveNext
Loop

Exit Sub

Handle_Error:
MsgBox Err.Description
Exit Sub
End Sub

thanks

Krista

 

[Krista H]

I decided to set the listbox.rowsource directly to the sqlStatement. I had
used the populate subroutine to add the headings dynamically, but was
planning to get rid of it soon anyway to add a sort feature to the search
results. (plus the list box populates MUCH faster now and I know the customer
will like that.)

Thanks.

 

[Tom Wickerath]

Hi Krista,

I was interested in this issue, because I've heard of limitations associated
with the Value List as a row source in the past. I wasn't able to find
anything that documents the limit, but some quicky testing that I have done,
in Access 2003, seems to indicate that the maximum size of the list is 33,206
characters. This includes the length of the heading plus the length of the
records added. I suspect that by the time you had added 495 records, you had
come to this limit.

Private Sub cmdPopulateListbox_Click()
Dim rstResults As DAO.Recordset
Dim MyHeading As String

Set rstResults = CurrentDb.OpenRecordset _
("SELECT * FROM AccessAndJetErrors")

MyHeading = "1234567890;123456789012345"

PopulateListFromRecordset lst_Results, rstResults, MyHeading, 2

End Sub


Public Sub PopulateListFromRecordset _
(lstList As ListBox, rsRecordset As Recordset, _
colHeading As String, intNumCols As Integer)

On Error GoTo Handle_Error

Dim intCounter As Integer
Dim strItem As String
Dim lngCharCount As Long '<-------Added this

lngCharCount = Len(colHeading) '<-------Added this
:
:
:
Do Until rsRecordset.EOF
'for each item in the current record, build string
For intCounter = 0 To intNumCols - 1
strItem = strItem & rsRecordset(intCounter).Value & ";"

Next intCounter
strItem = Replace(strItem, ",", "")
lstList.AddItem (strItem)
lngCharCount = lngCharCount + Len(strItem) '<-------Added this
Debug.Print lngCharCount '<-------Added this
strItem = ""
rsRecordset.MoveNext
Loop

End Sub


The maximum result that I can attain in the Immediate Window, without
producing the error, is 33,206 characters.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Krista H]

Thank you, I had a feeling there was a limit somewhere - glad I could switch
back to the table/query recordtype and avoid the problem.

One interesting thing to note is that on Access 2003 at the customer site, I
was able to get an error message that said it could not display all the data,
however on 2007/Vista - same code set - I did not get the message, yet the
data was still truncated. Not sure if there are access specific settings on
my 2007 vista that supressed the message. Anyway I did think it was strange
the two versions responded differently.