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

Printing a different value in a text box on multiple copies of same report

alpnz
100+
P: 113
Hi,
I am sure someone has managed this one before.
I have a report, which I call from a button on a form, which invokes the printing of 4 copies of a report. I would like to have a box on the report which prints "Office Copy" on the first page, "P.O.B." on the second pages etc.

The number of copies is set in the code on the Forms button. I have tried the following in the "On Print " Event on the report.
Expand|Select|Wrap|Line Numbers
  1.  
  2. PrintCount = 1
  3. Me.cpi_own = "Office Copy"
  4. PrintCount = 2
  5. Me.cpi_own = "P.O.B."
  6.  
  7. etc etc
  8.  
  9.  
Me.cpi_own being the text box on the report. Am I any where near the right method of achieving this type of event.

Any help appreciated.

John S
Dec 5 '06 #1
Share this Question
Share on Google+
11 Replies


alpnz
100+
P: 113
Off course it would be possible to have 4 text boxes nested on top of one another, and Use Me.Visible settings, however there must be a more eloquent way to achieve this.
Dec 5 '06 #2

NeoPa
Expert Mod 15k+
P: 31,656
In the OnOpen event of the report you can do all sorts of things to affect how the report works.
Another problem here though is accessing the information that the caller knows (IE. the count number).
You can do this with a Public variable in a module (which should be visible to both caller and callee), or you can define a Function to handle setting and returning value(s).
Dec 5 '06 #3

alpnz
100+
P: 113
In the OnOpen event of the report you can do all sorts of things to affect how the report works.
Another problem here though is accessing the information that the caller knows (IE. the count number).
You can do this with a Public variable in a module (which should be visible to both caller and callee), or you can define a Function to handle setting and returning value(s).
This explains the post from "another" forum, suggesting you would need to set-up a table with the descriptors you want to use, and then refer to them in the OpenArgs. It then shows code, setting up just as you have suggested, a Public Variable in a module. This is where it all goes pear shaped for me, as I am not to sure by Module, does this mean setting up a module seperate to the Report, and the form that calls it. Or is it a module, for the specific control within the report. I will go get the suggested code.
JDS
Dec 5 '06 #4

alpnz
100+
P: 113
One suggested option is to setup a table with the Tag options.
E.g.
Table
TagID
Tag Description


Then

Expand|Select|Wrap|Line Numbers
  1. Sub sPrintMultipleReportCopies(strReportName As String, bytNumberCopies As Byte)
  2.  
  3. [color=green]'/ Example sPrintMultipuleReportCopies("MyReport",3)[/color]
  4.  
  5.  
  6.  
  7. Dim db As DAO.Database
  8.  
  9. Dim rec  As DAO.Recordset
  10.  
  11. Dim intCounter As Integer
  12.  
  13. Dim bytCounter As Byte
  14.  
  15.  
  16.  
  17. strSQL = "SELECT * FROM tblReprotNames”
  18.  
  19.  
  20.  
  21. Set db = CurrentDb()
  22.  
  23. Set rec = db.OpenRecordset(strSQL, dbOpenSnapshot)
  24.  
  25. rec.MoveFirst
  26.  
  27.  
  28.  
  29. For I = 1 to bytNumberCopies
  30.  
  31. DoCmd.OpenReport strReportName,,,rec.(“TagDescription”) [color=red]This line needs to be check for the OpenArgs argument[/color]
  32.  
  33. rec.MoveNext
  34.  
  35. Next I
  36.  
  37.  
  38.  
  39. rec.Close
  40.  
  41.  
  42.  
  43. End Sub
  44.  
  45.  
However I probably need this code explained to me Adrian.
:-)

By that I mean, is this the code in the control, or is it a Seperate Public Module.
Dumb Question??.

John S
Dec 5 '06 #5

