Using ListBox values as query parameters

[Lillian]

I would like to use ListBox values (1 or more) in a query
and need help with the code. I need to be able to use the
items in the list to be the filter of a query.

So far I have this much...

Public Sub RowsSelected()
Dim ctlList As Control, varItem As Variant

Set ctlList = Forms!frmReports!filterbox
For Each varItem In ctlList.ItemsSelected
Debug.Print ctlList.ItemData(varItem)
Next varItem
End Sub

HELP! Please.

Lillian

 

[John Viescas]

Lillian-

Well, you have a good start. Let's assume that the List Box displays
product names and has the ProductID in a hidden first column that's the
bound column. You need to build a filter and then use that to open your
form or report.

Public Sub RowsSelected()
Dim ctlList As Control, varItem As Variant
Dim strWhere As String

Set ctlList = Forms!frmReports!filterbox
For Each varItem In ctlList.ItemsSelected
' Add the item to the list to be used in an IN clause
strWhere = strWhere & ctlList.ItemData(varItem) & ","
Next varItem
' Do nothing if nothing selected
If Len(strWhere) = 0 Then
MsgBox "You didn't select any products."
Exit Sub
End If
' Add the IN keyword and get rid of the extra comma at the end
strWhere = "ProductID IN (" & Left(strWhere, Len(strWhere) - 1) & ")"
' Open the products form filtered
DoCmd.OpenForm "Products", WhereCondition:=strWhere
End Sub


--
John Viescas, author
"Microsoft Office Access 2003 Inside Out" (coming soon)
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)

 

[Lillian]

Thanks, I will give it a try

-----Original Message-----
Lillian-

Well, you have a good start. Let's assume that the List Box displays
product names and has the ProductID in a hidden first column that's the
bound column. You need to build a filter and then use that to open your
form or report.

Public Sub RowsSelected()
Dim ctlList As Control, varItem As Variant
Dim strWhere As String

Set ctlList = Forms!frmReports!filterbox
For Each varItem In ctlList.ItemsSelected
' Add the item to the list to be used in an IN clause
strWhere = strWhere & ctlList.ItemData(varItem) & ","
Next varItem
' Do nothing if nothing selected
If Len(strWhere) = 0 Then
MsgBox "You didn't select any products."
Exit Sub
End If
' Add the IN keyword and get rid of the extra comma at the end
strWhere = "ProductID IN (" & Left(strWhere, Len (strWhere) - 1) & ")"
' Open the products form filtered
DoCmd.OpenForm "Products", WhereCondition:=strWhere
End Sub


--
John Viescas, author
"Microsoft Office Access 2003 Inside Out" (coming soon)
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)



.