A2007: Interesting list box bug

[Tony Toews [MVP]]

A client asked why some code behind a list box suddenly started
updating 57386 records in a table instead of the expected 38 records.
Turns out the problem is a difference in behavior between Access 2007
and previous versions of Access. The following code selects all 38
records in the list box in A2003.

For lngLoop = 0 To Me.lstUnreceivedPieces.ListCount - 1
Me.lstUnreceivedPieces.Selected(lngLoop) = True
Next lngLoop

However in Access 2007 the above code also selects the heading line.
You can visually see the difference on the list box between Access
2003 and Access 2007.

Now strictly speaking the code should've started at 1 and not 0 to
avoid selecting the heading line. But that wasn't a problem in A2003
and earlier. And I can certainly see a developer not even thinking
about this issue.

So later on his query then had the following as part of his WHERE
clause

Pieces.pID=pID OR Pieces.pID=52811 OR ...

instead of

Pieces.pID=52811 OR ...

Needless to say all 57386 records were updated.

So now we need to go into every list box in every app and double check
this behavior.

This bug has been reported to Microsoft.

Update: Ken Snell, fellow MVP couldn't recall where he got the ideo
from in the first place but the suggestion is changing the For line to
read

For lngLoop = 0 - (Me.lstUnreceivedPieces.ColumnHeads) To _
(Me.lstUnreceivedPieces.ListCount - 1)

Nice trick that.

Note that this problem happens whether the Multi Select property is
simple or complex.

Update: You can either search through all your code looking for
..selected or run the following code to open all forms with list boxes
in design view.

I didn't want to bore folks with the code so visit the blog entry if
interested in the code.
http://msmvps.com/blogs/access/archi...cess-2007.aspx

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

[AccessVandal via AccessMonster.com]

Hi Tony,

Does the listbox included the property "Column Heads = Yes"?
I had similiar problem whenever i set to "Yes". So My code would be like..

For i = 0 To List0.ListCount - 2

I use "-2" to minus the Column Head.

The error I'm getting is "Type Mismatch" and I'm using A2K.

>Tony Toews [MVP] wrote: snip

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200705/1

[Tony Toews [MVP]]

"AccessVandal via AccessMonster.com" <u18947@uwewrote:

>Does the listbox included the property "Column Heads = Yes"?

Yes, the troublesome listbox is because it has the Column Heads set to yes. In
previous versions of Access Access would ignore that row. In A2007 it does not.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

[Tom van Stiphout]

On Tue, 15 May 2007 04:38:18 GMT, "Tony Toews [MVP]"
<tt****@telusplanet.netwrote:

It is not unusual that with subsequent versions Access has become
stricter about certain expressions. What it means to be Null, and
expressions with Null, is one example. You found another one where
MSFT decided (hopefully consciously) that it's more important to get
it right than to be backward compatible.
I for one applaud them for that.

-Tom.

>"AccessVandal via AccessMonster.com" <u18947@uwewrote:

>>Does the listbox included the property "Column Heads = Yes"?

Yes, the troublesome listbox is because it has the Column Heads set to yes. In
previous versions of Access Access would ignore that row. In A2007 it does not.

Tony

[Tony Toews [MVP]]

Tom van Stiphout <no*************@cox.netwrote:

>It is not unusual that with subsequent versions Access has become
stricter about certain expressions. What it means to be Null, and
expressions with Null, is one example. You found another one where
MSFT decided (hopefully consciously) that it's more important to get
it right than to be backward compatible.
I for one applaud them for that.

Umm, yeah, you could argue that one both ways.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/