Hi, and welcome to Bytes!
It is possible but not straightforward to do as you ask. You will need to use VBA code to do so outside of the reports; this is not a task which can be accomplished from within.
You have three independent reports, and you wish to output the pages relevant to each user in turn. You will need to devise a query based on your report's underlying query which extracts the user ID or name of the users involved. In VBA code called from the On Click event of your command button running the report you then need to loop for each user and print each report in turn, filtered for that user only.
A skeleton of the code using dummy field and query names is shown below.
- Dim RS as DAO.Recordset
-
Dim userid as String
-
set RS = CurrentDb.OpenRecordset("name of your username query")
-
do while not RS.EOF
-
userID = RS![your user ID field name]
-
DoCmd.OpenReport "R1", acViewNormal, "[your user ID field name] = " & UserID
-
DoCmd.OpenReport "R2", acViewNormal, "[your user ID field name] = " & UserID
-
DoCmd.OpenReport "R3", acViewNormal, "[your user ID field name] = " & UserID
-
RS.Movenext
-
Loop
-
RS.Close
This uses a Data Access Object (DAO) recordset. Check in the VB editor that you have a reference to the DAO object library set already - select Tools, References and ensure that Microsoft DAO 3.6 or later object library is ticked. If you do not the code will not compile.
There is one other alternative that is much simpler, but I doubt if it is suitable in your case. That is to include R2 and R3 at the bottom of R1's detail section as subreports, linked parent/child by user ID or name. I doubt that this will be suitable because the formatting of the reports is unlikely to match sufficiently when constrained from within another report, but there is no harm in trying it out to see if it works. It is much easier than the code-based solution, but less flexible.
-Stewart
8
