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

How to Display the Copy Number in a report

mshmyob
Expert 100+
P: 903
I am using Access 2007 but any VBA code will probably work.

I need to display which copy number I have printed.

For example if the user prints 4 copies I need to display the following in the page footer of each copy respectively:
Copy 1
Copy 2
Copy 3
Copy 4

Any ideas would be appreciated.

If you can get it to show Copy 1 of 4, Copy 2 of 4, etc that would be even better.
Jan 7 '08 #1
Share this Question
Share on Google+
12 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
I haven't really thought about this but two things immediately pop to mind.

First one is that you will have to create a public variable to hold the total number of copies to be printed. A value which will have to be entered by the user when sending to print.

Secondly, you will need to look at creating some kind of loop in the on Print procedure of the report. Then somehow increment from one to the value stored in the public variable.
Jan 8 '08 #2

mshmyob
Expert 100+
P: 903
Thanks. I already ask for the number of copies so I have that as a variable in another form. So I will make it public and pass it along to the report and try and create a counter.

I don't know if the counter will clear each time a copy of the report is printed but I will give it a try and let you know.

I haven't really thought about this but two things immediately pop to mind.

First one is that you will have to create a public variable to hold the total number of copies to be printed. A value which will have to be entered by the user when sending to print.

Secondly, you will need to look at creating some kind of loop in the on Print procedure of the report. Then somehow increment from one to the value stored in the public variable.
Jan 8 '08 #3

mshmyob
Expert 100+
P: 903
Ok I have tried passing variables and it won't work. I think I figured out why and if anyone can figure out a better way I would appreciate it.

I open my report from a form but set the number of copies like so
Expand|Select|Wrap|Line Numbers
  1. Dim rpt As Report
  2.     Dim strDefaultPrinter  As String
  3.     ' get current default printer.
  4.     strDefaultPrinter = Application.Printer.DeviceName
  5.     ' switch to printer of your choice:
  6.     Set Application.Printer = Application.Printers(strDefaultPrinter)
  7.     ' open report but in hidden mode so you can apply parameters to it such as number of copies to print
  8.     DoCmd.OpenReport "PickSlip", acViewPreview, , , acHidden
  9.     ' set my parameters for the print job
  10.     Set rpt = Reports!PickSlip
  11.     rpt.Printer.Copies = cmbCopiesPick.Value
  12.       ' activate my print job by unhiding it
  13.     DoCmd.OpenReport "PickSlip", acViewNormal
  14.      DoCmd.Close acReport, "PickSlip", acSaveNo
  15.       Set Application.Printer = Nothing
  16.  
This code sets the number of copies for the printer to print.
I then open the report and it prints.

The problem is that the report looks at it as printing a single report and the printer controls the number of prints.

I guess I could change the code and have say 4 lines of docmd.openreport and each one would be a copy but that seems very ineficient.

Anyone know any other way other than having multiple docmd.openreport commands.





I haven't really thought about this but two things immediately pop to mind.

First one is that you will have to create a public variable to hold the total number of copies to be printed. A value which will have to be entered by the user when sending to print.

Secondly, you will need to look at creating some kind of loop in the on Print procedure of the report. Then somehow increment from one to the value stored in the public variable.
Jan 8 '08 #4

MMcCarthy
Expert Mod 10K+
P: 14,534
I'll see if any of the other experts are aware of anything.
Jan 8 '08 #5

Rabbit
Expert Mod 10K+
P: 12,366
What if you looped the print command while at the same time incrementing a print count variable? And then calling those controls from the report?

Expand|Select|Wrap|Line Numbers
  1. Dim CurrentCount As Integer, MaxCount As Integer
  2.  
  3. MaxCount = txtMaxCount
  4.  
  5. For CurrentCount = 1 To MaxCount
  6.    txtCurrentCount = CurrentCount
  7.    DoCmd.OpenReport "rpt_Name"
  8. Next CurrentCount
  9.  
Jan 8 '08 #6

Scott Price
Expert 100+
P: 1,384
This is just a brainīstorming suggestion since Iīm not at my main computer and canīt do any testing on the computer Iīm using to write this, but donīt forget the .Print method.

