Filter List Box from other list box selections

nosferatu26

Registered User.
Local time
Yesterday, 23:50
Joined
Jul 13, 2015
Messages
57
iterating through selections in multiselect list box

Hi, I have a form with 2 list boxes, part number and modification. There is a subform containing another list box that is supposed to show the part information (bpn,vendor,status,etc.) that corresponds to the selected part number/modification in the parent form list boxes.

the part info list box has multiselect enabled and what i want to is be able to select multiple line items and press a button which then sets all of the selected line items status to "Request Removal". This is my code for the button:

Private Sub removeButton_Click()
Dim varItem As Variant
With Me.acbModList
For Each varItem In .ItemsSelected
MsgBox (Me.Status.Value & Me.[Part Number].Value)
Me.Status = 6
Next
End With
End Sub


the msgbox was for debugging purposes. Here's my issue; the for each actually does iterate through each selected item but the value for the line item doesnt change along with it. For example, when I selected 3 items, the msgbox will pop up 3 times but each time will have the same information (first item in the table) even when that item isnt selected.
My next issue is that Im recieving an error message with "Me.Status = 6" stating "You cant assign a value to this object". 6 refers to the id of the status i want to set it to. I am relativily new with access and VBA so any help to resolve this issue will be tremendously appreciated. If I have left out any information please let me know.
 
Last edited:
Update the listbox recordset (table or updateable query), not the listbox.
 
When you use a for...each...next block, you have a variable that changes for each loop. You need to use that variable, shown in red . . .
Code:
Private Sub removeButton_Click()
   Dim varItem As Variant
   With Me.acbModList
      For Each [COLOR="Red"]varItem[/COLOR] In .ItemsSelected
         MsgBox [COLOR="Red"]varItem[/COLOR]
      Next
   End With
End Sub
. . . inside the loop. Note that varItem, in this case, will contain the index of the row in the list that is selected. Then, you might need the .Column property to get data out of the list using that index as the optional Row parameter.
Code:
      For Each rowIndex In Me.List0.ItemsSelected
[COLOR="Green"]         'show the data in the 4th column of List0 at row rowIndex[/COLOR]
         MsgBox Me.List0.Column(3, rowIndex)
      Next
hth
 
Thanks for the replies I was able to solve the issue thanks to the itemdata function. If anyone is curious this is what I did:


Private Sub removeButton_Click()
Dim varItem As Variant
Dim strSQL As String
With Me.acbModList
For Each varItem In .ItemsSelected
strSQL = "Update tbl_ACB_Parts " & _
"SET Status = 6 " & _
"WHERE [ACB Part ID] = " & acbModList.ItemData(varItem) & ";"
CurrentDb.Execute (strSQL)
Next
End With

Me.acbModList.Requery
Me.Refresh
End Sub


(:
 
Nice job. Thanks for posting your solution too! :)
 

Users who are viewing this thread

Back
Top Bottom