Charlotte:
To give you maximum flexibility I'd suggest basing the report on the query
which returns all reps, then open the report from a dialogue form which
filters it on the basis of selections in a multiselect list box. Assuming
you have a table SalesReps with columns SalesRepID (the primary key),
FirstName and Lastname, create the dialogue form and add a list box setting
it up as follows:
For its RowSource property:
SELECT [SalesRepID], [FirstName] & " " & [LastName] FROM [SalesReps] ORDER BY
[LastName], [Firstname];
For other properties:
Name: lstSalesReps
BoundColumn: 1
ColumnCount: 2
ColumnWidths: 0cm;8cm (Access will automatically convert these to inches
if you are not using metric units)
MultiSelect: Simple or Extended as preferred.
Add a button to the form to open the report, called rptSales in this example,
with the following in its Click event procedure:
Dim varItem As Variant
Dim strSalesRepIDList As String
Dim strCriteria As String
Dim ctrl As Control
Set ctrl = Me.lstSalesReps
If ctrl.ItemsSelected.Count > 0 Then
For Each varItem In ctrl.ItemsSelected
strSalesRepIDList = strSalesRepIDList & "," & ctrl.ItemData
(varItem)
Next varItem
' remove leading comma
strSalesRepIDList = Mid(strSalesRepIDList, 2)
strCriteria = "[SalesRepID] In(" & strSalesRepIDList & ")"
DoCmd.OpenReport "rptSales", _
View:=acViewPreview, _
WhereCondition:=strCriteria
Else
MsgBox "No sales reps selected", vbInformation, "Warning"
End If
To open the report you'd open the dialogue form select one or more sales reps
in the list box and click the button. The report will then open filtered to
the selected reps. Note that the SalesRepID column must be in the report's
underlying RecordSource, though not necessarily shown in the report.
Ken Sheridan
Stafford, England
