CODEWITHSUNDEEP
ms-accesslistboxvbams-access-2010
Jerry Travers
Jerry Travers

Reputation: 71

Listbox.Listcount not accurate

Searched this and other sites but could not find an answer as to why I am not seeing a correct listcount for a listbox when adding items from a recordset. Most other threads focus either on the count being off by one due to column headers on the listbox or a 65,534 physical limit. I am seeing something a bit different

Running Access 2010 with linked SQL Server 2008 tables.

Code:

lstSearchResults.RowSource = ""
lstSearchResults.AddItem ("Material Number;Description")
Set rsParts = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)

If rsParts.RecordCount > 0 Then
    rsParts.MoveLast
    rsParts.MoveFirst
    lngCount = 0
    Do While Not rsParts.EOF
        lngCount = lngCount + 1
        lstSearchResults.AddItem (rsParts.Fields("MaterialNumber") & ";" & rsParts.Fields("Description"))
        rsParts.MoveNext
    Loop
End If

Placing a breakpoint just after the loop I examined the following values:

lstSearchResults.ListCount - 1012
rsParts.RecordCount - 5255
lngCount - 5255

Upvotes: 2

Views: 2170

Answers (1)

HansUp
HansUp

Reputation: 97131

Since you're using the AddItem method, that means your list box's Row Source Type property must be "Value List". And that means the Record Source property is a string which contains that list of values. And there is a limit to the number of characters which can be included in a value list. Although I don't recall the number for that limit, I suspect that is the reason your value list does not contain all the values you expect.

But there is a simpler way to load the list box values. You have a query, strSQL, for which you open a recordset, then walk the recordset to add 2 field values from each row to the value list.

Use the SQL statement directly ... without the recordset as an intermediary.

  1. Change the list box's Row Source Type to "Table/Query".
  2. Use the SQL statement as the list box's Row Source property.

That approach is less fuss and avoids the Value List character limit. Then you only need to decide whether 1K or more rows is really appropriate for a list box. :-)

Upvotes: 2

Related Questions