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 download the file and open it in both A2003 or earlier and A2007 to see the difference.
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.
Sub FindFormsWithListBoxes()
Dim doc As Document, ctl As Control, Count As Long, Form As Form
Dim db As Database, ListBoxOnThisForm As Boolean
On Error GoTo FindFormsWithListBoxes_Error
Set db = CurrentDb
For Each doc In db.Containers!Forms.Documents
ListBoxOnThisForm = False
DoCmd.OpenForm doc.Name, acDesign
Set Form = Forms(doc.Name)
For Each ctl In Form.Controls
If ctl.ControlType = acListBox Then
ListBoxOnThisForm = True
Exit For
End If
Next ctl
If ListBoxOnThisForm = False Then
DoCmd.Close acForm, doc.Name
Else
Count = Count + 1
End If
tagNextForm:
Next doc
MsgBox "The " & Count & " forms with listboxes have been left open in design view."
On Error GoTo 0
Exit Sub
FindFormsWithListBoxes_Error:
Select Case Err.Number
Case 7784 ' This form or report is already being designed as a subform or subreport
msgbox doc.Name & " is a subform on an already open form so is being ignored."
Resume tagNextForm
Case Else
MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure FindFormsWithListBoxes of Module Module1"
End Select
Exit Sub
Resume
End Sub