Results 1 to 4 of 4
  1. #1
    RMittelman is offline Advanced
    Windows 10 Access 2016
    Join Date
    Dec 2010
    Location
    Simi Valley, CA
    Posts
    211

    Report Code Doesn't Run if Report is Exported Using DoCmd.OutputTo

    I have various reports that I want to be able to open in Preview mode, and/or export to PDF, depending upon which form I'm using to create the report. Here is the code I'm using, in a module:



    Code:
    Public Function PreviewOrExportReport(action As ReportActionEnum) As Boolean
    
        Dim isOK    As Boolean
        
        PreviewOrExportReport = False
        
        If action = raPreview Then
            
            On Error Resume Next
            DoCmd.OpenReport TempVars!ReportName, acViewPreview
            PreviewOrExportReport = Err.Number = 0
            If Err.Number <> 0 Then
                MsgBox Err.Description, vbExclamation, "Error"
            End If
            
        ElseIf action = raExport Then
            
            ' if report exists, ask if ok to replace
            isOK = Dir$(TempVars!ReportFile) = ""
            If Not isOK Then isOK = MsgBox("This report exists. OK to replace?", vbQuestion + vbYesNo + vbDefaultButton2, "Duplicate") = vbYes
            If isOK Then
                On Error Resume Next
                DoCmd.OutputTo acOutputReport, TempVars!ReportName, acFormatPDF, TempVars!ReportFile, False
                PreviewOrExportReport = Err.Number = 0
                If Err.Number <> 0 Then
                    MsgBox Err.Description, vbExclamation, "Error"
                End If
            End If
            
        End If
    
    
    End Function
    I have code running in the Report_Load() event and the Detail_Format() event. If I open the report in preview mode using the first part of the code above, everything works fine.
    However, when I choose to export the report using the second part of the code, no report code runs at all. Therefore, items in the page header or report header appear blank, because they depend on that code running.

    Researching this issue supplied a solution, which is to open the report in preview mode (so all the code runs), then export, then close the report in code.
    The modified portion of the module code looks like this:

    Code:
            If isOK Then            On Error Resume Next
                DoCmd.OpenReport TempVars!ReportName, acViewPreview
                If Err.Number = 0 Then
                    DoCmd.OutputTo acOutputReport, TempVars!ReportName, acFormatPDF, TempVars!ReportFile, False
                    DoCmd.Close acReport, TempVars!ReportName
                End If
                PreviewOrExportReport = Err.Number = 0
                If Err.Number <> 0 Then
                    MsgBox Err.Description, vbExclamation, "Error"
                End If
            End If
    This works, but causes an unpleasant flickering when the report opens in preview mode.


    Is there any other solution which doesn't rely on opening the report in preview mode?
    The report is based on a query rather than a table, so it's not so easy to put in values that are set at run-time into the data source query.

    Thanks...

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    AFAIK, .OutputTo does not open a report, hence your code doesn't run. Not sure if you realize that or just thought that the code wasn't running from the opened report. To eliminate the flicker, open the report hidden for the 2nd option.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    RMittelman is offline Advanced
    Windows 10 Access 2016
    Join Date
    Dec 2010
    Location
    Simi Valley, CA
    Posts
    211
    Quote Originally Posted by Micron View Post
    AFAIK, .OutputTo does not open a report, hence your code doesn't run. Not sure if you realize that or just thought that the code wasn't running from the opened report. To eliminate the flicker, open the report hidden for the 2nd option.
    Thanks Micron for the quick reply. Quite confusing, because the OutputTo does create the report with the proper data (except for the items updated in the report code). So it's definitely unintuitive to think the report code wouldn't run. None of the code, not just the code in the report open event.

    However, it seems an easy fix to actually open the report (even in hidden mode) then do the OutputTo while it's open.

    Thanks again...

  4. #4
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Some events don't run in certain report views. Perhaps the 'view' method used to Output isn't conducive to running your events. Sometimes turning off Echo (Application.Echo) solves screen flicker as well, but I'd open the report hidden rather than risk turning off screen updating for the entire session.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 04-12-2020, 08:34 PM
  2. Replies: 1
    Last Post: 12-11-2018, 01:05 PM
  3. Replies: 1
    Last Post: 07-30-2015, 12:56 PM
  4. DoCmd.OutputTo question
    By mkc80 in forum Access
    Replies: 5
    Last Post: 10-16-2012, 07:11 PM
  5. DoCmd.OutputTo
    By tylerg11 in forum Reports
    Replies: 2
    Last Post: 08-09-2012, 12:22 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums