Re: Need help with looping through records to email report

Hi Dale,

Thanks for your help!

I made the changes as you suggest. (My test table has two
employees.) I get the reports on screen, both at the same time and an
error message "Object required". Nothing gets emailed. I had a
problem with this line from your response item #2 below:


if Reset OF isempty(myUserID) then myUserID = Null
The code window highlights OF and then gives a message of Compile
Error, expected Then or GoTo. I am not sure what OF is referring to?

Any suggestions?

Julie
On Dec 22, 2:22 pm, Dale Fye <dale....@xxxxxxxxxx> wrote:
Julie,

1.  First thing I would do differently is modify your first SQL string, so
that it selects DISTINCT [Name] and [email] addresses from your table.  
Assuming that the report returns all of the records pretaining to this
individual, and not just one at a time, this is probably the only step that
you really need to do.  The rest is just how I handle these types of
automated reporting tasks.

strSQL = "SELECT DISTINCT [Name], [Email] FROM [2008MicroProcReviewTest]

2.  Create a function fnUserID() that looks like the following.  Put this in
a standard code module (not a forms code module).  I used to just put this
value in a hidden textbox on a form, and refer to that textbox in the query,
but found that using this technique, I can call the report from anywhere in
my code, or from any form, or even from the immediate window.

Public Function fnUserID(Optional SomeValue as Variant = Null, _
                                    Optional Reset as boolean = false) as
Variant

    Static myUserID as Variant

    if Reset OF isempty(myUserID) then myUserID = Null
    if not isnull(SomeValue) then myUserID = SomeValue

    fnUserID = myUserID

End function

3.  Modify the query that you use for your report to look something like:

SELECT * FROM ....
WHERE UserID = fnUserID()
OR fnUserID() IS NULL

4.  Then, inside the loop (Do While not rst.eof: Wend), I would set fnUserID
with a line of code that looks like:

    Call fnUserID(rs.Fields("Name"))

5.  Since the reports underlying query looks at fnUserID, you can delete the
call to FilterReport

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.



"jag2004" wrote:
Hello,

I have been working on a problem in my spare time for a few weeks now,
I have finally made some progress but I need some additional help.

I work in a hospital laboratory where we are required to keep track of
procedures read, continuing education seminars attended, etc.  The
hospital has a webform program which employees use to enter the
information.  There is a separate form for each type of item we are
tracking.  I am responsible for pulling the information off of the
hospital system and distributing it to the employees.  I created an
Access database to do this.
I have a table called 2008MicroProcReviewtest. It has the following
fields:
ID (primary key, autonumber), Name, Procedure, DateRead, Email
I have a report called rpt2008ProcedureReview

I created a form with a command button and I found code on the web
that will pick the employee name and email address, create the report
and email it to each employee.  The problem is that if an employee has
15 records, it will create the report and then email it 15 times.  I
need help with my SQL statement or with the looping to figure out how
to get it to see the employees 15 records as 1 report, then move to
the next employee.

I will post the code below:
This is the command button code.  There is also a function called
FilterReport and one called SendReportByEmail, they are pasted below
the command button code.  The functions are located in modules.

Any help would be GREATLY APPRECIATED!!!

Thanks,
Julie

Private Sub Command0_Click()
'Private Sub cmdSendReport_Click()
On Error GoTo PROC_ERR

' Declare variables
 Dim dbs As DAO.Database
 Dim rst As DAO.Recordset
 Dim strAcountStatus As String
 Dim strEmail As String
 Dim strUserID As String
 Dim fOk As Boolean

 ' Build our SQL string
 strSQL = "SELECT Name, Email From [2008MicroProcReviewtest]"

 ' Set our database and recordset objects
 Set dbs = CurrentDb
 Set rst = dbs.OpenRecordset(strSQL)

 ' Open the report
 DoCmd.OpenReport "rpt2008ProcedureReview", acPreview

 'Turn the filter on
 Reports![rpt2008ProcedureReview].FilterOn = True

  ' Loop the recordset
 Do While Not rst.EOF

 ' Grab the Email string
 strEmail = rst.Fields("email")

 ' Grab the UserID string
 strUserID = rst.Fields("name")

 ' Call the procedure used to filter the report based on the
 'Current employee
 Call FilterReport("rpt2008ProcedureReview", strUserID)

 ' Allow the report to refresh after filtering
 DoEvents

 ' Send the snapshot of the report to the current employee
 fOk = SendReportByEmail("rpt2008ProcedureReview", strEmail)

 ' Display message if failure
 If Not fOk Then
    MsgBox "Delivery Failure to the following email address: " &
strEmail
 End If

 ' Move and loop
 rst.MoveNext
 Loop

 ' Clean up
 rst.Close
 Set rst = Nothing
 dbs.Close
 Set dbs = Nothing

PROC_EXIT:
     Exit Sub

PROC_ERR:
     MsgBox Err.Description
     Resume PROC_EXIT
 End Sub

***Filter report function****

Public Sub FilterReport(strReportName As String, strUserID As String)
    ' Comments:    Filters Report based on Name parameter
    ' Parameters:  strUserID - employee's email UserID
    '              strReportName - report name
    On Error GoTo PROC_ERR
    ' Declare variables
    Dim strSQL As String

    'Build SQL String
    strSQL = "[Name] " & " = '" & strUserID & "'"
    ' Filter the report
    Reports("rpt2008ProcedureReview").Filter = strSQL
   ' Turn the filter on
    Reports("rpt2008ProcedureReview").FilterOn = True

PROC_EXIT:
     Exit Sub

PROC_ERR:
     MsgBox Err.Description
     Resume PROC_EXIT
 End Sub

***Send email function***

Function SendReportByEmail(strReportName As String, strEmail As
String)
 'As Boolean
   ' Comments:    Sends an email using SendObject method
     ' Parameters:  strEmail - employee's email address
     '              strReportName - report name
     ' Returns:    True of False

     On Error GoTo PROC_ERR

     Dim strRecipient As String
     Dim strSubject As String
     Dim strMessageBody As String

     'set the mail varaibles
     strRecipient = strEmail
     strSubject = Reports(strReportName).Caption
     strMessageBody = "2008 Procedure Review Attached"

     'send the report as HTML
     DoCmd.SendObject acSendReport, strReportName, acFormatHTML,
strRecipient, , , strSubject, strMessageBody, False

     SendReportByEmail = True

PROC_EXIT:
     Exit Function

PROC_ERR:
     SendReportByEmail = False
     If Err.Number = 2501 Then
         Call MsgBox( _
             "The email was not sent for " & strEmail & "..", _
             vbOKOnly + vbExclamation + vbDefaultButton1, _
             "User Cancelled Operation")
     Else
         MsgBox Err.Description
     End If
     Resume PROC_EXIT

 End Function- Hide quoted text -

- Show quoted text -

.

Loading