Module to run queries based on a parameter from looping through a recordset and emailing a report

[craig1988]

Hi there

I have tried to accomplish this a while ago with no luck. Since then my access skills have improved and I am ready to tackle it again.

I have an ever changing number of Staff that are identified from their StaffID in a table called TStaffList. I would like to run query for all the StaffID's (actually a report based on that query). I need it to loop through the StaffID's and use the StaffID as a parameter.

The query is a SELECT query from a table called TErrorLog where I select all Errors commited by a staff member between certain dates. I am using a Form called FWeeklyReport to pass the StartDate and EndDate to the Query

Between [Forms]![FWeeklyReport]![StartDate] And [Forms]![FWeeklyReport]![StartDate]

Upon generating the report (based on the query above) I would like it to be emailed to the person who it belongs to (the StaffID that was used during the recordset). In the TstaffList I have a column called EMail that contains the e-mail address of that person.

I am not confident enough to tackle the recordsets so I would like some help with them (I presume I have to open two recordsets, one for StaffID and one for Email). Also, I need to know how exactly to build the select query.

Thanks in advance.
Craig

[ranman256]

in a form, put a listbox on the form of your Staff called lstStaff: StaffID, Name, email
another list box for report. user can select the report to send. lstRpts
the report would have the lstStaff reference of the person
then click the email button to start the scan

Dim vRpt, vName, vTo

vRpt = lstRpts
For i = 0 To lstStaff.ListCount - 1
   lstStaff = lstStaff.ItemData(i)
   vName = lstStaff.Column(1)
   vTo = lstStaff.Column(2)
   
   DoCmd.SendObject acSendReport, vRpt, acFormatPDF, vTo, , , "Subject", "body"
Next

Hi there

I have tried to accomplish this a while ago with no luck. Since then my access skills have improved and I am ready to tackle it again.

I have an ever changing number of Staff that are identified from their StaffID in a table called TStaffList. I would like to run query for all the StaffID's (actually a report based on that query). I need it to loop through the StaffID's and use the StaffID as a parameter.

The query is a SELECT query from a table called TErrorLog where I select all Errors commited by a staff member between certain dates. I am using a Form called FWeeklyReport to pass the StartDate and EndDate to the Query

Between [Forms]![FWeeklyReport]![StartDate] And [Forms]![FWeeklyReport]![StartDate]

Upon generating the report (based on the query above) I would like it to be emailed to the person who it belongs to (the StaffID that was used during the recordset). In the TstaffList I have a column called EMail that contains the e-mail address of that person.

I am not confident enough to tackle the recordsets so I would like some help with them (I presume I have to open two recordsets, one for StaffID and one for Email). Also, I need to know how exactly to build the select query.

Thanks in advance.
Craig

[craig1988]

Hi Ranman

I am a little confused. I assumed I would have to open up recordsets... Why do I need the two list boxes? I want to run this all with one click of the command button.

Do I need to create a report called lstReport or will it be created when I click the button?

Thanks
Craig