Bug identified in Access 2007 Listbox .ListIndex and .Column() properties

Dale FyeOwner, Dev-Soln LLC
Beware when using the ListIndex and the Column() properties of a listbox in Access 2007.  A bug has been identified in the Access 2007 listbox code which can cause the .ListIndex property to return a -1, and the .Columns(#) property to return a NULL value.

While working on part of an application (attached) which allows the user to sort items in a list, I identified that the ListIndex property of the listbox was not working properly.  Although the value associated with the listbox does change and accurately represents the value of the bound column in the listbox, the ListIndex property of the listbox always returns a value of -1.  Additionally, references to the .Column(#) property of the listbox will return NULL.

The attached database contains two forms which are supposed to enable and disable the up and down arrow buttons associated with the item selected in the listbox.  In the first of these, "frmQueryReports", the click event contains code which refers to the listindex properly of the listbox and compares that value to the ListCount property.  I've added a debug.print line to the code which allows you to see that the value property returns the value associated with the leftmost (bound) column of the listbox, but the .listindex property always displays -1 and the .Column(1) always returns NULL.  Because the listindex property does not work properly, you will see that the up button is never enabled and the down button is always enabled.

In the second form "frmQueryReports_Modified", I've modified the code in the listbox Click event to avoid use of the .ListIndex property.  You will note that the up and down arrows are enabled/disabled appropriately based on the item selected in the listbox.

During testing, we found that you can also use the listbox AfterUpdate property to perform the test originally placed in the Click event, but this is not without flaws.  When we moved the debug.print line and the two lines which enable the up and down button into the AfterUpdate event (line numbers 80-100 in lst_QueryReports_AfterUpdate event), by themselves, the .ListIndex property works properly.  You can see this removing the remarks from those lines and running the  "Modified" form.  You will see the correct values of the bound column, column(1), and the ListIndex displayed in the immediate window.  But if you remove the remarks from lines 10 - 70 in that event, then the error again raises its ugly head.

Bottom line, be careful when using the .ListIndex and .Column( ) properties of listboxes in Access 2007.

I and other Access MVP's have tested this in other versions of Access, including 2003, 2010, 2013, and 2016 and this bug does not appear to affect any of those other versions.  Because of this, it is unlikely that Microsoft will provide an update to A2007 to fix this bug.


Dale FyeOwner, Dev-Soln LLC

Comments (0)

Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.

Get access with a 7-day free trial.
You Belong in the World's Smartest IT Community