CoStar
asked on
How to print n copies of one label on same sheet
I'm trying to print to sheet labels and want to print multiple copies of the same label on successive labels on the same sheet. Unfortunately, when I set a number >1, the printer uses a different page for each copy.
Here is the code I use:
When MyVar >1, the report prints each label on a new page. How can I print to successive labels (without page change until labels on page are printed on).
Label report has only a Detail section, with Force New Page = None and New Row or Col = None
Any help would be appreciated.
Here is the code I use:
Private Sub BtnLabel_Click()
Dim stDocName As String
Dim MyInv As Long, MyVar As Variant
Dim NumCopies, i
On Error GoTo Err_Btn1_Click
stDocName = "ShipLabel3x5"
MyInv = Me.InvoiceID
MyVar = InputBox("How many labels to print?", "Label Counter", "1")
If Nz(MyVar, "") = "" Then 'cancel
Exit Sub
Else
NumCopies = MyVar
End If
'just this invoice
DoCmd.OpenReport stDocName, acViewPreview, , "InvoiceID=" & MyInv
For i = 1 To NumCopies
DoCmd.PrintOut
Next i
DoCmd.Close acReport, stDocName
Exit Sub
Exit_Btn1_Click:
Exit Sub
Err_Btn1_Click:
If Err.Number <> 2501 Then
MsgBox "Error #" & Str(Err.Number) & vbNewLine & "An error has occurred." & vbNewLine & "Description:" & vbNewLine & ErrDesc
Resume Exit_Btn1_Click
End If
When MyVar >1, the report prints each label on a new page. How can I print to successive labels (without page change until labels on page are printed on).
Label report has only a Detail section, with Force New Page = None and New Row or Col = None
Any help would be appreciated.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks, IrogSinta
my user will always print just ONE record (i.e. one address label), but often multiple copies are needed (one on each shipping parcel).
I give the option to use roll-fed labels (which overcome this problem), but user wants to stay with sheet labels - 2-up, 6 per page.
my user will always print just ONE record (i.e. one address label), but often multiple copies are needed (one on each shipping parcel).
I give the option to use roll-fed labels (which overcome this problem), but user wants to stay with sheet labels - 2-up, 6 per page.
ASKER
Peter:
will your utility print multiple labels of ONE record on ONE page?
will your utility print multiple labels of ONE record on ONE page?
Another option is to have a table set aside for the number of records you want to print.
For instance, create a table called tblLabelNum with one Number type field called LabelNum.
Then change the RecordSource for your report to include this table. For instance if your source is:
Then you would use this modified code:
I have to add though that Peter's utility is much more flexible.
For instance, create a table called tblLabelNum with one Number type field called LabelNum.
Then change the RecordSource for your report to include this table. For instance if your source is:
Select TableName.* From TableName;
Change it to:
SELECT TableName.* FROM TableName, tblLabelNum;
Then you would use this modified code:
Private Sub BtnLabel_Click()
Dim stDocName As String
Dim myInv As Long, myVar As Variant
Dim numCopies As Integer, i As Integer
Dim rst As Recordset
On Error GoTo Err_Btn1_Click
stDocName = "ShipLabel3x5"
myInv = Me.InvoiceID
myVar = InputBox("How many labels to print?", "Label Counter", "1")
If Nz(myVar, "") = "" Then 'cancel
Exit Sub
Else
numCopies = myVar
End If
CurrentDb.Execute "Delete * From tblLabelNum", dbFailOnError
Set rst = CurrentDb.OpenRecordset("tblLabelNum")
For i = 1 To numCopies
rst.AddNew
rst!LabelNum = i
rst.Update
Next i
rst.Close
Set rst = Nothing
DoCmd.OpenReport stDocName, acViewPreview, , "InvoiceID=" & myInv
Exit Sub
Exit_Btn1_Click:
Exit Sub
Err_Btn1_Click:
If Err.Number <> 2501 Then
MsgBox "Error #" & Str(Err.Number) & vbNewLine & "An error has occurred." & vbNewLine & "Description:" & vbNewLine & ErrDesc
Resume Exit_Btn1_Click
End If
End Sub
I have to add though that Peter's utility is much more flexible.
You can print out multiple lables simply from one record using the NextRecord, MoveLayout, and PrintSection propeties of the report.
You can even start on a specific label on a page.
Let me know if you want more details.
Jim.
You can even start on a specific label on a page.
Let me know if you want more details.
Jim.
"Peter:
will your utility print multiple labels of ONE record on ONE page?"
Yes.
will your utility print multiple labels of ONE record on ONE page?"
Yes.
Here's a link to a MSKB article showing the technique:
How to skip used mailing labels and print duplicates in Access 2002
http://support.microsoft.com/kb/299024
This will work in any version of Access.
Jim.
How to skip used mailing labels and print duplicates in Access 2002
http://support.microsoft.com/kb/299024
This will work in any version of Access.
Jim.
NextRecord, MoveLayout, and PrintSection@Jim, Wow, this is a cool feature I've never heard of.
ASKER
Solid solution and did exactly as I needed. Thank you very much, indeed.
<<@Jim, Wow, this is a cool feature I've never heard of. >>
It's good to know as it allows you to control how the report moves through the page and records.
For skipping the labels, you tell it next record false, move layout and printsection true. With that, the same record repeats over and over.
There's other things you can do as well, such as underlay the entire page with a grid (do a pre-printed form) at the start of the page, then move down the page normally.
Between that and two-pass printing, you can do some amazing stuff with the report engine.
Jim.
It's good to know as it allows you to control how the report moves through the page and records.
For skipping the labels, you tell it next record false, move layout and printsection true. With that, the same record repeats over and over.
There's other things you can do as well, such as underlay the entire page with a grid (do a pre-printed form) at the start of the page, then move down the page normally.
Between that and two-pass printing, you can do some amazing stuff with the report engine.
Jim.
underlay the entire page with a gridJim, how would you do this?
<<Jim, how would you do this? >>
https://www.experts-exchange.com/Microsoft/Development/MS_Access/A_8911-How-to-underlay-a-section-in-Microsoft-Access-Reports.html
Jim.
PS. Per your request (or was it Dale? - I'll have to look), all Access articles have been moved to the top topic area except for two, which won't re-submit because of formatting. I hadn't mentioned it yet, but since we somewhat on the topic of articles, I thought I'd mention it
https://www.experts-exchange.com/Microsoft/Development/MS_Access/A_8911-How-to-underlay-a-section-in-Microsoft-Access-Reports.html
Jim.
PS. Per your request (or was it Dale? - I'll have to look), all Access articles have been moved to the top topic area except for two, which won't re-submit because of formatting. I hadn't mentioned it yet, but since we somewhat on the topic of articles, I thought I'd mention it
That's great, but it must have been Dale's request :-)
Edit... or you could use Peter's free software that he mentioned. :)