Multi-Select Listbox

D

DS

I want to run a report using a multi-select listbox. The selected items
would be the only ones that would come up on the report. Also it would be
grouped on these selected item! Any direction is appreited.
Thanks
DS
 
K

Klatuu

Here is a function I use for exactly that purpose.

Private Function BuildWhereCondition(strControl As String) As String
'Set up the WhereCondition Argument for the reports
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

The string returned by this function can be used in the OpenReport method's
Where argument to filter the report.
 
D

DS

OK This looks great, simpler than what I've been finding...So I would put
this function in a module and call it from the open arg of the report as
such?

DoCmd.OpenReport,,,BuildWhereCondition = strWhere

Thanks
DS
 
Top