Printing copy numbers on a report

[Sans]

Hi to everyone,

It is my first thread and I apologize if you already had a discussion on it. My question is regarding the possibility of printing multiple copies of reports so that every copy is marked "copy 1", "copy 2" ....

Thanks in advance,

 

[vbaInet]

Place a text box in the Page Footer section of your report. Then put this inside the text box:

="Page " & [Page] & " of " & [Pages]

And welcome!

 

[SOS]

Place a text box in the Page Footer section of your report. Then put this inside the text box:

="Page " & [Page] & " of " & [Pages]

That will number the pages, not number the copies (say they want to print 2 copies of the report - they want Copy 1 and then Copy 2 on the appropriate one). (I'd like to know how to accomplish that one too)

 

[Sans]

Thanks vbaInet,

I've tried it but it is not what I want. I need, for example 10 copies of the same report and on each copy a different number.
I would like to use them as labels for pallets. Each pallet has the same information (customer name, product name, etc.), only the pallet number is different.

 

[vbaInet]

Oh, cheers SOS lol.

This might be a difficult one, but I have an idea. Let me work it out.

 

[vbaInet]

I haven't tested this but it looks promising. For this to work you would need to do the following:

1. Create a label on your report
2. Create a button on your form. This will be used to call the function.
3. Create a module and put this in the module:

Public Function PrintCopies(reportName As String, noOfCopies As Long)
    Dim i As Integer
    
    If IsNumeric(noOfCopies) = False Then
        If Abs(noOfCopies) = 0 Then
            MsgBox "Please enter a number between 1 and 25", vbInformation, "Invalid number"
        ElseIf Abs(noOfCopies) > 0 And Abs(noOfCopies) <= 20 Then
            For i = 1 To Abs(noOfCopies)
                DoCmd.OpenReport reportName, acViewDesign, , , acHidden
                Reports(reportName).[COLOR=Blue]LabelName[/COLOR].Caption = "Copy " & i
                Reports(reportName).[COLOR=Blue]LabelName[/COLOR].Visible = True
                DoCmd.OpenReport reportName, acViewPreview, , , acHidden
                DoCmd.PrintOut
            Next
            DoCmd.OpenReport reportName, acViewDesign, , , acHidden
            Reports(reportName).[COLOR=Blue]LabelName[/COLOR].Visible = False
            DoCmd.Save acReport, reportName
            DoCmd.Close acReport, reportName
            MsgBox "Finished printing!"
        Else
            MsgBox "Number too big", vbExclamation, "Too many copies"
        End If
    Else
        MsgBox "Not a number"
    End If
End Function

Change the highlighted text in the code accordingly.

This how you call the function (as an example):

PrintCopies "NameOfReport", InputBox("Please enter the number of copies to print", "Number of copies")

Let me know if that works

 

[vbaInet]

Following your message, I've made some amendments. The requirements above remain the same. Module:

Public Function PrintCopies(reportName As String, noOfCopies As String)
On Error GoTo Err_Routine
    Dim i As Integer
    
    If IsNumeric(noOfCopies) Then
        If Left(noOfCopies, 1) = "-" Or noOfCopies = "0" Then
            MsgBox "Please enter a number between 1 and 25", vbInformation, "Invalid number"
        ElseIf Val(noOfCopies) > 0 And Val(noOfCopies) <= 25 Then
            DoCmd.OpenReport reportName, acViewDesign, , , acHidden
            Reports(reportName).[COLOR=Blue]lblCopies[/COLOR].Visible = True
            For i = 1 To Val(noOfCopies)
                DoCmd.OpenReport reportName, acViewPreview
                Reports(reportName).[COLOR=Blue]lblCopies[/COLOR].Caption = "Copy " & i
                DoCmd.PrintOut
            Next
            DoCmd.OpenReport reportName, acViewDesign, , , acHidden
            Reports(reportName).[COLOR=Blue]lblCopies[/COLOR].Visible = False
            DoCmd.Close acReport, reportName, acSaveYes
            MsgBox "Finished printing!"
        Else
            MsgBox "Number too big", vbExclamation, "Too many copies"
        End If
    Else
        If noOfCopies <> "" Then
            MsgBox "Not a number", vbExclamation, "Invalid input"
        End If
    End If

Exit_Err_Routine:
    Exit Function
    
Err_Routine:
    If Err.Number = 2501 Then
        DoCmd.OpenReport reportName, acViewDesign, , , acHidden
        Reports(reportName).[COLOR=Blue]lblCopies[/COLOR].Visible = False
        DoCmd.Close acReport, reportName, acSaveYes
    Else
        MsgBox Err.Description
    End If
    Resume Exit_Err_Routine

End Function

To call the function:

PrintCopies "[COLOR=Blue]rptMyReport[/COLOR]", InputBox("Please enter the number of copies to print", "Number of copies")

Amend the words in blue accordingly. Replace 'rptMyReport' with the name of the report you would like to print copies of. Replace 'lblCopies' with the name of the label that displays "Copies 1"... etc

The number '25' is the maximum number of copies (as an example). You can amend that too to whatever number you wish.

As requested, I've attached a sample db (in 2003). I use 07' but I'm not sure what version you have

 

[stopher]

Here's a method....

1. Create a table containing a single field Called intCopy containing the number 1 to n where n is the maximum number of copies you are ever likely to require.
2. Make the source of your report a Cartesian product queries of your original source data and the new table.
3. Create a top level group in your report using the intCopy field and add the field to the group level
4. Add a criteria if desired to asked for the number of copies.

Chris

 

[gemma-the-husky]

i dont think you can do this by setting the number of copies in the page setup dialog

as long as you call the print routine multiple times, in CODE, then you can set the copy number as indicated in the ideas above

 

[Sans]

Thx vbaInet,

it's working. I have one more question. The copies came out randomly (copy 1, copy 10, copy 8,..). Is there a possibility to make them print starting with copy 1 and then copy 2, until the last copy (sorted by size)?

Thx Chris for your suggestion.

S.

 

[vbaInet]

You're welcome Sans.

For that to have happened, then I can only assume it's the way you implemented it. The loop starts from 1 down. What do you mean by sorted by size? The sorting behaviour would have to be done on your report because the number of copies isn't linked to the report's record source (as stopher advised)

 

[Sans]

Maybe I made some mistake. I'll try one more time.

Sorry for the "Sorted by size", English is not my mother language. I meant what you wrote, to start from 1 down.

 

[vbaInet]

It's not possible for it to print random copies because of the way the loop was defined. Send your db so I can see how you implemented it.

 

[Sans]

Now it's working.
The problem was that I used Office One Note 2007 to see the copies, but when I print them on the printer they came out from 1 down.
When I use your sample db in combination with One Note 2007 happen the same thing.
I can send you the db, but it is in Croatian.

 

[vbaInet]

Now it's working.
The problem was that I used Office One Note 2007 to see the copies, but when I print them on the printer they came out from 1 down.

There must be a setting in One Note that you need to tweak to make it work. The code does what it was programmed to do.

I can send you the db, but it is in Croatian.

In that case there won't be a need of sending it, unless I were to take some Croatian lessons. We're talking 12 months full time lessons

 

[Sans]

I can give you some free online lessons.
You never now, maybe you will come to Cro for vacation

 

[vbaInet]

Haha! Thanks Sans. This forum isn't ideal for those lessons. And I bet it would be difficult to write some of the Croatian letters whilst typing.

I was thinking of going there at some point.

 

[Sans]

You are right for the letters.

 

[vbaInet]

You have your own keyboards right?

 

[Sans]

We have some aditional lettes (ž,š,đ,č,ć), but I think that the number of buttons (on the standard keyboards) is the same as on yours.