Renaming a pdf. report in email

[Vol71]

I am trying to email a report and rename the report after the referenced NCR# currently selected on my form. My codes works for putting the referenced NCR# in my email subject, but I am having issues renaming the report and the error is with the SetProperty line bolded below. When I click my email button, I get Run-time error 32004: The contronl name "Supplier Chargebacks" is mispelled or refers to a control that doesen't exists.

Thanks

Private Sub Command587_Click()

Dim stReport As String
Dim stWhere As String
Dim stSubject As String
Dim NCRNum As String

NCRNum = Forms![NCR Input Form]![NCR #]
stSubject = "Supplier Chargebacks NCR " & NCRNum
stReport = "Supplier Chargebacks"
stWhere = "[NCR #] = " & "'" & NCRNum & "'"



DoCmd.OpenReport stReport, acViewPreview, "", stWhere, acWindowNormal, ""
DoCmd.SetProperty stReport, acPropertyCaption, "Supplier Chargebacks NCR " & NCRNum
DoCmd.SendObject acSendReport, stReport, "PDFFormat(*.pdf)", , , , stSubject, , True, ""

DoCmd.Close acReport, stReport
DoCmd.SendObject acSendReport

End Sub

[ranman256]

You can set the caption before you send this way...

    Dim rpt
    Dim sRpt As String
    
    sRpt = "rReport1"
    DoCmd.OpenReport sRpt, acViewDesign
       Set rpt = Reports(sRpt)
       rpt.Caption = "Supplier Chargebacks NCR " & NCRNum


       'rpt.RecordSource = pvQry
       'rpt.OrderByOn = True
       'rpt.OrderBy = sFld
    DoCmd.Close acReport, rpt.Name, acSaveYes
    DoCmd.SendObject acSendReport, stReport, "PDFFormat(*.pdf)", , , , stSubject, , True, ""

[Vol71]

Thanks, I was able to get the report pdf. to be rename and attach to email, but now my where condition no longer works on the OpenReport. It prints all reports together in one pdf. and doesn't filter them by NCR #. What am I doing wrong? Thanks

[Vol71]

Thanks, I was able to get the report pdf. to be rename and attach to email, but now my where condition no longer works on the OpenReport. It prints all reports together in one pdf. and doesn't filter them by NCR #. What am I doing wrong? Thanks

Private Sub Command587_Click()
Dim stReport As String
Dim stWhere As String
Dim stSubject As String
Dim NCRNum As String

NCRNum = Forms![NCR Input Form]![NCR #]
stSubject = "Supplier Chargebacks NCR " & NCRNum
stReport = "Supplier Chargebacks"
stWhere = "[NCR #] = " & "'" & NCRNum & "'"
Dim rpt
Dim sRpt As String

sRpt = "Supplier Chargebacks"
DoCmd.OpenReport sRpt, acViewDesign, , stReport
Set rpt = Reports(sRpt)
rpt.Caption = "Supplier Chargebacks NCR " & NCRNum

'rpt.RecordSource = pvQry
'rpt.OrderByOn = True
'rpt.OrderBy = sFld
DoCmd.Close acReport, rpt.Name, acSaveYes
DoCmd.SendObject acSendReport, stReport, "PDFFormat(*.pdf)", , , , stSubject, , True, ""


DoCmd.Close acReport, stReport
DoCmd.SendObject acSendReport

[ranman256]

the QUERY behind the report does the filtering. Usu from a form where you picked an NCR.

query criteria: where [ncr] = forms!frmRpt!cboNCR

[June7]

Options:

1. as ranman suggests - report RecordSource is a dynamic parameterized query that refers to input control on a form for filter (I never use dynamic parameterized queries)

2. open filtered report in PrintPreview and SendObject will then act on the open report object instead of from the navigation pane