NeoPa
Expert Mod 15k+
P: 31,656
No Problem.
It is a separate module from both the form AND the report.
In the Project Explorer within the VBA window (Alt-F11 from Access then Ctrl-R) select Insert / Module.
The code you posted should be in a separate public module at a guess. It doesn't have Method type procedures (as you'd see in a Report or Form module).
Dec 5 '06 #6

alpnz
100+
P: 113
No Problem.
It is a separate module from both the form AND the report.
In the Project Explorer within the VBA window (Alt-F11 from Access then Ctrl-R) select Insert / Module.
The code you posted should be in a separate public module at a guess. It doesn't have Method type procedures (as you'd see in a Report or Form module).
I have given this idea a go, but it is all a bit obtuse to me.
When I enter the code as per the previous messages, I get a compile error at the DAO.etc etc part, saying Object not defined or some such.

This is how I see the code works.

It defines the "rec" recordset from the table "tbl_cpi", OK we can still get there, using a DLookup of the [cpi_desc] in the "tbl_cpi".

We can then rec.MoveFirst and for I = 1 OpenReport "The report" using rec as the OpenArgs
rec.MoveNext

Next I

etc etc.
However at compile time, it fails at the rec = part saying improper use of expression.

2 questions
Why would the DAO not work, and am I way of track trying to define the variables.
John S
Dec 6 '06 #7

NeoPa
Expert Mod 15k+
P: 31,656
I can see why your
Expand|Select|Wrap|Line Numbers
  1. DoCmd.OpenReport strReportName,,,rec.(“TagDescription”)
line would fail (wrong double-quote chars) but no idea why the
Expand|Select|Wrap|Line Numbers
  1. Set rec = db.OpenRecordset(strSQL, dbOpenSnapshot)
should have any problems.

BTW your
Expand|Select|Wrap|Line Numbers
  1. strSQL = "SELECT * FROM tblReprotNames”
line also has wrong char at end. Maybe this is causing subsequent line to fail.
Try fixing known problems and trying again.

Can't see any problems with your variable definitions.
Dec 6 '06 #8

MSeda
Expert 100+
P: 159
you didn't say what code you used to invoke the printing in the on click event, but i tried using the following code to pass the label text via openargs

this is the code I used in the print button:

Private Sub Command4_Click()

DoCmd.Close acForm, "Quote Printer"

DoCmd.OpenReport "Quote1", acNormal, , , , "Office Copy"
DoCmd.OpenReport "Quote1", acNormal, , , , "POB"
'etc...for each page

End Sub

I entered this code in the on format event for the report section where i had placed a textbox named "PageLabel"

Private Sub GroupHeader0_Format(Cancel As Integer, FormatCount As Integer)

Me.PageLabel = Me.OpenArgs

End Sub

I hope this can help.
Dec 6 '06 #9

alpnz
100+
P: 113
you didn't say what code you used to invoke the printing in the on click event, but i tried using the following code to pass the label text via openargs

this is the code I used in the print button:

Private Sub Command4_Click()

DoCmd.Close acForm, "Quote Printer"

DoCmd.OpenReport "Quote1", acNormal, , , , "Office Copy"
DoCmd.OpenReport "Quote1", acNormal, , , , "POB"
'etc...for each page

End Sub

I entered this code in the on format event for the report section where i had placed a textbox named "PageLabel"

Private Sub GroupHeader0_Format(Cancel As Integer, FormatCount As Integer)

Me.PageLabel = Me.OpenArgs

End Sub

I hope this can help.
Many thanks for your reply. This is similar to how I started out trying to achieve this, and I may try again.
Dec 6 '06 #10

NeoPa
Expert Mod 15k+
P: 31,656
With your better understanding, you may well manage it this time John.
If not - we're here to help.
Dec 6 '06 #11

alpnz
100+
P: 113
MSeda takes the oscar at the moment.
I have viewed a couple of global options, however they do not achieve much more than MSeda's offer.
When I get time I will cut and paste some of the module attempts.

Many thanks all.
Dec 7 '06 #12

Post your reply

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