List Box again

In my aplication I'm using Microsoft ActiveX Data Object Libraray. I have populate with code a List Box (the user can select one or more than one item (MultiSelect 1- simple). Here the code that I use to populate the list box
Private Sub Form_load()
With mrsPartCode
Do Until .EOF
LstPartCode.AddItem !part_code & vbTab & !part_desc
.MoveNext
Loop
.MoveFirst
End With
End Sub
I Want to find a way to filter other recordset based on the part_code item from the list box. I think the SQL should be called after the array is filled after completing the list. Obviously the other recordset have the same field part_code that is showing in the list box.
That means the user can select two, five, six, …. , or more items from the list.
How can I filter a Recordset with the multiple selection in the list box?
Thank you.

At the end, strSQL will contain a select statement with a IN clause. If you are dealing with string data, you must put single quotes round the values.

private Sub Command1_Click()

Dim strIn as string
Dim strCode as string

for t = 0 to List1.ListCount - 1
If List1.Selected(t) = true then
strCode = Left(List1.List(t), InStr(List1.List(t), vbTab) - 1)
strIn = strIn & strCode & ","
End If
next t

strsql = "SELECT * FROM OtherPartTable WHERE part_code in (" & Left(strIn, len(strIn) - 1) & ")"

End Sub




Tom Cannaerts
[email protected]

Programming today is a race between software engineers striving to build bigger and better idiot-proof programs, and the universe trying to produce bigger and better idiots. So far, the universe is winning -- Rich Cook