Improve company productivity with a Business Account.Sign Up


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

Published on
3,266 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

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

Join & Write a Comment

With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month