Again from the top of my head Iīm thinking of a function that would recieve the number of copies from a public variable, and increment them, then by directly printing to the page (which is the advantage of the .Print method, doing away with the need to tie your variables to report controls) you thereby have greater and easier control over what you are wanting to do.

Just ideas, good luck, and Iīll keep an eye on this as much as I can. Iīm still, unfortunately, not at a firm location yet, but will be getting back to normal in the next week.

Regards,
Scott
Jan 8 '08 #7

NeoPa
Expert Mod 15k+
P: 31,494
From my understanding of printing multiple copies of a report (using the .Copies property) it would be impossible to do what you ask as they are just that - copies.

To produce the result that you want you would need to take the approach suggested and code printing of the report n times (with one copy each time). This is slightly different as certain variables (like Print Date & Print Time would be different using this approach but exactly the same when using the .Copies approach.

To get a public variable to be accessed by an object like a report in the newer versions of Access (I don't use 2007 but I've noticed even 2003 is stricter in this than 2000 was and that was more so than 97) you will need to define a public function to return the value required. In the new versions it's probably worth designing a function with increased flexibility to handle passing various values as I anticipate it being required more with the newer strictures. I'm afraid I can't provide an example now as I'm only just starting to find some 2003 users on my databases at work. I expect I will have one in the not too distant future, but that won't help you now I'm afraid.
Jan 9 '08 #8

mshmyob
Expert 100+
P: 903
By the sounds of it I will need to scrap the code I have for the number of copies and use a counter loop and the openargs property to pass a variable to the report. I will re-code and let you guys know how it worked out.

It's too bad Access doesn't have the function built into the report for copies like they do for page counts.

From my understanding of printing multiple copies of a report (using the .Copies property) it would be impossible to do what you ask as they are just that - copies.

To produce the result that you want you would need to take the approach suggested and code printing of the report n times (with one copy each time). This is slightly different as certain variables (like Print Date & Print Time would be different using this approach but exactly the same when using the .Copies approach.

To get a public variable to be accessed by an object like a report in the newer versions of Access (I don't use 2007 but I've noticed even 2003 is stricter in this than 2000 was and that was more so than 97) you will need to define a public function to return the value required. In the new versions it's probably worth designing a function with increased flexibility to handle passing various values as I anticipate it being required more with the newer strictures. I'm afraid I can't provide an example now as I'm only just starting to find some 2003 users on my databases at work. I expect I will have one in the not too distant future, but that won't help you now I'm afraid.
Jan 9 '08 #9

NeoPa
Expert Mod 15k+
P: 31,494
You're showing a fine attitude here and I hope we can help you some more (or maybe even that you won't need more help) when you've adjusted your code.
However, I feel you're labouring under a misaprehension about copies. The property and the term.
  1. .Copies are a property of the print and not managed by Access at all (other than to pass the request on to the printer driver.
  2. A copy would NOT be a copy if it were not exactly the same. As soon as you introduce a copy counter into the printed output, it ceases to be a copy. Using the term loosely it may be used in general parlance, however the name "Copies" was chosen precisely to match the facility provided.
That's not at all to say that what you want to do can't be done, or even that you shouldn't refer to the results as copies. Just bear in mind that there will always be a level of ambiguity when terming them thus.
Jan 9 '08 #10

mshmyob
Expert 100+
P: 903
You are right about the definition of copy - If i add Copy 1 to the first copy and then Copy 2 to the 2nd etc. then they are not true copies.

My previous code was setting the printer copies like you say and therefore Access has no way to do what I want because it actually sends only one version of the report to the printer and the printer then just ejects how many copies its copy setting is set to.

I have redone my code to send individual docmd.openreport commands inside a loop and used the openargs parameter to send a number string representing the copy number. This works fine now.

I thank everyone for all their input, it put me on the right track. Sometimes you get so tied up in trying to solve something based on code that is already working that you just end up going in circles until someone gives you a knock upside the head.

You're showing a fine attitude here and I hope we can help you some more (or maybe even that you won't need more help) when you've adjusted your code.
However, I feel you're labouring under a misaprehension about copies. The property and the term.
  1. .Copies are a property of the print and not managed by Access at all (other than to pass the request on to the printer driver.
  2. A copy would NOT be a copy if it were not exactly the same. As soon as you introduce a copy counter into the printed output, it ceases to be a copy. Using the term loosely it may be used in general parlance, however the name "Copies" was chosen precisely to match the facility provided.
That's not at all to say that what you want to do can't be done, or even that you shouldn't refer to the results as copies. Just bear in mind that there will always be a level of ambiguity when terming them thus.
Jan 9 '08 #11

Expert 100+
P: 446
Hi

I have found that the easiest way to print things like Date Ranges or Filter Criteria on a report is to keep an underlying form open.Passing variable never worked and this save creating them anyway. The Control Source for the text box control on the report then just reference the controls on the form. These can be quite complicated e.g.
Expand|Select|Wrap|Line Numbers
  1. =[Forms]![dialogRecalls]![DatesBetween_Text] & " (Week " & [Forms]![dialogRecalls]![cmbFrom] & " to Week " & [Forms]![dialogRecalls]![cmbTo] & ")"
where here the underlying form is called dialogRecalls and things in square boxes are controls on my form.

You can set the form's Visible=False when you click the Print button and then close the form on the Report close event.
Expand|Select|Wrap|Line Numbers
  1. If IsLoaded("dialogRecalls") then DoCmd.Close accForm, "dialogRecalls"
(IsLoaded is a standard procedure in Access) So, if I had your problem I would set up a loop on my Print button to count the number of reports wanted and send separate print commands for each copy, updating a text box on my form with the current copy. That way I could easily do the 'Copy 1 of 4' as you requested.

The Control Sourcs for the text box in your footer would be something like;
Expand|Select|Wrap|Line Numbers
  1. ="Copy " & [Forms]![frmDialog]![CurrentCopy] & " of " & [Forms]![frmDialog]![LastCopy] 
where frmDialog is the form you are using to send the print commands and [CurrentCopy] and [LastCopy] are text boxes displaying the counts.I have not actually tested this and would be wary that the loop may have to wait for confirmation that the first report is complete before incrementing the copy number and sending the second print command.

But I would try it as a first step to see whether it is a problem.

Best of luck

S7
Jan 9 '08 #12

mshmyob
Expert 100+
P: 903
Thanks for the input Si.

It's basically what I have done. I always leave the form open to reference variables as you do. I created a loop and pass the copy number using the openargs parameter in the openreport method. I have a text box in the page footer and it references the openarg parameter passed to it when the report gets opened.

Hi

I have found that the easiest way to print things like Date Ranges or Filter Criteria on a report is to keep an underlying form open.Passing variable never worked and this save creating them anyway. The Control Source for the text box control on the report then just reference the controls on the form. These can be quite complicated e.g.
Expand|Select|Wrap|Line Numbers
  1. =[Forms]![dialogRecalls]![DatesBetween_Text] & " (Week " & [Forms]![dialogRecalls]![cmbFrom] & " to Week " & [Forms]![dialogRecalls]![cmbTo] & ")"
where here the underlying form is called dialogRecalls and things in square boxes are controls on my form.

You can set the form's Visible=False when you click the Print button and then close the form on the Report close event.
Expand|Select|Wrap|Line Numbers
  1. If IsLoaded("dialogRecalls") then DoCmd.Close accForm, "dialogRecalls"
(IsLoaded is a standard procedure in Access) So, if I had your problem I would set up a loop on my Print button to count the number of reports wanted and send separate print commands for each copy, updating a text box on my form with the current copy. That way I could easily do the 'Copy 1 of 4' as you requested.

The Control Sourcs for the text box in your footer would be something like;
Expand|Select|Wrap|Line Numbers
  1. ="Copy " & [Forms]![frmDialog]![CurrentCopy] & " of " & [Forms]![frmDialog]![LastCopy] 
where frmDialog is the form you are using to send the print commands and [CurrentCopy] and [LastCopy] are text boxes displaying the counts.I have not actually tested this and would be wary that the loop may have to wait for confirmation that the first report is complete before incrementing the copy number and sending the second print command.

But I would try it as a first step to see whether it is a problem.

Best of luck

S7
Jan 9 '08 #13

Post your reply

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