T
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/arch...mn-headings-of-list-boxes-in-access-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/
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/arch...mn-headings-of-list-boxes-in-access-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/