ItemsSelected in multiselect listbox 1

[sharkboots]

I'm using Access 97 and have some listboxes with extended multiselect enabled that are making me want to puke. I find that selecting all the fields in the listbox (using shift click) then deselected some (using ctrl-click) leads to errors when I run code that uses the ItemSelected array. The ItemsSelected array seems to break down under these circumstances and outputs around half of the recently unselected listrows as selected and throws in some non-existant listrows for good measure. Does anybody know why this is so and if anything can be done about it?

 

[jebry]

Hi!

Post the code you are using to find the items selected. Maybe we can find something there.



Jeff Bridgham
bridgham@purdue.edu

 

[sharkboots]

OK, I've been trying a few things so I put two alternatives in here, the results are identical. Any thoughts you may have on which alternative is more efficient code would be appreciated. The 'if isnull(mylist.itemdata(myselection)..' bit is to prevent those inexplicable non-existent listrow records being inserted.

Dim mySelection As Variant, myCount As Byte, myRecord As Integer, myTable As Recordset, myDB As Database, myList As Control
strquote = Chr(34)
Set myDB = CurrentDb()
Set myList = List93
myCount = myList.ListCount

'***Alternative One***
For Each mySelection In myList.ItemsSelected
If IsNull(myList.ItemData(mySelection)) Then
Exit For
Else
Set myTable = myDB.OpenRecordset("meetings2&quot
myRecord = Me!Record
myTable.AddNew
myTable!Record = myRecord
myTable!Member = myList.ItemData(mySelection)
myTable.Update
myTable.Close
End If
Next mySelection

Me.List93 = vbNullString
Me.List95 = vbNullString
Me.Recalc

'***Alternative Two***
If IsNull(List93.ItemData(mySelection)) Or mySelection > myCount Then
List93.Selected(mySelection) = False
Exit For
Else
myRecord = Me!Record
DoCmd.SetWarnings False
DoCmd.RunSQL ("insert into meetings2 (member,record) values (" & List93.ItemData(mySelection) & "," & myRecord & "&quot
List93.Selected(mySelection) = False
List95.Requery
DoCmd.SetWarnings True
End If
Next mySelection

Me.List93 = vbNullString
Me.List95 = vbNullString
Me.Recalc

 

[CajunCenturion]

First of all, instead of declaring myList as a Control, I would declare it as a ListBox. For efficiency sake, I would not constantly be opening and closing the table.

In alternative one, I see a problem. The first is that as soon as you encounter a null entry, you exit the for loop, rather than just skipping that one entry.

You might try the following:

Dim myList as ListBox
Set myList = List93

Set myTable = myDB.OpenRecordset("meetings2")
For Each mySelection In myList.ItemsSelected
   If Not IsNull(myList.ItemData(mySelection)) Then
      myRecord = Me!Record
      myTable.AddNew
      myTable!Record = myRecord
      myTable!Member = myList.ItemData(mySelection)
      myTable.Update
   End If
Next mySelection
myTable.Close

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[sharkboots]

Thanks very much,

I was initially running this code without the 'exit for' - this was just one of many futile amendments I tried. The 'null' entries only occur when the itemsselected value is higher than the number of listrows, so the first one encountered is always after all the valid info has been dealt with. Thanks again for the good efficiency suggestions. I'll keep plugging away.

 

[CajunCenturion]

You might also try the following approach:

Set myList = List93
Set myTable = myDB.OpenRecordset("meetings2")
For Idx = 0 To myList.ListCount - 1
   If (myList.Selected(Idx)) Then
      myRecord = Me!Record
      myTable.AddNew
      myTable!Record = myRecord
      myTable!Member = myList.ItemData(Idx)
      myTable.Update
   End If
Next Idx
myTable.Close

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[sharkboots]

Wee-heh! That works. I think this deserves a star and I strongly recommend that anybody using multiselect listboxes check that things are working EXACTLY as expected in ALL circumstances, and switch to this form of coding if anything is awry. I think the problem in my case may have stemmed from the nature of the query populating my listbox.