Changing name of report

[Arno]

I want to email a report as a PDF-file using a button on a form.
This works fine. The report is created, converted to a PDF , attached to the mail, etc.
But....I want to change the name of the report that is emailed.

For this I use following code for the on open event of the report:

Private Sub Report_Open(Cancel As Integer)
Me.Caption = "Blokkeringsnummer " & "Versie "
End Sub

This works fine, but I want to include the values of the fields [Blokkeringsnummer] and [Versie] in the name of the report. These fields are also on the report, and the report reports only 1 record.

I tried this code:

Private Sub Report_Open(Cancel As Integer)
Me.Caption = "Blokkeringsnummer " & Me!Blokkeringsnummer & "Versie " & Me!Versie
End Sub

and this code:

Private Sub Report_Open(Cancel As Integer)
Me.Caption = "Blokkeringsnummer " & Me![Blokkeringsnummer] & "Versie " & Me![Versie]
End Sub

In both cases I get an error 2427

What am I doing wrong?

 

[JANR]

I think the Open event is too early, try putting the code in the Load Event

JR

 

[Arno]

I think the Open event is too early, try putting the code in the Load Event

JR

Thanks !!

This works fine!

Could you tell me the exact difference between the Open and Load event?
Is the Load event the moment that the data from the table are loaded into the report?

I have a additional question.
I have to put this code into a lot of different reports.
Is it possible to program this action tot the same single button that selects the record, selects th eright report, opens the report and sends the mail ?

 

[JANR]

Is the Load event the moment that the data from the table are loaded into the report?

Yes.

Is it possible to program this action tot the same single button that selects the record, selects th eright report, opens the report and sends the mail ?

Yes. Create a custom sub, function or macro that perform these actions in sequence. Basically anything is possible, it's the how that the challenge.

Write down your sequence and and take on each action seperatly, when each step work on it's own it is easy to run the hole procedure in one go.

JR

 

[Arno]

Yes. Create a custom sub, function or macro that perform these actions in sequence. Basically anything is possible, it's the how that the challenge.

Write down your sequence and and take on each action seperatly, when each step work on it's own it is easy to run the hole procedure in one go.

JR

My question was not clear.
I already have al these actions coded to this one button, and they all work.
The only action that is not coded to and performed by clicking this button, is the renaming of the report.

Could you help me on my way?

Private Sub Knop49_Click()
On Error GoTo Err_Knop49_Click
    Dim stDocName As String
    Dim stLinkCriteria As String
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim strTo As String
 
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("Tbl-Emailadressen " & Me![Afdeling], dbOpenForwardOnly)
    Do While Not rst.EOF
    strTo = strTo & rst!Email & ";"
    rst.MoveNext
    Loop
 
    stDocName = "Rpt-Blokkering " & Me![Afdeling]
    stLinkCriteria = "[Blokkeringsnummer]=" & "'" & Me![Blokkeringsnummer] & "'"
    DoCmd.OpenReport stDocName, acPreview, , stLinkCriteria
    DoCmd.SendObject acSendReport, stDocName, acFormatPDF, strTo, , , "Blokkering " & Me![Blokkeringsnummer] & " Versie " & Me![Versie] & " is gemaakt."
Exit_Knop49_Click:
    Exit Sub
Err_Knop49_Click:
    MsgBox Err.Description
    Resume Exit_Knop49_Click
 
End Sub

 

[JANR]

First, you don't have to open a report before you send it.

In you code for your report you change the caption of the report, that is ok to printout or view, but the only thing that is included in the MessageText of the action SendObject are textboxes, labels and subreports in the detailsection of the report according to Access help. Header and Footer are ignored. So i don't think you can use the caption property the way you want.

You could perhaps put the reportname in an unbound textbox on your report and set it's value in the open event of the report.

Since your form is open you can refrence it at runtime.

Private Sub Report_OnLoad()
Me.txtReportname =  "Blokkeringsnummer " & Forms!NameOfForm!Blokkeringsnummer & "Versie " & Forms!NameOfForm!Versie
End Sub

Just thinking outloud, it's been a while since I'v used Send object, perhaps others might step in with some ideas.

JR

 

[Arno]

First, you don't have to open a report before you send it.

In you code for your report you change the caption of the report, that is ok to printout or view, but the only thing that is included in the MessageText of the action SendObject are textboxes, labels and subreports in the detailsection of the report according to Access help. Header and Footer are ignored. So i don't think you can use the caption property the way you want.

You could perhaps put the reportname in an unbound textbox on your report and set it's value in the open event of the report.

Since your form is open you can refrence it at runtime.

Private Sub Report_OnLoad()
Me.txtReportname =  "Blokkeringsnummer " & Forms!NameOfForm!Blokkeringsnummer & "Versie " & Forms!NameOfForm!Versie
End Sub

Just thinking outloud, it's been a while since I'v used Send object, perhaps others might step in with some ideas.

JR

Thank you for thinking out loud.
I do not need anything in the emailbody or in the detailsection of the report or in the header or footer, I just wanted that the pdf-file that is created and attached to the email renamed tot "Blokkeringsnummer xxx Versie x".
With your help, I succeeded. Thanks!

