Re: Need help with looping through records to email report

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

You probably need to add a line after displaying the inline errror message
box to clear the error. (I think Err.Clear should do it)

Post your final code, and I'll try to figure out what was causing the error.

I like to make sure that the error handling code is working properly. You
might even want to step through the code a couple of times, doing different
things. As an example, if you cancel the email action, it will return an
error code, and you probably want to ignore that, rather than display the
error message.

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.



"jag2004" wrote:

Dale,

Thank you so much. That works! I have been pulling my hair out
trying to get this!

I did have to comment out the error procedure. It would go through
and email the report but then it would give an "Object required"
message. I did a break and it was highlighting:

Resume PROC_EXIT

at the very bottom of the code.

I then had to comment out everything that refers to that and it worked
without a problem. I realize that means there is no way to exit if
there is an error.

You do not know how HELPFUL you have been!

Julie

On Dec 22, 7:11 pm, "Dale Fye" <dale....@xxxxxxxxxx> wrote:
My bad. A couple of issues.

1. The line in the function that caused the problem should read:

if Reset OR isempty(myUserID) then myUserID = Null

2. You don't really need to open the report in preview mode either, so I
removed that line of code. I also bypassed your SendReportByEmail code
altogether. If you want to still use that code, you will have to think of
another way to identify the Subject of the message, since the report won't
be open when you get to that code. I just hard coded it into the following.

Try this:

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, strSubject as String, strMsgBody as string
Dim strUserID As String
Dim fOk As Boolean

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

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

'Loop the recordset
Do While Not rst.EOF

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

'Grab the UserID string
Call fnUserID( rst.Fields("name"))

'Send the report
'Insert inline error handling to handle a cancelled report
ON Error Resume Next
strSubject = "2008 Procedure Review"
strMsgBody = "2008 Procedure Review Attached"
docmd.SendObject acSendReport, "rpt2008ProdedureReview",
acFormatHTML, _
strEmail, , , strSubject,
strMsgBody, False
If ERR.Number <> 0 then
MsgBox Err.Number & vbcrlf & err.Description, vbOkOnly,
"Delivery Failure to the following email address: " & strEmail
End If
On Error GoTo PROC_ERR

' Move and loop
rst.MoveNext
Loop

PROC_EXIT:
'Clean up. I like to put my cleanup in the Exit procedure so after the
error,
'I can resume at Proc_Exit and cleanup the battlefield
if not rs is nothing then
rst.Close
Set rst = Nothing
endif
if not dbs is nothing then
dbs.Close
Set dbs = Nothing
endif
Exit Sub

PROC_ERR:
MsgBox Err.Description
Resume PROC_EXIT
End Sub

HTH
Dale,

"jag2004" <julie_allo...@xxxxxxxxx> wrote in message

news:f7936602-f32b-4fa6-a1d4-1a5e06fa4566@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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 & "'"
.

Quantcast