Link to home
Start Free TrialLog in
Avatar of Patrick Miller
Patrick MillerFlag for United States of America

asked on

Issues with successfully printing labels from within MS Access 2010 to an Intermec PD41 label printer

I need to print labels that are coming off of a Roll inside the Intermec PD41 label printer.    Labels are 2 1/8 inchec long and 4 inches wide.   I have tried printing continuous labels and I cannot stop the program from skipping labels.   I tried to print a report and it has the same problem.  Any suggestions on whether or not it is possible to print in a truly continous fashion through Access.  I was hoping not to have to buy a label printing program to resolve this issue.
ASKER CERTIFIED SOLUTION
Avatar of Nick67
Nick67
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Patrick Miller

ASKER

I don't want to print just one at a time.   I want to print a hundred at a time.   I have a query built with the data I just need it to fit on the labels of the PD41 Thermal transfer printer.
It prints a report of labels.
Each label is a page.
However many pages the report comes to is how many comes out.

I usually don't have more than about 40 or so back to back.
There isn't any reason you can't get hundreds.
Define a paper size.
Open the report via VBA in design view
Force the correct printer, paper size, margins and options.
Switch to preview.
Force a print.
Close and discard changes.

Close and discard changes!?!

Access doesn't play nice and save the changes correctly, anyway, and constantly mucking with an object is a sure way to corrupt it.
It prints one good label and 3 blank labels and then 1 good label and three blanks.
If I DON'T force the paper-size issue in VBA, I'll get one label and then 10 blanks.
This is because Access gets it in it's head to use letter size paper (11" long, and my labels are 1" long)
So you need to go into the printer server properties
User generated imageAnd define a properly sized paper format
User generated image
While you can set up your report and your printer through the GUI, and preview it, you will find that Access doesn't respect your wishes in this matter, and will flip back to using the wrong paper.
So you throw some message boxes in temporarily to discover the integer value of the correct paper's size.
And then you set it up in VBA as I have outlined.

What have you done so far?
How do you get it to use the form that you put in the Print Server?   I called it "Barcode"

4" wide by 2 " high

Private Sub Command9_Click()
Dim stDocName As String
stDocName = "rptMtcnLab"
'Open it in DesignView, Hidden
DoCmd.OpenReport stDocName, acViewDesign, , , acHidden
'Set the name of the report's proper label printer.  I have that as a printer named 'Label'
Reports(stDocName).Printer = Application.Printers("\\sfpad\EasyCoder PD41 (203 dpi) - IPL")
'("label")

'No Margings, tractor feed and the paper size number that corresponds to the custom form in the Print Server control panel
With Reports(stDocName).Printer
    .LeftMargin = 0
    .RightMargin = 0
    .TopMargin = 0
    .BottomMargin = 0
    .PaperSize = 256
End With
'Print the label
DoCmd.OpenReport stDocName, acNormal
'toss out any changes
DoCmd.Close acReport, stDocName, acSaveNo
End Sub
That's the hard part!
And the part that, once I get it right, makes me NOT want to screw with the report unless absolutely necessary!
Now, I highly doubt that 256 is indeed the paper size.
Throw
msgbox me.printer.papersize
into the detail_format event of the report.
You need a form of one sort or another to hang the VBA from
User generated imageFor me, I preview the report with the button
Damn.  Letter-size
User generated imageI go into page setup and fix it
User generated imageIt's the right size now
User generated image
When the form opens I try to have it fix the report
Private Sub Form_Open(Cancel As Integer)
'...stuff omitted for brevity
Call FixLabelPrinter
End Sub
'----------------------
Private Sub FixLabelPrinter()
Dim ptr As Printer
Dim ptrstring As String
Dim HaveLabelPrinter As Boolean
For Each ptr In Application.Printers
    If ptr.DeviceName Like "label" Then
        ptrstring = ptr.DeviceName
        HaveLabelPrinter = True
        Exit For
    ElseIf ptr.DeviceName Like "*label*" Then
        ptrstring = ptr.DeviceName
        HaveLabelPrinter = True
    End If
Next ptr


If HaveLabelPrinter = False Then
    MsgBox "You don't have the label printer installed.  Ask Nick about that"
    Exit Sub
End If

Dim stDocName As String
stDocName = "rptBillingLabel"

Dim mypapersize As Integer

Select Case ReturnComputerName
    Case "touchsmart2"
        mypapersize = 130
    Case "office4"
        mypapersize = 152 '195
    Case "office3"
        mypapersize = 121
    Case "office2"
        mypapersize = 161
    Case "mobile1"
        'mypapersize = 161
    Case "itadmin1"
        mypapersize = 161
    Case Else
        'mypapersize = 132
End Select

DoCmd.OpenReport stDocName, acViewDesign ', , , acHidden
Set Reports(stDocName).Printer = Application.Printers(ptrstring)
With Reports(stDocName).Printer
    .LeftMargin = 0
    .RightMargin = 0
    .TopMargin = 0
    .BottomMargin = 0
    .PaperBin = acPRBNTractor
    If ReturnComputerName = "office4" Then
        .PaperSize = 152 '195
    End If
End With

