delete report after print preview is closed

[tuxalot]

I have a report that I want to delete once print preview is closed by the user. I tried this:

If stDocRemote = "1" Then        'delete external reports
        Do While CurrentProject.AllReports(stDocName).IsLoaded
        Loop

        DoCmd.DeleteObject acReport, stDocName
    
End If

And this puts me in an endless loop that I must break out of. How can I code this?

Thanks,

Tux

[FishVal]

Hello, Tux.

I recommend you another approach.

'declare object variable to handle report events in the form module
Dim WithEvents rpt As Access.Report

Private Sub Command0_Click()
    
    'make copy of report and open it (illustration purpose only)
    With DoCmd
        .CopyObject NewName:="rptNew", SourceObjectType:=acReport, _
            SourceObjectName:="rpt"
        .OpenReport "rptNew", acViewPreview
    End With
    
    'set object variable to the opened report
    Set rpt = Reports("rptNew")
    'tune its OnClose property
    rpt.OnClose = "[Event Procedure]"
    
End Sub

'now report is closed and could be deleted
Private Sub Form_Timer()
    Me.TimerInterval = 0
    DoCmd.DeleteObject acReport, "rptNew"
End Sub

'report close event
'since report is still open it couldn't be deleted
'event handler sets form timer and yields execution to Access
Private Sub rpt_Close()
    Me.TimerInterval = 1
End Sub

[ADezii]

Hello, Tux.

I recommend you another approach.

'declare object variable to handle report events in the form module
Dim WithEvents rpt As Access.Report

Private Sub Command0_Click()
    
    'make copy of report and open it (illustration purpose only)
    With DoCmd
        .CopyObject NewName:="rptNew", SourceObjectType:=acReport, _
            SourceObjectName:="rpt"
        .OpenReport "rptNew", acViewPreview
    End With
    
    'set object variable to the opened report
    Set rpt = Reports("rptNew")
    'tune its OnClose property
    rpt.OnClose = "[Event Procedure]"
    
End Sub

'now report is closed and could be deleted
Private Sub Form_Timer()
    Me.TimerInterval = 0
    DoCmd.DeleteObject acReport, "rptNew"
End Sub

'report close event
'since report is still open it couldn't be deleted
'event handler sets form timer and yields execution to Access
Private Sub rpt_Close()
    Me.TimerInterval = 1
End Sub

1. First, 2 Assumptions:
   1. The Name of the Form that Opens your Report is Form2.
   2. The Report Name is rptEmployees.
2. Place the following code in the Timer() Event of Form2, and set the Timer Interval = 0.
   

   Private Sub Form_Timer()
   On Error Resume Next
     DoCmd.DeleteObject acReport, "rptEmployees"
     Me.TimerInterval = 0            'Disable
   End Sub

3. Assuming you only want to Delete the Report after it is Open in 'Preview' Mode, open the Report from your Form and pass to it an OpenArgs = 'Preview'.
   

   DoCmd.OpenReport "rptEmployees", acViewPreview, , , acWindowNormal, "Preview"

4. Place the following code in the Close() Event of the Report:
   

   Private Sub Report_Close()
   If Me.OpenArgs = "Preview" Then
     'Wait 2 seconds after closing
     Forms!Form2.TimerInterval = 2000
   End If
   End Sub

5. Code has been tested and is fully operational.

[tuxalot]

Thanks for the replies.

ADezii - if possible I would prefer not to put code in the reports themselves, and was looking for a solution that could be coded from my main form.

FishVal - let me see if I understand. The code you provided makes a copy of the report, after which time it deletes the copy? I'm not sure this will give me what I need so allow me to explain. The reports that I wish to delete are those that are transferred from a linked db via DoCmd.TransferD atabase. Please help me to understand your code and if it would apply in my situation.

Thanks again,

Tux

[FishVal]

FishVal - let me see if I understand. The code you provided makes a copy of the report, after which time it deletes the copy? I'm not sure this will give me what I need so allow me to explain. The reports that I wish to delete are those that are transferred from a linked db via DoCmd.TransferD atabase. Please help me to understand your code and if it would apply in my situation.

Report copying in the example I've posted is for illustration purposes only.
The only reason for it was to have a renewable copy of a report to delete while testing the code.
You definitely don't need this part. The rest will work for you, just replace "rptNew" report name with whatever you need.

The logic of the code is as follows:

• Open report.
• Set a form module global object variable declared with events to opened report object.
• Thus the report events could be handled in the form module.
• Once the report object has triggered Close event, event handling code in the form module (sub rpt_Close) is being called. At this point the report could not be deleted because the report is not yet closed. Therefore, instead of delete the report, code sets form timer and returns execution flow back to Access which closes the report and then triggers Form_Timer event sub where the report is actually deleted.
• Timer interval value is not important because VBA execution is not asynchronous. That means no event will be triggered until Access perform all actions necessary for report closing. So, in the example timer interval is set to a least possible value - 1 ms. Like saying "one moment", though, actually, it may take thousand "moments".

[tuxalot]

Thanks for the explanation FishVal. I'll give your code a go and see what happens.

Tux

[ADezii]

Thanks for the replies.

ADezii - if possible I would prefer not to put code in the reports themselves, and was looking for a solution that could be coded from my main form.

FishVal - let me see if I understand. The code you provided makes a copy of the report, after which time it deletes the copy? I'm not sure this will give me what I need so allow me to explain. The reports that I wish to delete are those that are transferred from a linked db via DoCmd.TransferD atabase. Please help me to understand your code and if it would apply in my situation.

Thanks again,

Tux

Why not simply Open the Report in the External Database from the Current Database? Wouldn't that solve all your problems?

[tuxalot]

Yes! However the queries for the reports reside in my FE db. The StateReports db only houses the reports themselves.

[ADezii]

Yes! However the queries for the reports reside in my FE db. The StateReports db only houses the reports themselves.

Hello tuxalot, IMHO I do not think that the current logic is sound in that:
You are Importing Reports from an External Database whose Record Sources reside locally. After Previewing these Reports, you are then Deleting them.

[tuxalot]

I know it sounds strange but...
The reports reside externally because the main db may roll out before the state reports db. The state reports db may update several times each year, so I need an easy way to update the state reports without affecting the FE or BE. The queries for the State Reports reside locally because some local reports use the same queries.

At max, if all external reports were transferred and the main db saved, the added bloat may approach 15mb (each report uses a pdf as it's background and they average 3mb each report). So we're not talking about a significant amount I just figured if I could tidy things up it would be better.

I suppose another solution may be to perform the deletion on db shut-down, or every 24 hours if the user keeps their pc running.