Print Access Report using VBA

[bconner]

I have a Report in Access and I would like to Print it to a certain Printer how would I do this using VBA Code?

 

[HiTechCoach]

It is possible to set Access's default print with VBA code before you print the the report.

How will your VBA code know which printer?

Note:
If you will always send to the same printer, then you can set the printer in the report's design, under page setup.

 

[ezfriend]

Go to
File -> Page Setup -> Page tab -> Use Specific Printer -> Printer... then Browse and chose one

 

[bconner]

I was going to use PrimoPDF as the default printer it's program that will convert the Access Report to a PDF File.

Problem is I don't know the VBA Syntax for sending the report to a specific printer. I have looked under DoCmd property but I can't find anything.

 

[honda882000]

If you are looking to have the printer dialog come up after you open the report, try this:

Sub print_dialog()

    On Error GoTo ErrorHandler
    
    DoCmd.OpenReport "Report1", acViewPreview
    
    'Opens print dialog for current screen (report in this case):
    DoCmd.RunCommand acCmdPrint

ErrorHandler:
    If Err.Number <> 0 And Err.Number <> 2501 Then
        MsgBox "Error: " & Err.Number & vbNewLine & Err.Description
        Exit Sub
    End If

End Sub

As far as the list of printers, the list of printers is stored in Application.printers

 

[genomon]

In Access 2002 & later, there is a new printer object model.
You can set any propperties and use any printer you like (we do it here all the time). Here is a link to the MS documentation, code samples, and sample database downloads:
http://msdn.microsoft.com/en-us/library/aa139946(office.10).aspx

 

[bconner]

below is the code I am using. When I execute the below code it runs and looks like it is printing then it errors out and the database automatically creates a backup and runs a compact repair. Any ideas why it might be bombing out????? I even tried another PDF writer other than CutePDF Writer called PrimoPDF and it to bombed out....

[FONT=Times New Roman][SIZE=3]Private Sub Button_Run_Multiple_Reports_Click()[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3] [/SIZE][/FONT]
[SIZE=3][FONT=Times New Roman]Dim strDefaultPrinter As String[/FONT][/SIZE]
[FONT=Times New Roman][SIZE=3] [/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3]' get current default printer.[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3] [/SIZE][/FONT]
[SIZE=3][FONT=Times New Roman]strDefaultPrinter = Application.Printer.DeviceName[/FONT][/SIZE]
[FONT=Times New Roman][SIZE=3] [/SIZE][/FONT]
[SIZE=3][FONT=Times New Roman]' switch to printer of your choice:[/FONT][/SIZE]
[FONT=Times New Roman][SIZE=3] [/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3]Set Application.Printer = Application.Printers("CutePDF Writer")[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3] [/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3] [/SIZE][/FONT]
[SIZE=3][FONT=Times New Roman]If Option_Ref_Loc_Bad_Debt_By_Paycode = True Then[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]DoCmd.OpenReport "Rpt_Ref_Loc_Bad_Debt_By_Paycode", acViewNormal[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]DoCmd.PrintOut[/FONT][/SIZE]
[FONT=Times New Roman][SIZE=3]DoCmd.Close acReport, "Rpt_Ref_Loc_Bad_Debt_By_Paycode", acSaveNo[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3] [/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3]End if[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3] [/SIZE][/FONT]
[SIZE=3][FONT=Times New Roman]' Switch back to original default printer[/FONT][/SIZE]
[FONT=Times New Roman][SIZE=3] [/SIZE][/FONT]
[SIZE=3][FONT=Times New Roman]Set Application.Printer = Application.Printers(strDefaultPrinter)[/FONT][/SIZE]
[FONT=Times New Roman][SIZE=3] [/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3]End sub[/SIZE][/FONT]

 

[BrettM]

acViewNormal is already telling the report to print. Isn't PrintOut used after acViewPreview?

Allen Brown has a neat printer selection utility at http://www.allenbrowne.com/AppPrintMgt.html

Regards Brett