Re: [MSAccessProfessionals] Filter report before emailing it

I would try use some DAO code to modify the SQL property of a saved query in the record source of the report. This allows you to temporarily use a query filtered by supervisor.

Duane

---

From: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io> on behalf of Doyce Winberry <doyce.winberry@xpo.com>
Sent: Thursday, June 22, 2023 3:59 PM
To: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io>
Subject: [MSAccessProfessionals] Filter report before emailing it

 

Hello everyone,

 

I have an attendance list report that I would like to filter by supervisor and email each supervisor only their pages of the report. I have sub procedure, "SendMailAttachment", that will email the report. It's variables are the report name and recipients. I have setup this VBA code that will loop through the supervisors and pull out their email address. I can also filter the report and print it by supervisor. I just don't know how to filter the report and then email the filtered report to each supervisor. Here is the code:

Public Sub AttendanceList()

    'Email Attendance list to each supervisor

    Dim db As Database

    Dim rst As DAO.Recordset

    Dim lngSupID As Long

    Dim SupEmail As String

      

        Set db = CurrentDb

        Set rst = db.OpenRecordset("tblSupervisorEmail")

        Do Until rst.EOF

            lngSupID = rst![EEID-1]

            SupEmail = rst![Email]

            SendMailAttachment "rptAttendanceSheet-Email", SupEmail

            DoCmd.OpenReport "rptAttendanceSheet-Email", acViewNormal, , "SupID = " & lngSupID

            Debug.Print rst![Email]

            rst.MoveNext

        Loop

        rst.Close

        Set rst = Nothing

        Set db = Nothing

       

End Sub

 

This line will email the complete report to each supervisor   SendMailAttachment "rptAttendanceSheet-Email", SupEmail

This line filters the report and will print each supervisor's report separately, DoCmd.OpenReport "rptAttendanceSheet-Email", acViewNormal, , "SupID = " & lngSupID

 

I need to know how to add the filter to the sendMailAttachment sub procedure.

 

 

Doyce Winberry

Manufacturing

Manager Systems

 

XPO

2001 Benton Street

Searcy, AR 72143 USA

O: +1 501-207-5973   M: +1 501-207-2269