Email Current Record as PDF (1 Viewer)

[andybuck86]

Hello,

I have a button on my form that opens a report and emails:

Private Sub Submit_Click()
DoCmd.SendObject acReport, "rptFM83", "PDFFormat(*.pdf)", "exampleemailaddress", "", "", "Agency Authorisation Required", "Hello, Please authorise the attached agency request. Thank you", True, ""
 
End Sub

How do I add a filter so that only the current record on the form is emailed?

I have searched the forum but can't seem to find the answer I need.

Thanks a lot for any help

 

[AccessMSSQL]

Before using the Docmd.SendObject to send a report you will have to modify the reports recordsource dynamically. You can't pass a WHERE statement to the report via SendObject. This simply will send the entire report.

So what you need to do is:
1. Make sure the recordsource of your report is the name of a query that does a simple SELECT statement from one table or one query. If your rmain report recordsource has many joins, in order to make it easier to dynamically change the WHERE clause you should save the statement as it's own query with no WHERE clause in it. Create a 2nd query based on your main query that does a simple SELECT * from qryReportMain. This might be saved as qryReportMainSelect.

2. Before you run your SendObject code, modfiy the query used behind the report dynamically from VBA:

Dim qry as dao.querydef
Dim strSQL as string
Dim ReportQueryName as string
ReportQueryName = "qryReportMainSelect"
set qry = currentdb.querydefs(ReportQueryName)
strSQL = "SELECT * FROM qryReportMain WHERE ID = " & YourRecordID
qry.SQL = strSQL

qryReportMain would be your main select statement for the report with no where clause
qryReportMainSelect would just be a simple select from qryReportMain with the WHERE clause
Your report recordsource should be qryReportMainSelect.

Hope this makes sense!

 

[andybuck86]

Hi Cheryl,

Thank you very much for the swift response.

I don't seem to be able to get this working, I'm a bit of a noob sorry!... maybe a sample of my db would help. Please find attached - Access 2010 format

My button is on the frmAgencyRequest - called 'Submit'

Thanks again



Andy

 

[AccessMSSQL]

You are close.

Change your code to this:

ReportQueryName = "qryrptFM83"
Set qry = CurrentDb.QueryDefs(ReportQueryName)
[COLOR=red]strSQL = "SELECT * FROM tblAgencyRequests WHERE AgencyRequestID = " & Me.AgencyRequestID[/COLOR]
qry.SQL = strSQL

You didn't change my sample query names and fieldIDs. I see your report is fairly simple, it only selects from one table, so no need to create the report main query. This should work for you.

Just a note, if you are printing from a newly created record make sure you use me.requery before you run the report.
You should put that code before your sendObject statement otherwise the report will print with no record in it.

 

[pbaldy]

This would be another option:

http://www.granite.ab.ca/access/email/reporttomultiplerecipients.htm

 

[andybuck86]

Ahh that's working now thank you.

I did change the field and query names originally but didn't reference them correctly as you have done... Did I mention I'm a noob

Thanks again for your help

 

[michelecobb]

I am new to Access. I am using Access 2010. I have searched and found several articles on emailing the current record shown on a form but haven't been able to get any of them to work.

My goal is to have a user input information onto the form and then have one button the user will click that will save the current record, email the current record in pdf, and then close the form.

The form name is: frmCorrectiveActions. The unique identifier for a record is: Corrective Action ID.

I tried the coding in this post but I am getting the message Run-time error '3075': Syntax error (missing operator) in query expression "Corrective Actions ID = 23' and when I hit debug this is highlighted: qry.SQL = strSQL

form Private Sub Email_Click()
Dim qry As DAO.QueryDef
Dim strSQL As String
Dim ReportQueryName As String
ReportQueryName = "qryCorrectiveActions"
Set qry = CurrentDb.QueryDefs(ReportQueryName)
strSQL = "SELECT * FROM tblCorrectiveActions WHERE Corrective Actions ID = " & Me.Corrective_Action_ID
qry.SQL = strSQL
DoCmd.Requery [qryCorrectiveActions]
DoCmd.SendObject acSendForm, frmCorrectiveActions, acFormatPDF, "variable", , , _
"Corrective Actions", "Attached please find Corrective Actions notification"

End Sub

It was suggested to bracket the field name as shown below.

strSQL = "SELECT * FROM tblCorrectiveActions WHERE [Corrective Actions ID] = " & Me.Corrective_Action_ID

After I bracketed the field, I received message Run-time error '2465': MS Access can't find the field '|1' referred to in your expression. When I hit debug DoCmd.Requery [qryCorrectiveActions] is highlighted.

Any help would be greatly appreciated!

 

[pbaldy]

Please don't post the same question twice. I've answered in your other thread.