Solved Error 2585 on DoCmd.OutputTo in public function

[GaP42]

I am constructing a public function which accepts inputs and prompts to save various reports in a specific folder when called on the close event of the print preview of the report. The relevant code where this error occurs is:

    sFilename = sRptPath & sFilename & sFileType
    MsgBox "WARNING!!! If a file named -- " & sFilename & " -- exists it will be deleted. " & vbCrLf & _
            "NOTE: The file name uses todays date. Unless this report has been run more than once today, for the same person, this is not an issue." & vbCrLf & _
            "Rename the old file before pressing OK if you need to keep the old file.", vbInformation, "Overwrite warning"
    KillFile (sFilename)
    Debug.Print sRptName
    Debug.Print sFilename
    DoCmd.OutputTo acOutputReport, sRptName, acFormatPDF, sFilename  ' <---- ERROR HERE
 
    MsgBox "File saved successfully" & vbCrLf & _
                " -- & sFileName", vbInformation + vbOKOnly

The Debug.Print output looks OK:
sRptName: rptBloodPressureStats
sFileName: C:\Users\....\Reports\MyTestyMcTesty\BloodPressureStats_00007_20250214.pdf (... is just obfuscation)

error 2485: This action can't be carried out while processing a form or report event.

DoCmd.OutputTo can be used in public function? Is it because the function is called while the report is in Print Preview on the on Close?
Why or what is incorrect? How is it fixed?

 

[arnelgp]

you can use the Unload event of the report at the same time
you can check if the report is in PrintPreview (acCurViewPreview = 5) view"

Private Sub Report_Unload(Cancel As Integer)
If Me.CurrentView = 5
    'in print preview
    'your code goes here
End If
End Sub

are you Outputting Same report as in PrintPreview view?

 

[GaP42]

are you Outputting Same report as in PrintPreview view?

Yes.
Will look at the other suggestion.

EDIT/ UPDATE
Same error occurs when the call to the public function is made from the on unload event of the report in Print Preview

 

[arnelgp]

maybe call a "Hidden" form when the print preview is closed.

Private Sub Report_Unload(Cancel As Integer)
If Me.CurrentView = 5
    'in print preview
    'open a hidden form to carry on with the OutputTo
    docmd.OpenForm "HiddenFormName",,,,,acHidden
End If
End Sub

on the Load of the hidden form, do your Outputting code.

Private Sub Form_Load()
'your Outputting code here
'
'....
'....
'
'then set a timer to close the form
'
Me.TimerInterval = 500
End Sub

Private Sub Form_Timer()
'kill the timer
Me.TimerInterval = 0
'just close the this form
DoCmd.Close acForm, Me.Name, acSaveNo
End Sub

 

[The_Doc_Man]

You have a call to something called "KillFile" that I infer will eventually perform a "Kill <filename>" operation either through VBA's KILL command or perhaps a FileSystemObject Delete command. What else does it do that made you want to write a custom routine to delete a file when a direct VBA command was available?

The snippet you showed us in post #1 of the thread includes a KillFile. If I correctly followed that discussion, it is executed from Close event code in the report that eventually generates the replacement for the killed file. Two different thoughts come to mind.

First, after you want to kill that file, you immediately want to create a new one. File deletion can take a moment. Put a DoEvents after the KillFile. But that is a less likely issue.

Second, you are processing a report that will be fed to a .PDF converter at the same time that it is up for display in report preview AND is being closed. Arnelgp also saw an issue with the idea that you are doing two contradictory things at once to the same file from that event routine that is involved in closing the report. His idea of a "relay form" - having a form open up that will have a life of its own to print the report - is probably good.

I think what is going on is related to the idea that the report has a few active file handles associated with files being created. IF Access is doing what I think it is doing, it is closing all the files that were opened during that report's processing and therefore is not allowing you to open a new file. A report_Close event is essentially a part of a "process rundown" sequence that will eventually lead to deletion of the child process that is in the open report window. That's what I think is causing the "can't do this now" error.

 

[GaP42]

Thank you - I will get back to the relay file concept - busy and away for a few days.
Re the KillFile:

You have a call to something called "KillFile" that I infer will eventually perform a "Kill <filename>" operation either through VBA's KILL command or perhaps a FileSystemObject Delete command. What else does it do that made you want to write a custom routine to delete a file when a direct VBA command was available?

The code is:

Public Function KillFile(filePath As String) As Boolean
' Function to delete a file:
'   makes sure its file property is set to vbNormal (use SetAttr function).
'   Also verifies if the file exists before attempting to delete it.

    If Len(Dir(filePath)) > 0 Then
        SetAttr filePath, vbNormal
        Kill filePath
        KillFile = True
        Exit Function
    End If
    KillFile = False
