Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.
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.
|How to create an Access database for a business overview||2,298|
|Create Windows Phone Colour Palette and Selector using WithEvents||865|
|MS Access VBA error 3146 when using a string longer than 128 characters in a WHERE clause with SQL Server||147|
|Protecting Queries , Macros And Table Links in MS Access 2016||97|