Close all reports with VBA

[puniverse]

Can Someone please help I am trying to close the report whether or not the email is sent. This is the code I currently have. The reason I need to close it is because the next one that I do for somereason only changes the report name but keeps the existing data causing the pdfs saved to be the same with a different day. Even when attached in email they are the same and I must close the report and then have the button reopen in order for the data to change.

Private Sub cmdEmailInvoice_Click()
On Error GoTo cmdEmail_Click_Err
DoCmd.OpenReport "Invoice", acViewPreview, , "[ProjectID]=" & [ID]
'Save report to local drive
Dim strFile As String
strFile = "C:\Invoices\Invoice_" & "#" & [ID] & "_" & Format(Now, "MM-DD-YYYY") & ".pdf"
DoCmd.OutputTo acOutputReport, "Invoice", acFormatPDF, strFile
Dim stReport As String
    Dim stWhere As String
    Dim stEmail As String
    Dim stSubject As String
    Dim stEmailMessage As String
    Dim ReportCap As String
    
    stEmailMessage = "" & vbCrLf & vbCrLf & "" & vbCrLf & ""   'Email Body
    stSubject = "Invoice" 'Email Subject
    stReport = "Invoice" 'Original name of the Report
    ReportCap = "Invoice"    'Current Report Caption
    stEmail = "[EMAIL="e-mail@e-mail.xx"]e-mail@e-mail.xx[/EMAIL]"    'E-mail adresses or (Me.E-mail fieldname)
    'DoCmd.OpenReport stReport, acViewPreview, "", stWhere, acWindowNormal, ""   'Opens the report
    Reports!Invoice.Caption = "Invoice_#" & [ID]    'Renames the Report
    DoCmd.SendObject acSendReport, stReport, "PDFFormat(*.pdf)", stEmail, , , stSubject, stEmailMessage, True, "" 'Send out the E-mail
    DoCmd.Close acReport, stReport, acSaveNo
cmdEmail_Click_Exit:
Exit Sub
cmdEmail_Click_Err:
   Select Case Err.Number
      Case 2501
      MsgBox "Email Canceled by user.", vbInformation
  Case Else
      MsgBox "Error " & Err.Number & " " & Err.Description
 End Select
    Resume cmdEmail_Click_Exit
End Sub

 

[JHB]

Look at the DoCmd.Close in the Help file.

 

[puniverse]

I did.... this is what I found

DoCmd.Close acReport, " Report name"

But this will not work because I am changing the report name to add the [ID]. If the report name always stayed the same then I would not have a problem. So I was looking for a VBA code that would close all reports no matter what the name is.

 

[pr2-eugin]

I do not understand.. You have not changed the name of the report anywhere in the code you have.. You only have changed the caption of the report..

If you do not want to make the changes you always can use the acSaveNo option to abandon all changes..

PS: Could you Please use CODE tags when posting VBA Code?

 

[puniverse]

You say it is the caption name but when I use the

DoCmd.Close acreport, "invoice".

It does not close the report...

I am adding it under the send object... Any ideas?

 

[pr2-eugin]

So is this how your code looks like now?

Private Sub [B]codeNeedsToBeIndented[/B]()
    Dim strFile As String
    Dim stReport As String
    Dim stWhere As String
    Dim stEmail As String
    Dim stSubject As String
    Dim stEmailMessage As String
    Dim ReportCap As String
    
    strFile = "C:\Invoices\Invoice_" & "#" & [ID] & "_" & Format(Date, "MM-DD-YYYY") & ".pdf"
    
    DoCmd.OutputTo acOutputReport, "Invoice", acFormatPDF, strFile

    stEmailMessage = "" & vbCrLf & vbCrLf & "" & vbCrLf & ""         [COLOR=Green]'Email Body[/COLOR]
    stSubject = "Invoice"                                            [COLOR=Green] 'Email Subject[/COLOR]
    stReport = "Invoice"                                             [COLOR=Green]'Original name of the Report[/COLOR]
    ReportCap = "Invoice"                                            [COLOR=Green] 'Current Report Caption[/COLOR]
    stEmail = "e-mail@e-mail.xx"                                     [COLOR=Green]'E-mail adresses or (Me.E-mail fieldname)[/COLOR]
    
    [COLOR=Green]'Opens the report[/COLOR]
    DoCmd.OpenReport stReport, acViewPreview, "", stWhere, acWindowNormal, "" 
    Reports!Invoice.Caption = "Invoice_#" & [ID]                     [COLOR=Green]'Change Caption[/COLOR]
   [COLOR=Green] 'Send out the E-mail[/COLOR]
    DoCmd.SendObject acSendReport, stReport, "PDFFormat(*.pdf)", stEmail, , , stSubject, stEmailMessage, True, "" 
    DoCmd.Close acReport, stReport, [COLOR=Green][COLOR=Black][B]acSaveNo[/B][/COLOR]
[/COLOR]cmdEmail_Click_Exit:
    Exit Sub
cmdEmail_Click_Err:
    Select Case Err.Number
        Case 2501
            MsgBox "Email Canceled by user.", vbInformation
        Case Else
            MsgBox "Error " & Err.Number & " " & Err.Description
    End Select
    Resume cmdEmail_Click_Exit
End Sub

 

[puniverse]

yes but it does not close the report. I tried many different ways and still not closing report. I edited my first post with the code and code brackets.

 

[puniverse]

Re: [SOLVED] Close all reports with VBA

I figured it out. For some odd reason you cannot close the report after I have used the sendobject to email. The fix is to use the "ON Deactivate" feature on the report itself which will close the report when you click on the form. Thanks for the help guys.