multi select list box in a form as a query parameter


S

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
 
Ad

Advertisements

T

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.


Soboths said:
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
 
P

petra schmidt

Soboths said:
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
 
Ad

Advertisements

S

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

Tokyo Alex said:
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.


Soboths said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top