Loop through all the selected items in a listbox (1 Viewer)

aman · Oct 26, 2018

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

Code:

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 · Oct 26, 2018

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

Cronk · Oct 26, 2018

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

aman · Oct 26, 2018

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 · Oct 26, 2018

the below statement retreives same allocation ID each time it tuns

Code:

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

Gasman · Oct 26, 2018

aman said:
the below statement retreives same allocation ID each time it tuns

Code:

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

Code:

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 · Oct 26, 2018

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

Code:

Me.lstAllocation.Column(6)

aman · Oct 26, 2018

got it working

. changed to below:

Code:

me.lstAllocation.column(6,i)

Gasman · Oct 26, 2018

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 · Oct 26, 2018

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 · Oct 26, 2018

@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 · Oct 26, 2018

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

misscrf · Nov 19, 2019

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!

Gasman said:
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

Code:

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

Loop through all the selected items in a listbox (1 Viewer)

aman

Registered User.

Gasman

Enthusiastic Amateur

Cronk

Registered User.

aman

Registered User.

aman

Registered User.

Gasman

Enthusiastic Amateur

aman

Registered User.

aman

Registered User.

Gasman

Enthusiastic Amateur

The_Doc_Man

Immoderate Moderator

Minty

AWF VIP

The_Doc_Man

Immoderate Moderator

misscrf

Registered User.

Similar threads

Users who are viewing this thread