Solved

How to print n copies of one label on same sheet

Posted on 2013-05-23
14
1,029 Views
Last Modified: 2013-05-24
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:
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

Open in new window


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.
0
Comment
Question by:CoStar
  • 5
  • 4
  • 3
  • +1
14 Comments
 
LVL 12

Accepted Solution

by:
pdebaets earned 250 total points
ID: 39192905
Try our free Label Saver utility: http://peterssoftware.com/ls.htm
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 39192907
It doesn't work that way.  Each label is bound to a record so in order to print 5 labels, you need to have 5 records in the table that your report is bound to.

Edit... or you could use Peter's free software that he mentioned. :)
0
 

Author Comment

by:CoStar
ID: 39192921
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.
0
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 

Author Comment

by:CoStar
ID: 39192928
Peter:
will your utility print multiple labels of ONE record on ONE page?
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 39192968
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:
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

Open in new window


I have to add though that Peter's utility is much more flexible.
0
 
LVL 57
ID: 39193035
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.
0
 
LVL 12

Expert Comment

by:pdebaets
ID: 39193038
"Peter:
will your utility print multiple labels of ONE record on ONE page?"

Yes.
0
 
LVL 57
ID: 39193078
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.
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 39193115
NextRecord, MoveLayout, and PrintSection
@Jim, Wow, this is a cool feature I've never heard of.
0
 

Author Closing Comment

by:CoStar
ID: 39193453
Solid solution and did exactly as I needed. Thank you very much, indeed.
0
 
LVL 57
ID: 39193987
<<@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.
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 39194116
underlay the entire page with a grid
Jim, how would you do this?
0
 
LVL 57
ID: 39194189
<<Jim, how would you do this? >>

http://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
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 39195898
That's great, but it must have been Dale's request :-)
0

Featured Post

Active Directory Webinar

We all know we need to protect and secure our privileges, but where to start? Join Experts Exchange and ManageEngine on Tuesday, April 11, 2017 10:00 AM PDT to learn how to track and secure privileged users in Active Directory.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question