DoCmd.OpenReport stDocName, acViewPreview
DoCmd.Close acReport, stDocName, acSaveYes

End Sub

Open in new window


And yet, the report previews as letter on occasion.
The button for previewing the report tries to keep it fixed
Private Sub cmdPreview_Click()

Dim ptr As Printer
Dim ptrstring As String
Dim HaveLabelPrinter As Boolean
For Each ptr In Application.Printers
    If ptr.DeviceName Like "label" Then
        ptrstring = ptr.DeviceName
        HaveLabelPrinter = True
        Exit For
    ElseIf ptr.DeviceName Like "*label*" Then
        ptrstring = ptr.DeviceName
        HaveLabelPrinter = True
    End If
Next ptr

'MsgBox ptrstring

If HaveLabelPrinter = False Then
    MsgBox "You don't have the label printer installed.  Ask Nick about that"
    Exit Sub
End If

Dim stDocName As String
stDocName = "rptBillingLabel"

Dim mypapersize As Integer

Select Case ReturnComputerName
    Case "touchsmart2"
        mypapersize = 130
    Case "office4"
        mypapersize = 152 '195
    Case "office3"
        mypapersize = 121
    Case "office2"
        mypapersize = 161
    Case "mobile1"
        'mypapersize = 161
    Case "itadmin1"
        mypapersize = 161
    Case Else
        'mypapersize = 132
End Select

DoCmd.OpenReport stDocName, acViewDesign ', , , acHidden
Set Reports(stDocName).Printer = Application.Printers(ptrstring)
With Reports(stDocName).Printer
    .LeftMargin = 0
    .RightMargin = 0
    .TopMargin = 0
    .BottomMargin = 0
    .PaperBin = acPRBNTractor
    If ReturnComputerName = "office4" Then
        .PaperSize = 152 '195
    End If
End With

DoCmd.Close acReport, stDocName, acSaveYes
DoCmd.OpenReport stDocName, acPreview

Exit_cmdPreview_Click:
    Exit Sub

Err_cmdPreview_Click:
    MsgBox Err.Description
    Resume Exit_cmdPreview_Click
    
End Sub

Open in new window


And yet it will preview as letter-sized at times.
Once I do get it properly sized though, the print button code never fails to get it to spit out labels properly
Public Sub cmdPrint_Click()

Dim ptr As Printer
Dim ptrstring As String
Dim HaveLabelPrinter As Boolean
For Each ptr In Application.Printers
    If ptr.DeviceName Like "label" Then
        ptrstring = ptr.DeviceName
        HaveLabelPrinter = True
        Exit For
    ElseIf ptr.DeviceName Like "*label*" Then
        ptrstring = ptr.DeviceName
        HaveLabelPrinter = True
    End If
Next ptr

If HaveLabelPrinter = False Then
    MsgBox "You don't have the label printer installed.  Ask Nick about that"
    Exit Sub
End If

Dim mypapersize As Integer

Select Case ReturnComputerName
    Case "touchsmart2"
        mypapersize = 130
    Case "office4"
        mypapersize = 152 '195
    Case "office3"
        mypapersize = 121
    Case "office2"
        mypapersize = 161
    Case "mobile1"
        'mypapersize = 161
    Case "itadmin1"
        mypapersize = 161
    Case Else
        'mypapersize = 132
End Select


Dim stDocName As String
stDocName = "rptBillingLabel"
'DoCmd.OpenReport stdocname, acPreview
DoCmd.OpenReport stDocName, acViewDesign, , , acHidden
Reports(stDocName).Printer = Application.Printers(ptrstring) '("label")
'Set Reports(stdocname).Printer = Application.Printers(ptrstring)
With Reports(stDocName).Printer
    .LeftMargin = 0
    .RightMargin = 0
    .TopMargin = 0
    .BottomMargin = 0
    .PaperBin = acPRBNTractor
    If ReturnComputerName = "office4" Then
        .PaperSize = 152 '195
    End If
End With
DoCmd.OpenReport stDocName, acNormal
DoCmd.Close acReport, stDocName, acSaveNo

Exit_cmdPrint_Click:
    Exit Sub

Err_cmdPrint_Click:
    MsgBox Err.Description
    Resume Exit_cmdPrint_Click
    
End Sub

Open in new window


But the trick is to get it to print-from-preview correctly at least once, and msgbox out the papersize and get that right.
As you can see from the code, despite the fact that each different machine has the same printer paper form created on it, the numbers vary from machine to machine, and none of them are 256

I doubt yours is either.
And if anything isn't perfect, Access flips back to letter-size again.
So, how did you get it to play nice in the end?
I setup my default printer to be the label printer.   I followed your instructions on the print server settings .  I did not have to use the VB.   I made my label on the Access report smaller than the 4" wide by 2" high and when I previewed the report I saw that it was viewing properly and then I printed the labels.  They printed perfectly.  The printer took care of the label size and spacing.  

I cannot thank you enough.
Ah, good enough then.
Perhaps it is the Okidata that makes mine such a monster.
I had a devil of a time to prevent creep -- that the printer advance EXACTLY the right amount and not too little or too much, thereby slowly moving the printing off the label.
I am glad for you that defining the custom form size is all it took!

Nick67