By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,204 Members | 1,212 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,204 IT Pros & Developers. It's quick & easy.

Need help on creating macro to print reports sequentially

P: 8
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
Jul 23 '08 #1
Share this Question
Share on Google+
2 Replies


Expert Mod 2.5K+
P: 2,545
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.

Expand|Select|Wrap|Line Numbers
  1. Dim RS as DAO.Recordset
  2. Dim userid as String
  3. set RS = CurrentDb.OpenRecordset("name of your username query")
  4. do while not RS.EOF
  5.   userID = RS![your user ID field name]
  6.   DoCmd.OpenReport "R1", acViewNormal, "[your user ID field name] = " & UserID
  7.   DoCmd.OpenReport "R2", acViewNormal, "[your user ID field name] = " & UserID
  8.   DoCmd.OpenReport "R3", acViewNormal, "[your user ID field name] = " & UserID
  9.   RS.Movenext
  10. Loop
  11. 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
Jul 24 '08 #2

P: 8
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
Jul 24 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.