[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More


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

Published on
3,581 Points
Last Modified:
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.


Author:Dale Fye

Featured Post

Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
A query can call a function, and a function can call Excel, even though we are in Access. This is Part 2, and steps you through the VBA that "wraps" Excel functionality so we can use its worksheet functions in Access. The declaration statement de…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month