How to use a listbox return as a criteria for a query?

[alibongo]

Hi,

I want to have a form which has a list box of item (lstSource) and an empty list box(lstDestination) which I can pass items to by pressing an add button and can also delete them via a delete button. I then want to run a query via an OK button which returns a number of rows from a table dependent on what I have selected from the lstSource box.

My row source for the lstSource listbox is a query which returns SkillNames (group by as many skills belong to many employees). My code to move from lstSource to lstDestination is as follows:

Public Sub CopySelected(ByRef frm As Form)

Dim ctlSource As Control
Dim ctlDest As Control
Dim strItems As String
Dim intCurrentRow As Integer

Set ctlSource = frm!lstSource
Set ctlDest = frm!lstDestination

For intCurrentRow = 0 To ctlSource.ListCount - 1
If ctlSource.Selected(intCurrentRow) Then
strItems = strItems & ctlSource.Column(0, _
intCurrentRow) & ";"
End If
Next intCurrentRow

' Reset destination control's RowSource property.
ctlDest.RowSource = ""
ctlDest.RowSource = strItems

Set ctlSource = Nothing
Set ctlDest = Nothing

End Sub

My Add button is the following

Private Sub AddButton_Click()
CopySelected Me
End Sub

This works and I can do Multiselect (using the expanded option in properties).
What I cant do is use the results in that list box as cirteria to return the employees with those skills selected in another query.

I hope you understand this, I have probably not explained it very well.
If you need further clarification please let me know.

Thanks for all help,
Alistair

 

[NVSbe]

What I'd do is make a recordset with a command cmd behind it

dim rcd as recordset
dim cmd as string
Dim ctlDest As Control

cmd = "select * from tblSkill where emp_name in ("
Set ctlDest = frm!lstDestination

For intCurrentRow = 0 To ctlDest.ListCount - 1
If ctlDest.Selected(intCurrentRow) Then
cmd = cmd & "'" ctlSource.Column(0, _
intCurrentRow) & "',"
End If
Next intCurrentRow

cmd = left(cmd, len(cmd) -1)
cmd = cmd & &quot"

set rcd = currentdb.openrecordset(cmd)

In rcd, you should now get the right data (if I understand your database right)

The cmd string would look like
select * from tblSkill where emp_name in ('NAME1', 'NAME2', 'NAME3', ... 'NAMEn') --------------------------------------
It's not the monsters under your bed, it is the men next door.
That make you fear, make you cry. Make you cry for the Child.
All the wars are fought amongst those lonely men. Unharmed, unscarred.