I do need to open the report to activate the stLinkCriteria, otherwise the report will contain all records instead of just the selected one.

 

[JANR]

If I understand you correctly, you have 1 report named Rpt-Blokkering?
and you use this report to send different records to different departments.

If so there are 2 metodes I can think of, one is easy BUT has Big drawbacks.

- Bloating of db
- Can't use it in a mde/accde

Create a copy of your report which you can name as you wish and delete it when your done with it.

Metode 2:
Use the report and output it to a file on your disk and use a mailclient ex Outlook, to send the file as an attachment.

- You can't use DoCmd.SendObject
- Saves you from bloating and it can be used in mde/accde
- require some extra code to use the MailClient.

ex 1:

Private Sub Knop49_Click()
On Error GoTo Err_Knop49_Click
    Dim stDocName As String
    Dim stLinkCriteria As String
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim strTo As String
    [COLOR=red]Dim strSubject As String
[/COLOR]    
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("Tbl-Emailadressen " & Me![Afdeling], dbOpenForwardOnly)
    Do While Not rst.EOF
    strTo = strTo & rst!Email & ";"
    rst.MoveNext
    Loop
 
    [COLOR=red]strSubject = "Blokkering " & Me![Blokkeringsnummer] & " Versie " & Me![Versie] & " is gemaakt."
[/COLOR]    stLinkCriteria = "[Blokkeringsnummer]=" & "'" & Me![Blokkeringsnummer] & "'"
    
   [COLOR=red] DoCmd.CopyObject , strSubject, acReport, "rpt-Blokkering"
[/COLOR]    DoCmd.OpenReport strSubject, , , stLinkCriteria, [COLOR=red]acHidden
[/COLOR]    DoCmd.SendObject acSendReport, , acFormatPDF, strTo, , , [COLOR=red]strSubject
[/COLOR]    [COLOR=red]DoCmd.Close acReport, strSubject, acSaveYes
[/COLOR]    [COLOR=red]DoCmd.DeleteObject acReport, strSubject
[/COLOR]    
Exit_Knop49_Click:
    Exit Sub
Err_Knop49_Click:
    MsgBox Err.Description
    Resume Exit_Knop49_Click
 
End Sub

* marked in red are added parts to your code

ex 2:

Private Sub Knop49_Click()
On Error GoTo Err_Knop49_Click
    Dim stDocName As String
    Dim stLinkCriteria As String
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim strTo As String
   [COLOR=red] Dim strFile As String
    Dim strSubject As String
[/COLOR]    
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("Tbl-Emailadressen " & Me![Afdeling], dbOpenForwardOnly)
    Do While Not rst.EOF
    strTo = strTo & rst!Email & ";"
    rst.MoveNext
    Loop
    
    [COLOR=red]strFile = CurrentProject.Path & "\" & "Blokkering " & Me![Blokkeringsnummer] & " Versie " _
              & Me![Versie] & " is gemaakt" & ".pdf"
[/COLOR]    stLinkCriteria = "[Blokkeringsnummer]=" & "'" & Me![Blokkeringsnummer] & "'"
    [COLOR=red]strSubject = "Blokkering " & Me![Blokkeringsnummer] & " Versie " & Me![Versie] & " is gemaakt."[/COLOR]
    [COLOR=red]DoCmd.OpenReport "Rpt-Blokkering", , , strlinkcriteria, acHidden
    DoCmd.OutputTo acOutputReport, , acFormatPDF, strFile
    DoCmd.Close acReport, "rpt-Blokkering", acSaveNo
    SendOutlookMail strTo, strSubject, strFile
[/COLOR]    
Exit_Knop49_Click:
    Exit Sub
Err_Knop49_Click:
    MsgBox Err.Description
    Resume Exit_Knop49_Click
 
End Sub

* Marked red are added parts.

extra code to use Outlook as a MailClient. Paste this in a StandardModule.

Function SendOutlookMail(strTo As String, strSubject As String, Optional Attached As Variant)
Dim olApp As Object
Dim olMail As Object
Const olMailItem = 0
 
On Error Resume Next
Set olApp = GetObject(, "Outlook.Application")
 
If Err.Number = 429 Then
     Err.Clear
      'Outlook is not running; open Outlook with CreateObject
      Set olApp = CreateObject("Outlook.Application")
End If
 
Set olMail = olApp.CreateItem(olMailItem)
With olMail
    .To = strTo
    .subject = strSubject
        If Not IsMissing(Attached) Then
            .Attachments.Add Attached
        End If
    .Display
End With
 
Set olMail = Nothing
Set olApp = Nothing
End Function

Hope this can give you some ideas.

JR

 

[JANR]

Obviously a good night sleep is whats the brain needs, the 3. option.

DoCmd.Rename "My new reportname", acReport, "MyOldName"

But this falls under the same restrictions as option 1, and as a sidenote it is not wise to constantly rename or change objects through code other than once in-a-blue-moon. If something goes wrong then other parts of your application which relays on this object woulden't find it and you'll get a runtime error.

JR

 

[Arno]

Thanks for your help.
I will consider your solutions, but for now I stay with on-load option.