Results 1 to 10 of 10
  1. #1
    Join Date
    Dec 2005
    Posts
    121

    Unanswered: Print when closing? Can it be done?

    All,
    I have tried several methods, macros, sendkeys, etc. but have yet to find a way to make this work.

    I want to open a report in preview mode, but, when closing (or opening, whichever works better), ask the user if they want to print the report, using vb OkCancel. If they click Cancel, the report just closes, but if they click Ok, it prints the report to the default printer.

    Any suggestions from the group?

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Here is one solution:

    1. Create an empty form (in my example its name is Form1) and add the following code to its module:
    Code:
    Option Compare Database
    Option Explicit
    Private m_ReportName As String
    
    Public Function PrintReport(ByVal ReportName As String)
    
        m_ReportName = ReportName
        Me.TimerInterval = 1000
        
    End Function
    
    Private Sub Form_Timer()
    
        Dim rpt As Report
        Dim StillOpen As Boolean
        
        Do
            StillOpen = False
            For Each rpt In Reports
                If rpt.name = m_ReportName Then StillOpen = True
            Next
            If StillOpen = False Then Exit Do
        Loop
        DoCmd.OpenReport m_ReportName
        Me.TimerInterval = 0
        m_ReportName = ""
        
    End Sub
    Add the following code to the report's module:
    Code:
    Option Compare Database
    Option Explicit
    
    Private Sub Report_Close()
    
        Dim frm As Form
        
        If MsgBox("Do you want to print the report?", vbQuestion + vbYesNo, Me.name & " is closing.") = vbYes Then
            Set frm = Forms!Form1
            frm.PrintReport Me.name
            Set frm = Nothing
        End If
        
    End Sub
    All you have to do now is to be sure that Form1 is open when you open the report (it can be hidden, though). You can even open the form when you open your application and use it with different reports.
    Have a nice day!

  3. #3
    Join Date
    Dec 2005
    Posts
    121
    I have set this up, and it is working great with one small exception. It is asking me twice if I want to print the form, once as I click to close the form, then again once the print out box appears to show delivery to the printer. Is there any reason why it asks twice, the same message? It only prints once.

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Are you sure that the report is set up to use the default printer? Normally you should not be asked twice and no print out box should appear.
    Have a nice day!

  5. #5
    Join Date
    Dec 2005
    Posts
    121
    Yes, it is set up to the default printer. It presents the message box asking if you want to print the report, Ok or Cancel, you select yes, you see the now printing box pop open showing the printer it is going to, then the message box comes back again and asks the same question. Also, I named the form that is hidden Form1 as your example just to make sure it matched. I am just afraid it might be confusing to users to be asked twice if they want to print.

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    OK, then try this:
    Code:
    Private Sub Report_Close()
    
        Dim frm As Form
        
        If Me.Visible = True Then
            If MsgBox("Do you want to print the report?", vbQuestion + vbYesNo, Me.name & " is closing.") = vbYes Then
                Set frm = Forms!Form1
                frm.PrintReport Me.name
                Set frm = Nothing
            End If
        End If
        
    End Sub
    Have a nice day!

  7. #7
    Join Date
    Jun 2009
    Posts
    89
    trying to understand this code. What does the timer part do?

    EDIT, ok i understand it more now. But why is it necessary to keep a form on loop trying to print a report? is it not possible to do this in the reports unload event?

  8. #8
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    For printing the report you have to issue a Docmd.OpenReport command.

    If you try to do this while the report is closing, you get an error message:
    Run-time error '2585': "This action can't be carried out while processing a Form or Report event".

    The timer is there to be sure the report is closed before trying to reopen it. The delay could probably be shorter than one second, but this depends on the machine the program is running on. One second is safe.

    In the application where this code is used, the form has several additional properties and methods that allow to do various things with a report, such as open it several times simultaneously with different parameters etc. In this context, the timer just was handy.

    As I first wrote, this is one solution, among many others.
    Have a nice day!

  9. #9
    Join Date
    Dec 2005
    Posts
    121
    That worked! This will be a great feature to users to keep from printing the report if the initial view gives them what they want. And if they do want to print it, they don't have to replicate the action a second time. Helps with our "Green" initiative!

    Thanks for the tip, you guys are a great group to share information like you do. Over the last 4 or 5 years I have learned a lot!

  10. #10
    Join Date
    Jul 2004
    Location
    South Dakota
    Posts
    267
    You code try this approach as well. Just put the code into a standard module. Then call the PrintReport function passing it the name of the report that you want to print from within Close event of the report. You can just pass it Me.Name. Of course you would want to put the "Do you want to print" dialog code in the Close event as well before you call this function.

    Code:
    Public Function PrintReport(pReportName As String)
        
        Static x As Boolean
        
        If x = False Then
            x = True
            DoCmd.CopyObject , "temp", acReport, pReportName
            DoCmd.OpenReport "temp", acViewNormal
            DoCmd.Close acReport, "temp"
            DoCmd.DeleteObject acReport, "temp"
        Else
            x = False
        End If
    
    End Function

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •