Solved Build a Filtstr from ListView Multiselection (1 Viewer)

[rodski]

I have a form that has both an Access Listview and Treeview & have successfully coded it so that I can drag & drop a single item from the listview onto a treeview node. However, I am struggling with dropping multiple items from the listview onto the treeview node. In dissecting the VBA in the Listview1_Click event I found that it is successful in iterating through the selected items, but only grabs the ID from the last item selected. Here is the VBA:

For i = 1 To ListView1.listItems.Count
If ListView1.listItems(i).Selected Then
'MsgBox (i) 'use this to see the iteration(s)
If FiltStr = "" Then
FiltStr = "ID = '" & ListView1.SelectedItem.ListSubItems(7).Text & "'"
Else
FiltStr = FiltStr & " OR ID = '" & ListView1.SelectedItem.ListSubItems(7).Text & "'"
End If
MsgBox ("Filtstr = " & FiltStr)
End If
Next

So if only 1 item is selected, the FiltStr successfully completes as " ID = '123' ". If 3 items are selected, the Filtstr completes as " ID = '456' OR ID = '456' OR ID = '456' " which is the ID of the last item selected. It is successfully building the Filtstr but is only grabbing the last ID each time through. (I used the MsgBox to see what ID it was collecting each time through the iteration.)

I am hopeful someone can help me as I find that documentation on Listview in Access somewhat scarce. Thanks in advance for any help!

-Rod

 

[vba_php]

you can always delete this post if you want to, Rod. but you wanted help on documentation? try these:

ms access form listview vs treeview - Google Search

www.google.com

ms access form listview drag to treeview - Google Search

www.google.com

 

[rodski]

Thanks vba_php. I have already been through most of those searches but haven't been able to source how to add to the Filtstr on each iteration or why it isn't working (keeping in mind this is Access & not one of the other applications).

 

[June7]

I think you need a For Each, https://bytes.com/topic/access/answers/689087-listview-control-selected-items

 

[vba_php]

It was just a thought. Good luck with it rod

 

[rodski]

For anyone else trying to figure out the same issue, here is what works (my listview control is ListView1):

For i = 1 To ListView1.listItems.Count
If ListView1.listItems(i).Selected Then
'MsgBox ("Iteration: " & i) 'use this to see the iteration(s)
If FiltStr = "" Then
FiltStr = "ID = '" & ListView1.listItems.item(i).ListSubItems(7).Text & "'"
Else
FiltStr = FiltStr & " OR ID = '" & ListView1.listItems.item(i).ListSubItems(7).Text & "'"
End If
'MsgBox ("Filtstr = " & FiltStr) 'use this to check on the Filtstr
End If
Next

When I selected 3 different items, my Filtstrm resulted in ID = 'ABC' OR ID = 'DEF' or ID = 'GHI', so it worked exactly the way I wanted it to. These are the records that will be updated when I drop them on my Treeview node.

In my situation, the value of the 7th column is what I was trying to capture and the above code does exactly what I wanted. If the value you are looking for is in the 1st column, you would use:

ListView1.listItems.item(i).Text

To get the value from any other column (column = X):

ListView1.listItems.item(i).ListSubItems(X).Text

In my case, because it is a string that I'm after, I have to use the single & double quotes and concatenate. If the value is a number, you simply would use this:

Filtstr = " ID = " & ListView1.listItems.item(i).ListSubItems(X).Text

Hope this helps someone as it certainly took long enough for me to figure it out and get the syntax right! -Rod