There have been several threads relating to what I have found to be a complex subject. I have 2 reports, called in succession but one needs to go to a standard printer, the other to a PDF file I do not want to chose the printer for each report by having to select a printer via a dialog box; I want to do it by code.
I have found the following works very well BUT would welcome any comments from my programming peers.
Firstly, you need to know the Printer number in order to designate the printer for each report. This simple Report does that: note I have limited the fields on the report to a max of 6 printers. You only need to run it once to determine the printer numbers. Fields index1 through to index6 and P1 through to P6 are unbound and refer to printers 0(yes) to 5.
Option Compare Database
Dim PrinterQuantity, iCount As Integer
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
On Error GoTo Err_Printers
PrinterQuantity = InputBox("How many printers do you have installed?", "Printers")
If PrinterQuantity > 6 Then
MsgBox "Need to amend the report as a maximum of only 6 lines are set", vbCritical, "Max 6 at the moment"
Exit Sub
Else: End If
For iCount = 1 To PrinterQuantity
Set Application.Printer = Application.Printers(iCount - 1)
Me.Controls("index" & iCount) = iCount - 1
Me.Controls("P" & iCount) = Application.Printer.DeviceName
Next iCount
'
Exit_Printers:
Exit Sub
Err_Printers:
If Err.Number = 5 Then
Resume Exit_Printers
Else
MsgBox Err.Description & " --- " & Err.Number
End If
________________________________________________________
Now the reports can be called via code; such as
Function MyPrint()
' strSearch is my Unique reference variable for a report filter
strSearch = "old504"
Set Application.Printer = Application.Printers(1)
'Printer(1) is a PDF
DoCmd.OpenReport "R-Print Invoice", , , "[Job Ref] = '" & strSearch & "'"
DoCmd.Close acReport, "R-Print Invoice"
Set Application.Printer = Application.Printers(2)
'Printer (2) is a Laserjet
DoCmd.OpenReport "R-Print Quotation", , , "[Job Ref] = '" & strSearch & "'"
DoCmd.Close acReport, "R-Print Quotation"
End Function
____________________________________________________________
I realise that there is a method to use the File/Page Setup/Page/Use Specific Printer as this does the same thing but if like me you want to use code, then this works.
One small point, if one of the printers is a PDF or Image Composer, run that report first as the default file name for some reason relates to the name of the first report. (No idea why it does that, unless someone knows the solution)
Maybe this thread will be useful to someone. If the moderators think its rubbish, then by all means delete it; I shall not be upset!
I have found the following works very well BUT would welcome any comments from my programming peers.
Firstly, you need to know the Printer number in order to designate the printer for each report. This simple Report does that: note I have limited the fields on the report to a max of 6 printers. You only need to run it once to determine the printer numbers. Fields index1 through to index6 and P1 through to P6 are unbound and refer to printers 0(yes) to 5.
Option Compare Database
Dim PrinterQuantity, iCount As Integer
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
On Error GoTo Err_Printers
PrinterQuantity = InputBox("How many printers do you have installed?", "Printers")
If PrinterQuantity > 6 Then
MsgBox "Need to amend the report as a maximum of only 6 lines are set", vbCritical, "Max 6 at the moment"
Exit Sub
Else: End If
For iCount = 1 To PrinterQuantity
Set Application.Printer = Application.Printers(iCount - 1)
Me.Controls("index" & iCount) = iCount - 1
Me.Controls("P" & iCount) = Application.Printer.DeviceName
Next iCount
'
Exit_Printers:
Exit Sub
Err_Printers:
If Err.Number = 5 Then
Resume Exit_Printers
Else
MsgBox Err.Description & " --- " & Err.Number
End If
________________________________________________________
Now the reports can be called via code; such as
Function MyPrint()
' strSearch is my Unique reference variable for a report filter
strSearch = "old504"
Set Application.Printer = Application.Printers(1)
'Printer(1) is a PDF
DoCmd.OpenReport "R-Print Invoice", , , "[Job Ref] = '" & strSearch & "'"
DoCmd.Close acReport, "R-Print Invoice"
Set Application.Printer = Application.Printers(2)
'Printer (2) is a Laserjet
DoCmd.OpenReport "R-Print Quotation", , , "[Job Ref] = '" & strSearch & "'"
DoCmd.Close acReport, "R-Print Quotation"
End Function
____________________________________________________________
I realise that there is a method to use the File/Page Setup/Page/Use Specific Printer as this does the same thing but if like me you want to use code, then this works.
One small point, if one of the printers is a PDF or Image Composer, run that report first as the default file name for some reason relates to the name of the first report. (No idea why it does that, unless someone knows the solution)
Maybe this thread will be useful to someone. If the moderators think its rubbish, then by all means delete it; I shall not be upset!
