Select particular managers for a report

[Guest]

Working on a database which has a table that stores managers. I'm trying to
create a form that will allow the user to select 1, a few or all managers to
display in a report. Selecting only one manager or all managers, I know how
to do but selecting a few managers, ie. 2 of the 5 managers, I'm not sure
what approach to take. I've been using a combo box so far for allowing the
user to select all or 1 manager only. What's the easiest or best approach to
accomodate selecting a few managers?

 

[Steve]

Since you only have a small number of managers, a multiselect listbox might
work best. You can make multiple selections and use the selections as
criteria in your query that the report is based on.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)

 

[Guest]

If I use that listbox in the criteria of my query, does the query
automatically know what items are selected on the list box?

 

[Guest]

The query does not know. You have to modify the query programmatically to
get it included. Here is a function that will create a string you can use to
include the selections in the query:

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

You can use this to filter the report by one or more managers:

strRptFilter = "[MANAGER] = " & BuildWhereCondition(Me.lstMgrs.Name)
DoCmd.OpenReport "SomeReport", , , strRptFilter