OK. I solved it in this way: I wrote a module with two functions, first one
creates a string to be included inside IN expression of WHERE clause, and
second one to synchonize any "child" listbox in correspondence to "parent"
listbox.
Option Compare Database
Function MultiselectListValues(ListFullName As Control) As String
'This function returns a string, as comma-separated list of values,
'based on selected values in a multi-select list box.
'This string can be used inside IN expression in WHERE clause of a query
Dim strWhere As String, varItem As Variant
'Request to edit items selected in the list box
'If no items selected, then nothing to do
If ListFullName.ItemsSelected.Count = 0 Then Exit Function
'Loop through the items selected collection
For Each varItem In ListFullName.ItemsSelected
'Grab the column for each selected item
strWhere = strWhere & "'" & ListFullName.Column(0, varItem) & "'" & ","
Next varItem
'Throw away the extra comma on the "IN" string
strWhere = Left$(strWhere, Len(strWhere) - 1)
'Finish creation of "IN" string
MultiselectListValues = strWhere
End Function
Function SynchronizeListBox(ParentListFullName As Control, ChildListFullName
As Control, SelectExpression As String, LinkField As String)
'This function synchronize recordset in child list box, based on selectde
values in parent list box.
'Parent list box has a query or SQL statement as its rowsource, while
recordset of child list box is created programmatically.
Dim db As Object
Dim qdf As Object
Dim qdfSQL As String
Dim rs As Object
Dim strWho As String
Dim strWhere As String
Dim strSQL As String
DoCmd.Hourglass True
Set db = CurrentDb
strWho = LinkField
strWhere = MultiselectListValues(ParentListFullName)
Set qdf = db.CreateQueryDef("")
If strWhere <> "" Then
strSQL = SelectExpression & " WHERE (((" & strWho & ")IN(" & strWhere &
")));"
Else
strSQL = "select plants.plant from plants WHERE " & strWho & "=""""" &
";"
End If
qdf.SQL = strSQL
qdf.returnsrecords = True
Set rs = qdf.openrecordset()
Set ChildListFullName.Recordset = rs.clone
rs.Close
qdf.Close
DoCmd.Hourglass False
End Function
Ron2005 said:
I was research similar subject yesterday and found this:
Use Multi-Select List boxes as query parameters
(Q) I have a MultiSelect listbox control on my form. I want to pass the
selected items to a query as a parameter. How do I do this?
(A) Unlike simple listbox controls which can be referenced as a
parameter by a query, MultiSelect listboxes cannot be used directly as
a parameter. This is because calling the listbox
(Forms!frmMyForm!lbMultiSelListBox) from anywhere will not
automatically concatenate all the selected items. You need to build the
criteria yourself.
Note: You can still use a parameterized query provided you pass the
entire Where clause to it via code as a parameter. (eg. Have the query
reference a hidden control to which you manually assign the complete
WHERE clause using the following logic.)
For example,
'******************** Code Start ************************
Dim frm As Form, ctl As Control
Dim varItem As Variant
Dim strSQL As String
Set frm = Form!frmMyForm
Set ctl = frm!lbMultiSelectListbox
strSQL = "Select * from Employees where [EmpID]="
'Assuming long [EmpID] is the bound field in lb
'enumerate selected items and
'concatenate to strSQL
For Each varItem In ctl.ItemsSelected
strSQL = strSQL & ctl.ItemData(varItem) & " OR [EmpID]="
Next varItem
'Trim the end of strSQL
strSQL=left$(strSQL,len(strSQL)-12))
'******************** Code end ************************
Hope it gives you some ideas.
Ron
