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.
patrickmillerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Nick67Commented:
I do get labels off a continuous tractor-fed Okidata Microline 320 to print.
It isn't fun as Access REALLY wants letter-size paper.
It involves a fair bit of VBA
Mine are Avery 3.5" x 1" labels.
I've got a report 2.9896" wide by 0.625" tall
Top and bottom margins are 0.18"
Left and right margins are 0.2" and 0.5" respectively.

Now the hard part.

The computer didn't know from a 3.5" x 1" paper size
You have to go into device and printers and into the print server properties and set up a custom paper size.
You configure Access to use the specific printer and the specific paper size.
Throw some messageboxes into the detail_format event.
You are looking to capture the correct values of
Me.Printer.DeviceName
Me.Printer.PaperSize

Once you have those documented, you need a form with a button to trigger your label printing.
What it is going to do is this

Dim stDocName As String
stDocName = "TheNameOfYourSingleLabelReport"
'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(ptrstring) '("label")
'Now, screw with the printer
'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
    .PaperBin = acPRBNTractor
    .PaperSize = 152
    End If
End With
'Print the label
DoCmd.OpenReport stDocName, acNormal
'toss out any changes
DoCmd.Close acReport, stDocName, acSaveNo

Open in new window


And I am VERY, VERY careful NOT to SCREW with the report design once I get it working.
Access will flip it back to trying to print on Letter paper in a heartbeat.

YMMV

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
patrickmillerAuthor Commented:
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.
Nick67Commented:
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.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

patrickmillerAuthor Commented:
It prints one good label and 3 blank labels and then 1 good label and three blanks.
Nick67Commented:
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
print serverAnd define a properly sized paper format
form
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?
patrickmillerAuthor Commented:
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
Nick67Commented:
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
formFor me, I preview the report with the button
Damn.  Letter-size
letterI go into page setup and fix it
fixedIt's the right size now
fixed
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.
Nick67Commented:
So, how did you get it to play nice in the end?
patrickmillerAuthor Commented:
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.
Nick67Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.