End Function

Courtesy of one of our fellow posters here..

General flow:
Report in Print Preview
Close Print Preview is selected
On Unload Event triggers Msg about save or not.
If Yes then call public Function to save report:
- determine/construct filename and path etc
- delete existing file if exists
- use Docmd.OutputTo -as in code snippet
After completion return to PrintPreview/ on unload --> Close

Wondering if this cannot be done on the rundown events of Close, what other event might be used as the trigger for saving. Immediately after presenting PrintPreview seems too early as the user has not yet had a chance to review the print preview, and if cancelled won't be asked again, they would need to remember to do it manually before closing.

 

[The_Doc_Man]

Wondering if this cannot be done on the rundown events of Close, what other event might be used as the trigger for saving.

You probably would want a separate button to trigger the print preview and then just allow that to be closed before doing whatever is doing the close operation. Saving an already opened file during a CLOSE event is perfectly consistent with CLOSE events. It is the fact that during a close you are OPENING a new file from the CLOSE event that makes it a bit kinky. Or at least that is my opinion on what is happening.

 

[GaP42]

@arnelgp and @The_Doc_Man. Thank you for your assistance. Using the hidden form method is working to save the PrintPreview as a pdf on Close of the PrintPreview (if user responds Yes to save) avoiding the runtime Error 2585.
The requirement was to prompt to save the PrintPreview when closing it - automating the allocation of a folder and filename, based on the registered person record (there are many report variants available, and some further tweaking to occur).
The Print Preview is launched from a button on a form from which a date range is taken:

    If IsDate(Me.txtFrom) And IsDate(Me.txtThru) Then
        If Me.txtFrom <= Me.txtThru Then
            strCriteria = "PersonID = " & Me.Parent.cboFindPerson
'            Debug.Print strCriteria
            sRptName = "RptBloodPressureStats"
            DoCmd.OpenReport sRptName, acViewPreview, , strCriteria
            
        Else
            MsgBox " The From date must be prior to the To date.", vbOKOnly
            Me.txtFrom.SetFocus
            Exit Sub
        End If
    Else
        MsgBox "Both the From and To dates are required.", vbOKOnly
        Me.txtFrom.SetFocus
        Exit Sub
    End If

The PrintPreview on Unload event contains the code:

    response = MsgBox("Do you wish to save this report to a PDF file?", vbYesNo, "Save to PDF File")

    If response = vbYes Then
        sRptName = Me.name
        intPersonID = Me.PersonID
        sFileType = ".pdf"
        ' use function to check / samitize, kill duplicate and return path/filename
        sRptPath = fSaveRpt(sRptName, sRptName, intPersonID, sFileType)
       
        sOpenArgs = sRptName & "|" & sRptPath
        Debug.Print sOpenArgs
        
        DoCmd.OpenForm "frmHiddenSaveReport", , , , , acHidden, sOpenArgs
    End If

The frmHiddenSaveReport form has the following code (note different arrangement to @arnelgp 's suggestion) as I was getting the same error 2585 when the Docmd was placed in the Load event of the hidden form - presumably needed to be delayed so that the PrintPreview instance could be closed.

Private Sub Form_Load()

Dim strOpenArgs As String
Dim sRptName As String
Dim sRptPath As String
Dim intPosn As Integer

    strOpenArgs = Me.OpenArgs
    ' parse the OpenArgs string
    
    intPosn = InStr(1, strOpenArgs, "|")
    If intPosn > 1 Then
        sRptName = Left(strOpenArgs, intPosn - 1)
        sRptPath = Mid(strOpenArgs, intPosn + 1)
    End If

    Me.Text1 = sRptName
    Me.Text3 = sRptPath
    
    Me.TimerInterval = 500

End Sub

Private Sub Form_Timer()

Dim sRptName    As String
Dim sRptPath    As String
Dim strOpenArgs As String

    sRptName = Me.Text1
    sRptPath = Me.Text3
    strOpenArgs = Me.OpenArgs
    
    If InStr(1, strOpenArgs, "|") > 1 Then
        DoCmd.OutputTo acOutputReport, sRptName, acFormatPDF, sRptPath
        MsgBox "File saved successfully" & vbCrLf & _
            " -- " & sRptName & " -- ", vbInformation + vbOKOnly
    End If

    ' kill the timer
    Me.TimerInterval = 0
 
    DoCmd.Close acForm, Me.name, acSaveNo
    
End Sub

Thank you again - now works will mark as solved and ready to apply to the many other PrintPreview/Export options.