Selecting printer in VBasic in Access

[Stev]

I am a beginner at using Access 2000. I have created two reports that are printing from a command button on an Access form. I would like each report to access a different printer. What is the VisualBasic function that allows me to select a printer?

 

[huzza]

Hi Steve,
this is a tricky one if you want to do it without prompting the user. I've tried various things to find out how you select a printer in vba code.

I have two suggestions the first is the easy one, if you want to prompt the user for which printer to print from. To do this alter the code on your command button to do/include the following:

Private Sub Command2_Click()
DoCmd.OpenReport "test", acViewPreview
DoCmd.RunCommand acCmdPrint
End Sub

For the second (if you want to hardcode which printer) is the following code. Note: I've only used it for printing word documents so I can't guarantee it will work. You will need the microsoft word 8.0 object libarary reference set in access (or equivalent if you have office 2000).

Private Sub Print_Click()
Dim OldPrinter as string
On Error GoTo Print_Click_Err
  
  OldPrinter = ActivePrinter
  ActivePrinter = "HP Deskjet 500"
  DoCmd.OpenReport "Report", acViewNormal
  ActivePrinter = OldPrinter

Print_Click_end:
    Exit Sub
Print_Click_Err:
    MsgBox Err.Description, vbOKOnly
    Resume Print_Click_end
End Sub

To print from a named printer you must replace the "HP Deskjet 500" with the name of the printer you want to print to (as it's shown in windows). This code changes the printer to this for the print job then changes it back to the windows default afterwards.

If you have any questions regarding this let me know.
Richard