Bug in listbox control

Question

I ran into what seems like a bug in Access' listbox control related to RowSource-bound lists and accessing the RecordSet property.

Environment:

Windows 10, Office 365 Pro Plus. Access Version 1806, Build 10228.20080 Click-to-Run

Steps to reproduce the bug:

1. Start with a blank database.
2. Create a table called Items, with a single field called ID.
3. Add 3 rows to the table: 1,2, and 3.
4. Create a form with a listbox ("ItemList", button, and textbox ("OutputText")
5. Bind the list to the table using RowSource = "SELECT ID FROM Items Order by ID;"
6. Add the following code to the button's click event:

    Dim var As Variant

    OutputText = OutputText & "Value of 2nd item in list: " & ItemList.Column(0, 1) & vbCrLf

    Set var = ItemList.Recordset 'Just looking at this property seems to be causing issues

    OutputText = OutputText & "Value of 2nd item in list: " & ItemList.Column(0, 1) & vbCrLf

When the form opens, the list shows 3 items: 1, 2, and 3.

Clicking the button outputs the following: 

Value of 2nd item in list: 2

Value of 2nd item in list: 1

The first line is correct, since 2 is the 2nd item (with a zero-based index of 1). It appears the Column property begins reporting incorrect values after looking at the Recordset property of the listbox.

Subsequent presses of the button output:

Value of 2nd item in list: 1

Value of 2nd item in list: 1

Interestingly, just clicking on any item in the list and then using the button again makes the code always output the correct values:

Value of 2nd item in list: 2

Value of 2nd item in list: 2

If anyone is curious why I would look at the recordset property in a rowsource-bound list, it's part of a function used in our larger Access application. We have a mix of listboxes populated with RowSource and others bound directly to Recordsets. To get around another oddity in Access, we have a function which returns 0 for a listbox's item count when it has neither a rowsource nor recordset. (Listbox returns 1 in that case, despite having no items to display). This has worked well, put using that "TrueListItemCount" function is causing rowsource-bound lists to report incorrect Column values as above.

I want to say this is a new bug. The Access application is used heavily by many people daily, and we haven't changed the code for looking at listboxes in some time.

Answer 1

Hi Ken.

I don't know if you remember me.  You helped me a lot on  my accounting database,.  I just wanted  to say hello and I hope you are well. :)

Answer 2

Having tried this with an existing list box in a form in one of my own applications, I can confirm the behaviour you have described.  Whether this is a recent bug or not  I can't say, as I've never had occasion to return a reference to a list box control's Recordset before.  I don't think there's any doubt it is a bug, however.

Answer 3

I haven't reproduced your steps, but if you think it might be a recent bug then simply revert a PC to an earlier build no and see if the problem goes away and report back your findings.  It only takes a few moments to do and then you'll have your answer.

http://www.devhut.net/2017/10/04/microsoft-office-uninstall-an-update/

Answer 4

Hi,

I recall having had this similar problem when I used Access 2003.  I'm just going by my memory here, but I think I used the same field name ID.  I don't know if you want to try to change the name from ID to something else, like Identity or something that may not have id in then field name...  I believe you when you say you may have found a bug.  

One question.  Is you data type for ID correct?