multi select list box in a form as a query parameter

[Soboths]

I have been able to build a query that is filtered by a selection in a
Listbox in a form, but now i want to use a multi select list box so that two
or more items can be included in the query. I constructed the query pointing
to the List box which is not working. All I get is an empty dataset.

Form Name: Receipts_Selection_Form
List Box Name: List42
List Source: TBL_SLocation
Multi Select: Simple

With below code
----------

Private Sub List42_AfterUpdate()
Dim frm As Form
Dim ctl As Control
Dim varItem As Variant
Dim strSQL As String
Dim strWhere As String

Set frm = Forms!Receipts_Selection_Form
Set ctl = Me![List42]
strSQL = "Select * from TBL_SLocation"

For Each varItem In ctl.ItemsSelected
strWhere = strWhere & ctl.ItemData(varItem) & ", "
Next varItem
End Sub

 

[Tokyo Alex]

Hi,

Instead of

For Each varItem In ctl.ItemsSelected
strWhere = strWhere & ctl.ItemData(varItem) & ", "
Next varItem

you'll need something like:
'!Untested Code Fragment!***********
For Each varItem In ctl.ItemsSelected
strWhere = strWhere & ctl.ItemData(varItem) & ","
Next

lngLen = Len(strWhere)
If lngLen <> 0 Then 'There was at least one item selected
strWhere = "WHERE [FieldYouWantToFilterOn] IN (" & _
Left(strWhere, lngLen-1) & ")"
Else 'No items selected - no filter
'Do Nothing
End If

strSQL = strSQL & strWhere
'!End Code Fragment!**************

Note this assumes your listbox is returning numeric values. If it returns
text you'll have to concatenate in some quotes to delimit them.

Let me know how this works.

Cheers,
Alex.

I have been able to build a query that is filtered by a selection in a
Listbox in a form, but now i want to use a multi select list box so that two
or more items can be included in the query. I constructed the query pointing
to the List box which is not working. All I get is an empty dataset.

Form Name: Receipts_Selection_Form
List Box Name: List42
List Source: TBL_SLocation
Multi Select: Simple

With below code
----------

Private Sub List42_AfterUpdate()
Dim frm As Form
Dim ctl As Control
Dim varItem As Variant
Dim strSQL As String
Dim strWhere As String

Set frm = Forms!Receipts_Selection_Form
Set ctl = Me![List42]
strSQL = "Select * from TBL_SLocation"

For Each varItem In ctl.ItemsSelected
strWhere = strWhere & ctl.ItemData(varItem) & ", "
Next varItem
End Sub

 

[petra schmidt]

I have been able to build a query that is filtered by a selection in a
Listbox in a form, but now i want to use a multi select list box so that
two
or more items can be included in the query. I constructed the query
pointing
to the List box which is not working. All I get is an empty dataset.

Form Name: Receipts_Selection_Form
List Box Name: List42
List Source: TBL_SLocation
Multi Select: Simple

With below code
----------

Private Sub List42_AfterUpdate()
Dim frm As Form
Dim ctl As Control
Dim varItem As Variant
Dim strSQL As String
Dim strWhere As String

Set frm = Forms!Receipts_Selection_Form
Set ctl = Me![List42]
strSQL = "Select * from TBL_SLocation"

For Each varItem In ctl.ItemsSelected
strWhere = strWhere & ctl.ItemData(varItem) & ", "
Next varItem
End Sub

 

[Soboths]

Thank you for your help.
My list box is pulling names of the location which the returns should be
text. I will try it again with your suggestion. Thanks again
Soboths

Hi,

Instead of

For Each varItem In ctl.ItemsSelected
strWhere = strWhere & ctl.ItemData(varItem) & ", "
Next varItem

you'll need something like:
'!Untested Code Fragment!***********
For Each varItem In ctl.ItemsSelected
strWhere = strWhere & ctl.ItemData(varItem) & ","
Next

lngLen = Len(strWhere)
If lngLen <> 0 Then 'There was at least one item selected
strWhere = "WHERE [FieldYouWantToFilterOn] IN (" & _
Left(strWhere, lngLen-1) & ")"
Else 'No items selected - no filter
'Do Nothing
End If

strSQL = strSQL & strWhere
'!End Code Fragment!**************

Note this assumes your listbox is returning numeric values. If it returns
text you'll have to concatenate in some quotes to delimit them.

Let me know how this works.

Cheers,
Alex.

I have been able to build a query that is filtered by a selection in a
Listbox in a form, but now i want to use a multi select list box so that two
or more items can be included in the query. I constructed the query pointing
to the List box which is not working. All I get is an empty dataset.

Form Name: Receipts_Selection_Form
List Box Name: List42
List Source: TBL_SLocation
Multi Select: Simple

With below code
----------

Private Sub List42_AfterUpdate()
Dim frm As Form
Dim ctl As Control
Dim varItem As Variant
Dim strSQL As String
Dim strWhere As String

Set frm = Forms!Receipts_Selection_Form
Set ctl = Me![List42]
strSQL = "Select * from TBL_SLocation"

For Each varItem In ctl.ItemsSelected
strWhere = strWhere & ctl.ItemData(varItem) & ", "
Next varItem
End Sub