Need help on creating macro to print reports sequentially

[JM420A]

I have 3 reports that I need to print sequentially, situation is this

R- Report
P-Person

R1 has 3 pages, one for each P
R2 " "
R3 " "

I would like to be able to print R1P1,R2P1,R3P1. .., then do the same thing for each subsequent person.

Any guidance or direction is greatly appreciated. I tried to build a macro in to the detail section of the report to run another macro, but it failed.

thanks in advance
jm

[Stewart Ross]

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

[JM420A]

Stewart,
Option 2 worked. I tried doing that before I asked and it didn't work. Must be one of those "Take the car to the mechanic so the noise stops issues"

I'll probably post more

You answered my question, thanks

jm420a