Results 1 to 3 of 3
  1. #1
    craig1988 is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Aug 2014
    Posts
    82

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

    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
    Code:
    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

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    8,201
    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

    Code:
    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





    Quote Originally Posted by craig1988 View Post
    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
    Code:
    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

  3. #3
    craig1988 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Aug 2014
    Posts
    82
    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

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 05-06-2014, 11:38 PM
  2. emailing a report based on a combobox selection
    By ecalvert47462 in forum Access
    Replies: 9
    Last Post: 12-11-2013, 12:52 PM
  3. looping through recordset (columnwise)
    By pradeep.sands in forum Queries
    Replies: 1
    Last Post: 06-27-2013, 09:46 AM
  4. Replies: 2
    Last Post: 11-19-2012, 05:42 PM
  5. A RecordSet based on a Union Queries
    By Paul H in forum Programming
    Replies: 3
    Last Post: 05-31-2012, 02:05 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums - Senior Forums