Loop through all the selected items in a listbox

[aman]

Hi Guys

The below code doesn't update all the selected items in the listbox . It just loop through the first item multiple items. Can anyone please figure out what's going wrong here ? Thanks

With Me.lstAllocation
     For i = 0 To .ListCount - 1
         If .Selected(i) Then
        
         Set rs = CurrentDb.OpenRecordset("select * from tbl_RMS_MinStand_Mas_Allocation where AllocationID=" & Me.lstAllocation.Column(6) & "")
'        MsgBox Me.lstAllocation.ItemsSelected(i)
         'If Not (rs.BOF = True And rs.EOF = True) Then
            With rs
                 .Edit
                 .Fields("MonthRef") = Me.cboMonthRef.Column(0)
                 .Fields("SupRef") = Me.cboSupervisor1
                 .Update
             End With
      
           Set rs = Nothing
           .Selected(i) = False
        End If
     
     Next i
End With

 

[Gasman]

Walk through the code with the debugger. I cannot see why it does what you say it does?

 

[Cronk]

Requery the list box. Updating the underlying data does not cause the list box display to be updated.

 

[aman]

I have selected first 2 records in a listbox and when i run the code with debugger then it just loops through the first one 2 times.

 

[aman]

the below statement retreives same allocation ID each time it tuns

Set rs = CurrentDb.OpenRecordset("select * from tbl_RMS_MinStand_Mas_Allocation where AllocationID=" & Me.lstAllocation.Column(6) & "")

 

[Gasman]

the below statement retreives same allocation ID each time it tuns

Set rs = CurrentDb.OpenRecordset("select * from tbl_RMS_MinStand_Mas_Allocation where AllocationID=" & Me.lstAllocation.Column(6) & "")

So you have answered it yourself.? You are using the incorrect value to search on.

FWIW when I did something similar for a report I used

Set ctl = Me!lstCrew
' Now select what records from listbox
If ctl.ItemsSelected.Count > 0 Then
    For Each varItem In ctl.ItemsSelected
        strParam = strParam & ctl.ItemData(varItem) & ","
    Next varItem
  Else
    MsgBox ("At least one Day Type is required")
    Exit Sub
End If

HTH

 

[aman]

I need to retrieve Column(6) value from a listbox each time so how can this be changed ?

Me.lstAllocation.Column(6)

 

[aman]

got it working . changed to below:

me.lstAllocation.column(6,i)

 

[Gasman]

When silly things like that happen, I always go to the debugger. That then shows me where it does not do what I thought it was going to do.

 

[The_Doc_Man]

It just loop through the first item multiple items.

Note that the code showed in post #1 of this thread steps through the list box but never changes the position of the RecordSet (i.e. has no RS.MoveNext command inside the loop).

 

[Minty]

@Doc - I think the recordset is specific to the outer loop (i) value hence it's recreated every iteration? e.g it's only retrieving a single record (I assume)

 

[The_Doc_Man]

OK, Minty, noted. I missed something earlier. The fact that the row number wasn't part of the selector escaped my attention.

 

[misscrf]

Thank you for posting this solution. It's exactly what I needed, to use some listboxes for building criteria and appending that criteria onto a dynamic sql statement. Works like a charm!

So you have answered it yourself.? You are using the incorrect value to search on.

FWIW when I did something similar for a report I used

Set ctl = Me!lstCrew
' Now select what records from listbox
If ctl.ItemsSelected.Count > 0 Then
    For Each varItem In ctl.ItemsSelected
        strParam = strParam & ctl.ItemData(varItem) & ","
    Next varItem
  Else
    MsgBox ("At least one Day Type is required")
    Exit Sub
End If

HTH