Email single record report (1 Viewer)

[Sneale]

Hello,

I have a report that needs to be emailed to specific email addresses for each record as the table is updated.

I have 2 tables, one called tblAuditInfo and one called tblSupplier. I have an input form called frmAuditDataEntry. I also have a report called Receiving Audit.

My goal is to have the user enter data to the form, run an update query to update the supplier name from tblSupplier to tblAuditinfo, then have the form sent via email only for the current record.

I have tried the macro EmailDatabaseObject, but it sends all records. is anyone able to assist with this?

Thanks in advance

Steve

 

[bob fitz]

Hello,

I have a report that needs to be emailed to specific email addresses for each record as the table is updated.

I have 2 tables, one called tblAuditInfo and one called tblSupplier. I have an input form called frmAuditDataEntry. I also have a report called Receiving Audit.

My goal is to have the user enter data to the form, run an update query to update the supplier name from tblSupplier to tblAuditinfo, then have the form sent via email only for the current record.

I have tried the macro EmailDatabaseObject, but it sends all records. is anyone able to assist with this?

Thanks in advance

Steve

Perhaps set the criteria in the query for the report to the SupplierID value that is on the form.
There's id a "Builder" wizard button that will build the required expression for you when in Design view of the query.

 

[Pat Hartman]

Unlike the OpenReport method which provides arguments used to filter the data for the report, the SendObject does not. Therefore, as Bob mentioned, YOU need to have the report filter itself. The Where clause for the report needs to be something like:

Where SomeID = Forms!myform!myID

If you ever have to run the report from multiple places making the form reference not viable, then you can create a TempVar and populate the tempvar. Then use that instead of a form reference in the where clause.

 

[Sneale]

Below is the code i have and it seems to work well. Thank you for the help!

Private Sub Command116_Click()

 Const MESSAGETEXT = "No current record."
    

If Not IsNull(Me.ID) Then
    Me.Dirty = False
    DoCmd.OpenQuery "qryUpdateSupplierName", acViewNormal
    DoCmd.OpenReport "ReceivingAudit", acViewPreview, , "ID = " & ID
    DoCmd.SendObject acSendReport, "ReceivingAudit", acFormatPDF, "myemail@email.com", , , "Receiving Audit " & Dte, "Please see attached Receiving Audit.", True

    
       DoCmd.Close
            DoCmd.GoToRecord , , acNewRec
  
  Else
        MsgBox MESSAGETEXT, vbExclamation, "Invalid Operation"
      
    End If
    
    
    
End Sub

 

[Pat Hartman]

Did you modify the RecordSource of the report? That is what you have to do in order to provide selection criteria.