pass multi selected list box to query

[inungh]

I would like to pass multi selected in the list box to my query for
parameter.

How do I specify which row is passing in the loop?

Your help is great appreciated,

 

[Dale Fye]

There are a couple of ways to address this.

1. Create your query dynamically at runtime, looping through the lists
SelectedItems collection to build an IN( ) clause for your query. The
problem with this method is that IN ( ) clauses tend to run slower than my
preferred method.

The way you do this is similar to:

Dim varItem as Variant
Dim strSQL as string
Dim strInClause as string

For each varItem in me.lst.ItemsSelected
'If the column you want is numeric drop both
'of the references to chr$(34)
strInClause = strInClause & "," & chr$(34) _
& me.lst.column(0, varItem) _
& chr$(34)
Next

'drop the leading ", "
strInClause = Mid(strInClause, 2)

strSQL = "SELECT * FROM [yourTable]"
if len(strInClause) > 0 then
strSQL = strSQL & " WHERE IN(" & strInClause & ")"
endif

currentdb.querydefs("SomeQuery").sql = strsql

2. The other method, that I use whenever I can is to:
a. Add a field (IncludeThis, Yes/No) to the table that the list is based on
b. When you load the form, execute an update query that sets the
"IncludeThis" column to false
c. In the list boxes Click event, execute an update query that changes the
value of the IncludeThis column for the specific record selected.
d. Write the query something like:

SELECT *
FROM yourTable
INNER JOIN listTable
ON yourTable.CommonField = listTable.CommonField
WHERE listTable.IncludeThis = True

If you decide to implement method #2, and get stuck, post back and I'll dig
up an example for you.
--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

 

[Klatuu]

Here is a function I use to create a filtering string based on a multi select
list box.

Function BuildWhereCondition(strControl As String) As String
Dim varItem As Variant
Dim strWhere As String
Dim ctl As Control

Set ctl = Me.Controls(strControl)

Select Case ctl.ItemsSelected.Count
Case 0 'Include All
strWhere = ""
Case 1 'Only One Selected
strWhere = "= '" & _
ctl.ItemData(ctl.ItemsSelected(0)) & "'"
Case Else 'Multiple Selection
strWhere = " IN ("

With ctl
For Each varItem In .ItemsSelected
strWhere = strWhere & "'" & .ItemData(varItem) & "', "
Next varItem
End With
strWhere = Left(strWhere, Len(strWhere) - 2) & ")"
End Select

BuildWhereCondition = strWhere

End Function

It can be used for filtering forms, reports, or for including in the
filterting of a query.