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

[alpnz]

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.

 
PrintCount = 1
Me.cpi_own = "Office Copy"
PrintCount = 2
Me.cpi_own = "P.O.B."
 
etc etc
 
 

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

[alpnz]

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.

[NeoPa]

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).

[alpnz]

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

[alpnz]

One suggested option is to setup a table with the Tag options.
E.g.
Table
TagID
Tag Description


Then

Sub sPrintMultipleReportCopies(strReportName As String, bytNumberCopies As Byte)
 
[color=green]'/ Example sPrintMultipuleReportCopies("MyReport",3)[/color]
 
 
 
Dim db As DAO.Database
 
Dim rec  As DAO.Recordset
 
Dim intCounter As Integer
 
Dim bytCounter As Byte
 
 
 
strSQL = "SELECT * FROM tblReprotNames”
 
 
 
Set db = CurrentDb()
 
Set rec = db.OpenRecordset(strSQL, dbOpenSnapshot)
 
rec.MoveFirst
 
 
 
For I = 1 to bytNumberCopies
 
DoCmd.OpenReport strReportName,,,rec.(“TagDescription”) [color=red]This line needs to be check for the OpenArgs argument[/color]
 
rec.MoveNext
 
Next I
 
 
 
rec.Close
 
 
 
End Sub
 
 

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

[NeoPa]

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).

[alpnz]

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

[NeoPa]

I can see why your

DoCmd.OpenReport strReportName,,,rec.(“TagDescription”)

line would fail (wrong double-quote chars) but no idea why the

Set rec = db.OpenRecordset(strSQL, dbOpenSnapshot)

should have any problems.

BTW your

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.

[MSeda]

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.OpenRepor t "Quote1", acNormal, , , , "Office Copy"
DoCmd.OpenRepor t "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_Fo rmat(Cancel As Integer, FormatCount As Integer)

Me.PageLabel = Me.OpenArgs

End Sub

I hope this can help.

[alpnz]

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.OpenRepor t "Quote1", acNormal, , , , "Office Copy"
DoCmd.OpenRepor t "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_Fo rmat(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.