Print report to specific printer

[JudyJones]

I have a report that should print to my label printer. How do I include code to send the report to that specific printer?

[HansV]

If you specify the printer in the report's page setup (click Use Specific Printer in the Page tab of the Page Setup dialog), Access should store the setting with the report.
Reputedly, the setting may be lost if 'Track name AutoCorrect info' is turned on for the database, so you should keep that turned off (Access 2003 or before: Tools | Options..., General tab; Access 2007 or later: Office button > Access Options..., Current Database).

It is possible to set the printer using code, but that is not very attractive, since you have to open the report in design view, set the printer, then close and save the report, and finally open it in preview or print mode.

Another option is to set Application.Printer using code, but this isn't very attractive either, since it changes the user's default printer system-wide. (You could set it back to the original printer afterwards, but if something goes wrong, the user would have to do that manually)

[Wendell]

This is one area that continues to be an issue when you have to direct a specific report to a printer for an application distributed across a group of users. If the printer is defined with a slightly different name on a workstation, Access detects that and gives the user a message that it can't find the desired printer and would you like to print the report on your default printer. If you are the only user, no problem, but if others want to use that report (label), and have their own label printer, make sure all the printers have the same exact name.

[JudyJones]

The instructions from Hans worked great to send the labels to the Dymo label maker but I could not get it to print the labels without leaving a blank label between each one. The print direction had to be changed to landscape since the Dymo prints from a continuous roll with a label size of 1 1/8" x 3 1/2". I tried making various adjustments but could not make it work. I would be open to suggestions, if anyone has any experience in using that type of label. The Wizard did not offer that manufacturer for me to select.

As Wendell noted when multiple users would use the file there would be a problem by specifying a specific printer unless they all had access to the same label printer. I'll have to check that out.

[ChrisGreaves]

How do I include code to send the report to that specific printer?

I usually let each user identify the appropriate printer.
Here's a bit of code that loads a listbox so that the user can elect a printer. The name of the printer is stored in an INI file, until the next time the user elects to change printers:

Code: Select all

Function LoadPrinterListBox(lb As ListBox)
    lb.Clear
    Dim strAr() As String
    Call BuildArrayOfPrinters(strAr, "", "", "")
    Dim lng As Long
    For lng = LBound(strAr) To UBound(strAr) - 1
        lb.AddItem (strAr(lng))
    Next lng
End Function

Here is the code that builds a string array with the names of all the printers:

Code: Select all

Function BuildArrayOfPrinters(strAr() As String, strServer, strUser, strPassword)
    Dim Printers
    Dim oService
    Dim oPrinter
    Dim iTotal
    If WmiConnect(strServer, kNameSpace, strUser, strPassword, oService) Then
        Set Printers = oService.InstancesOf("Win32_Printer")
    Else
    End If
    ReDim strAr(0)
    For Each oPrinter In Printers
        strAr(UBound(strAr)) = oPrinter.DeviceID
        ReDim Preserve strAr(UBound(strAr) + 1)
    Next
End Function

I have attached a complete module as a text file.

[HansV]

I have no experience with Dymo printers, but what are the settings in the tabs of the Page Setup dialog?

[Carol W.]

I could not get it to print the labels without leaving a blank label between each one.

I have no experience with this type of printer either. However, whenever I have an erroneous blank page in between report pages, the issue generally turns out to be that the report layout is too wide for the page. Hence, it overflows on to a second page (which is the blank page).

Suggest you check the width of your report layout, in report design mode, and compare it to the width of the label. Maybe the margins need adjusting?

It can't hurt to check .

[JudyJones]

On the tabs of the page setup dialog box I have the following settings: (Top, Bottom and Right margins .15, Left margin .23), (Page orientation - landscape), (Size - 30252 Address), (Number of columns 1), (Row spacing 0), (Column spacing is .125 and cannot be changed), (Column width 3"), (Height 1").

The actual label width is 3.5" but if I changed the column width to that much, I got 3 blank labels between each printed label. No matter how much less than 3" I changed the column width, I continued to get one blank label between each one. Changing the height did not seem to affect the print/preview at all.

On the layout itself, I kept reducing the height and finally got it to fit without the blank in between each label. Thanks to everyone who offered suggestions. By putting everyone's ideas together, the problem was solved. That is what makes this lounge such a wonderful resource.

[HansV]

Glad you were able to solve it! Thanks for posting back.