Opening the report multiple times filtered to each department supervisor is
not too difficult; you'd loop through each supervisor in code and call the
OpenReport method for each with the WhereCondition argument filtering the
report to the current supervisor each time.
There are various ways you could loop through the supervisors, e.g. you
could you could step through the rows in a recordset, but a more flexible
method would be to have an unbound multi-select list box on a form and a
button to open the reports. This would enable you to select as few or as
many of the supervisors as you wish for each circulation. I'll confine the
code to simply opening the report in print preview for the moment as you
don't say what you are using to generate the PDF files – I have a custom
button on the report toolbar which simply prints the report currently
selected in print preview to an installed PDF printer driver, but you may
have a more automated method in mind.
The list box would be set up along these lines:
RowSource: SELECT EmployeeID, FirstName & " " & LastName FROM Employees
WHERE JobTitle = "Department Supervisor" ORDER BY LastName, FirstName;
BoundColum: 1
ColumnCount: 2
ColumnWidths: 0cm;8cm
MultiSelect: Extended
If your units of measurement are imperial rather than metric Access will
automatically convert the last one. The important thing is that the first
dimension is zero to hide the first column and that the second is at least as
wide as the combo box.
Setting the MultiSelect property to Extended allows you to select (or
deselect) a range of contiguous items in the list by Shift-Clicking or to
cumulatively select individual items by Ctrl-Clicking. If you wanted to
cumulatively select individually by simple clicking the set the property to
Simple.
The code for the button's Click event procedure would go like this:
Dim varItem As Variant
Dim ctrl As Control
Dim strCriteria As String
Set ctrl = Me.lstSupervisors
If ctrl.ItemsSelected.Count > 0 Then
For Each varItem In ctrl.ItemsSelected
strCriteria = "[EmployeeID] = " & ctrl.ItemData(varItem)
DoCmd.OpenReport "YourReport", _
View:=acViewPreview, _
WhereCondition:=strCriteria
Next varItem
End If
For this example I've assumed that department supervisors are identified by
the value of a JobTitle column in an Employees table of which the key is
EmployeeID. The report is thus filtered on the basis of the EmployeeID.
Your model may differ of course, but it shouldn't be difficult to make the
necessary amendments.
Ken Sheridan
Stafford, England
