I can't save after "Set specific printer to a report"

[nam0812]

Hello
my application access has a report name "RpKQNS"
In a form i made a combobox "CboPrinters" to change printer for report "RpKQNS"
but it can't be saved. There are no change in "The printer for that report"
Anyone has idea
Thanks in advance

Here the vba code

Private Sub FillPrinterList(ctl As Control)
    Dim prt As Printer
    ctl.RowSourceType = "Value List"
    For Each prt In Application.Printers
       ctl.AddItem prt.DeviceName
    Next prt

End Sub

Private Sub Form_Load()

    Call FillPrinterList(Me.CboPrinters)
    On Error Resume Next
    Me.CboPrinters = Application.Printer.DeviceName
End Sub

Private Sub CboPrinters_AfterUpdate()

    Dim prts As String
    prts = Me.CboPrinters.Value
    DoCmd.OpenReport "RpKQNS", acViewDesign, , , acHidden
    Set Reports(RpKQNS).Printer = Application.Printers(prts)
    DoCmd.Close acReport, "RpKQNS", acSaveYes


End Sub

 

[theDBguy]

Hi. Welcome to AWF!

Are you getting any error messages or a warning prompt to confirm before saving the design changes?

 

[nam0812]

Hi
theDBguy

There are no error messages or a warning. It can change printer but no saved in report

 

[Isaac]

Here is a very helpful bit of code from Microsoft, which makes it appear that you should set your report object after opening the report in design view, and should leverage the .PrtDevNames property instead.

Automate process of selecting printer for a report - Office

Describes how to programmatically select a printer and print the reports with the options that you predefine. Assumes you are familiar with the Access database and Visual Basic programming.

docs.microsoft.com

 

[nam0812]

Hi Isaac
I want to user can set specific printer for a report

 

[Isaac]

Hi Isaac
I want to user can set specific printer for a report

Yes, that is what I understood. Hopefully that Microsoft post can give you a path forward.

 

[Isaac]

I am not sure if this will work, but you can try it.

Private Sub CboPrinters_AfterUpdate()

    Dim prts As String, rpt as Report
    prts = Me.CboPrinters.Value
    DoCmd.OpenReport "RpKQNS", acViewDesign
    Set rpt = Reports("RpKQNS")
    rpt.PrtDevNames = prts
    DoCmd.Close acReport, "RpKQNS", acSaveYes

End Sub

 

[arnelgp]

on old MSA version, probably worked before.
unfortunately, now it doesn't.

you can still achieve what you need, switching different printer
on different report.

first create a New table (tblReportPrinter):

ReportName (short text, 100)
PrinterName(short text, 100)

change the Combo's AfterUpdate event:

Private Sub CboPrinters_AfterUpdate()
    If DCount("1", "tblReportPrinter", "ReportName='RpKQNS'") > 0 Then
        CurrentDb.Execute "Update tblReportPrinter " & _
            "Set PrinterName='" & Me.cboPrinters & "' Where ReportName='RpKQNS';"
    Else
        CurrentDb.Execute "Insert Into tblReportPrinter (ReportName, PrinterName) " & _
            "Select 'RpKQNS', '" & Me.cboPrinters & "';"
    End If
End Sub

add code to 'RpKQNS report's Activate and Close event:

Private Sub Report_Activate()
    If DCount("1", "tblReportPrinter", "ReportName='" & Me.Name & "'") > 0 Then
        Set Application.Printer = Application.Printers(DLookup("PrinterName", "tblReportPrinter", "ReportName='" & Me.Name & "'"))
    End If
End Sub

Private Sub Report_Close()
    Set Application.Printer = Nothing
End Sub

 

[Isaac]

that changes the default printer for the entire application which I would have suggested but that is not what the Op wanted. He wanted it per report...

 

[arnelgp]

I am not sure if this will work, but you can try it.

i don't believe you did not try it.

 

[nam0812]

Thanks Isaac
I will try it

 

[nam0812]

Thanks arnelgp. Your code worked.
I also found that "Has Module" property of RpKQNS is yes. My code worked in other report with "Has Module" No