MultiSelect Listbox & Display Window w/Add/remove

[CptTom]

I want to display a MultiSelect Listbox (thousands of records) & a box showing what was selected with Add & Remove buttons moving the selected items to & from the selected list. I want to then use the selected list to build a filter. Trick - there are 3 possible MultiSelect Listboxes depending on whether to find the item(s) based on Manufacturer's Code, Our Code, or ISO Code.

I can do part of this through an additional listbox and funtion like:

Public Function BuildSQL(objType As Object, objForm As Form, strList As String) As String
Dim intListCounter As Integer
Dim intSelectedCounter As Integer
Dim intListItems As Integer
Dim intSelectedItems As Integer
Dim intFoundinList As Integer
Dim strTEMP As String
Dim varItem As Variant 'Items in List box
Dim intI As Integer 'Count of Items in List Box

intListItems = objType.ListCount - 1
intSelectedItems = objForm.[lst_Selected].ListCount - 1

For intListCounter = 0 To intListItems
'Debug.Print Me.lst_LIN.Selected(intListCounter)
If objType.Selected(intListCounter) = True Then
If IsNull(objType.RowSource) Then
strList = objType.Column(0, intSelectedCounter) & ";" '& Chr$(13) '";"
objForm.[lst_Selected].RowSource = strList
Else
intFoundinList = False
For intSelectedCounter = 0 To intSelectedItems
If objForm.[lst_Selected].Column(0, _
intSelectedCounter) = objType & ".Column(0, intListCounter)" Then
intFoundinList = True
End If
Next intSelectedCounter
If Not intFoundinList Then
strList = objForm.[lst_Selected].RowSource & _
objType.Column(0, intListCounter) & ";" '& Chr$(13)
objForm.[lst_Selected].RowSource = ""
objForm.[lst_Selected].RowSource = strList
End If
End If
End If
Next intListCounter

End Function

However, when I try to run another function to actually build the filter's code, the listbox does not appear to actually have anything in it. It displays the "Running Set Filter" and then nothing.


Public Function SetFilter(objForm As Form, ctl As Control, strFilterField As String) As String

Dim intLenCount As Integer
Dim intListCounter As Integer
Dim intSelectedCounter As Integer
Dim intListItems As Integer
Dim intSelectedItems As Integer
Dim intFoundinList As Integer
Dim strTEMP As String
Dim varItem As Variant 'Items in List box
Dim intI As Integer 'Count of Items in List Box
Dim strList As String

Debug.Print "Running Set Filter"
'strFilterField = "[" & strFilterField & "] = "

intSelectedItems = ctl.ListCount - 1
Debug.Print "# Selected =" & intSelectedItems + 1
For Each varItem In ctl.ItemsSelected
Debug.Print ctl.ItemData(varItem)

Next varItem

Debug.Print strList
End Function

Any Ideas?


larry

 

[bsupport]

I've done this also, but I had to insert the selected items into a new table to store them, then I created the filter using another SQL statement on the New table. I saw another solution in a Access book I have. If you have not found a solution after the New year, I'll send you the one from my book. Who takes 7 seconds to develop,
7 mins to document,
7 hours to test,
7 months to fix will always blame the clock. s-)

 

[CptTom]

What I came up with was to create the second listbox as a multiselect, move the selection from the left box to the right and then highlight the list in the second box. That way, they ARE selected. Sounds dumb & may be a better way, but it works. When you have to select from an original list of over 15,500 records, then the ones you selected for the 2d box isn't so bad.

Larry