ItemsSelected.Count not working

[AlanS]

I'm running Access 97 under Windows XP. On an unbound form, I have an unbound list box (lstItems) whose MultiSelect property is set to Extended, and whose RowSource is a select query; some of the query's criteria are based on the values in other fields on the same form. The application requires that, whenever the list box's selections are changed, I need to recalculate (a) the number of items selected, and (b) the sum of the values in a particular column in the selected items.

In the list box's AfterUpdate event procedure, I'm using lstItems.ItemsSelected.Count to determine the number of selected items, and the following code to compute the sum:

Dim PaymentsSelected As Currency, V As Variant, C As Currency
For Each V In lstItems.ItemsSelected
C = Nz(lstItems.Column(6, V), 0)
PaymentsSelected = PaymentsSelected + C
Next V

All this works fine in most cases. However, I've noticed that about 5 percent of the time something bizarre happens: holding the Control key down, I click on a particular item to select or de-select it and it gains or loses a highlight to reflect that change. However, neither the count nor the sum is updated. This occurs even though the AfterUpdate event is firing in all cases, and even if I add the following code:

lstItems.Requery: Me.Requery: Me.Repaint

Does anyone know why this is happening or how I can get around it?

 

[llkhoutx]

dim varItem as variant

with me!lst
form each varitem in .itemsselected
'you can process items selected here, one by one
next
end with

 

[Rich]

Private Sub List6_Click()
Dim ctl As Control, varItm As Variant, Answer As Long
Dim Criteria(30) As Currency
Set ctl = Me![List6]
For Each varItm In ctl.ItemsSelected
Criteria(varItm) = ctl.Column(2, varItm)
Answer = Answer + Criteria(varItm)
Next varItm

Me.Text8 = Answer
End Sub

 

[AlanS]

What llkhoutx and Rich are suggesting (looping through all the elements of the ItemsSelected collection in order to sum them) is what I'm already doing to calculate the new sum. The problem remains - for whatever reason, the collection does not seem to reflect the latest selection or de-selection, even though that change is reflected on the screen.

 

[Rich]

Yes, I have to say that I had problems when trying to select too many items, out of stack memory, etc. I went a different route in the end

 

[llkhoutx]

You say that you're summing on the ListBox "AfterUpdate" event.

I'm not sure that I understand how that fires and what rows are selected, if any, when it does. Remember, I'm stupid.

Try the triggering the sum on the button click event.

 

[Rich]

Did you try Me.Recalc or Me.Refresh?

 

[AlanS]

llkhoutx: The AfterUpdate event fires immediately after a control's value has been changed by the user. In the case of a list box, that means whenever the user clicks on one of the displayed items to change which item(s) is/are selected. In my form, this event always fires when it's supposed to, the event procedure always executes, and the list box's highlight always reflects the change, but somehow the list box's ItemsSelected collection doesn't detect the change.

Rich: I've added Me.Recalc and Me.Refresh, but the problem persists. Also, each one of these extra methods slows the screen refresh down unacceptably - with both of them inserted the list box goes blank for three seconds before returning with the changed selections. Here's the current code:

Private Sub lstItems_AfterUpdate()
UpdateForm 'updates other controls on this form
lstItems.Requery
Me.Requery
Me.Recalc
Me.Refresh
Me.Repaint
MsgBox lstItems.ItemsSelected.Count 'inserted just for debugging
End Sub

 

[AlanS]

Looks like it's solved now. Although I could find no mention of this in Microsoft's KnowledgeBase, it appears that the ItemsSelected collection (including its Count property) is "unreliable" - see http://www.rogersaccesslibrary.com/download.asp?SampleName='MultiSelectProblem.mdb' for examples of both what works and what does not. The solution seems to be to use ListBox.Selected, which is a zero-based array of Boolean values, each of which indicates whether the corresponding list box item is selected. One can examine the entire array (to count the selected elements, summarize the values in a particular column of the selected items, etc.) with code like this:

Dim ISel As Integer, PSel As Currency, I As Integer
ISel = 0
PSel = 0
With lstItems
For I = 0 To .ListCount - 1
If .Selected(I) Then
ISel = ISel + 1
PSel = PSel + Nz(.Column(6, I), 0)
End If
Next I
End With