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
"Allen Browne" <Al*********@SeeSig.Invalid> je napisao u poruci interesnoj
grupi:43***********************@per-qv1-newsreader-01.iinet.net.au...
A query cannot read the values in a multi-select list box.
It is possible to write a VBA function that loops through the
ItemsSelected collection, and returns True if the value in the record is
one of them (True becoming the result of a condition in the WHERE clause),
but it's messy.
What is the target for this query? If it is for a report, see:
Use a multi-select list box to filter a report
at:
http://allenbrowne.com/ser-50.html
If it is for a form, you could create the Filter string for the form in
exactly the same way as that article creates the WhereCondition for
OpenReport.
If it is for a Recordset, again you could create the entire SQL string
that way.
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Zlatko Matif" <zl***********@sb.t-com.hr> wrote in message
news:dq**********@ss405.t-com.hr...
How to reference selected values from a multi-select list box, as a
criteria in a query ?
Is it possible at all?
Regards,
Zlatko
