Need help with looping through records to email report

J

jag2004

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
 
D

Dale Fye

Julie,

1. First thing I would do differently is modify your first SQL string, so
that it selects DISTINCT [Name] and 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.



[QUOTE="jag2004"]
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
[/QUOTE]
 
J

jag2004

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
Julie,

1.  First thing I would do differently is modify your first SQL string,so
that it selects DISTINCT [Name] and 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.



[QUOTE="jag2004"]
Hello,[/QUOTE]
[QUOTE]
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.[/QUOTE]
[QUOTE]
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[/QUOTE]
[QUOTE]
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.[/QUOTE]
[QUOTE]
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.[/QUOTE]
[QUOTE]
Any help would be GREATLY APPRECIATED!!!

Private Sub Command0_Click()
'Private Sub cmdSendReport_Click()
On Error GoTo PROC_ERR[/QUOTE]
[QUOTE]
' 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[/QUOTE]
[QUOTE]
 ' Build our SQL string
 strSQL = "SELECT Name, Email From [2008MicroProcReviewtest]"[/QUOTE]
[QUOTE]
 ' Set our database and recordset objects
 Set dbs = CurrentDb
 Set rst = dbs.OpenRecordset(strSQL)[/QUOTE]
[QUOTE]
 ' Open the report
 DoCmd.OpenReport "rpt2008ProcedureReview", acPreview[/QUOTE]
[QUOTE]
 'Turn the filter on
 Reports![rpt2008ProcedureReview].FilterOn = True[/QUOTE]
[QUOTE]
  ' Loop the recordset
 Do While Not rst.EOF[/QUOTE]
[QUOTE]
 ' Grab the Email string
 strEmail = rst.Fields("email")[/QUOTE]
[QUOTE]
 ' Grab the UserID string
 strUserID = rst.Fields("name")[/QUOTE]
[QUOTE]
 ' Call the procedure used to filter the report based on the
 'Current employee
 Call FilterReport("rpt2008ProcedureReview", strUserID)[/QUOTE]
[QUOTE]
 ' Allow the report to refresh after filtering
 DoEvents[/QUOTE]
[QUOTE]
 ' Send the snapshot of the report to the current employee
 fOk = SendReportByEmail("rpt2008ProcedureReview", strEmail)[/QUOTE]
[QUOTE]
 ' Display message if failure
 If Not fOk Then
    MsgBox "Delivery Failure to the following email address: " &
strEmail
 End If[/QUOTE]
[QUOTE]
 ' Move and loop
 rst.MoveNext
 Loop[/QUOTE]
[QUOTE]
 ' Clean up
 rst.Close
 Set rst = Nothing
 dbs.Close
 Set dbs = Nothing[/QUOTE]
[QUOTE]
PROC_EXIT:
     Exit Sub[/QUOTE]
[QUOTE]
PROC_ERR:
     MsgBox Err.Description
     Resume PROC_EXIT
 End Sub[/QUOTE]
[QUOTE]
***Filter report function****[/QUOTE]
[QUOTE]
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[/QUOTE]
[QUOTE]
    'Build SQL String
    strSQL = "[Name] " & " = '" & strUserID & "'"
    ' Filter the report
    Reports("rpt2008ProcedureReview").Filter = strSQL
   ' Turn the filter on
    Reports("rpt2008ProcedureReview").FilterOn = True[/QUOTE]
[QUOTE]
PROC_EXIT:
     Exit Sub[/QUOTE]
[QUOTE]
PROC_ERR:
     MsgBox Err.Description
     Resume PROC_EXIT
 End Sub[/QUOTE]
[QUOTE]
***Send email function***[/QUOTE]
[QUOTE]
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[/QUOTE]
[QUOTE]
     On Error GoTo PROC_ERR[/QUOTE]
[QUOTE]
     Dim strRecipient As String
     Dim strSubject As String
     Dim strMessageBody As String[/QUOTE]
[QUOTE]
     'set the mail varaibles
     strRecipient = strEmail
     strSubject = Reports(strReportName).Caption
     strMessageBody = "2008 Procedure Review Attached"[/QUOTE]
[QUOTE]
     'send the report as HTML
     DoCmd.SendObject acSendReport, strReportName, acFormatHTML,
strRecipient, , , strSubject, strMessageBody, False[/QUOTE]
[QUOTE]
     SendReportByEmail = True[/QUOTE]
[QUOTE]
PROC_EXIT:
     Exit Function[/QUOTE]
[QUOTE]
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[/QUOTE]
[QUOTE]
 End Function- Hide quoted text -[/QUOTE]

- Show quoted text -[/QUOTE]
Click to expand...
 
D

Dale Fye

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,


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
Julie,

1. First thing I would do differently is modify your first SQL string, so
that it selects DISTINCT [Name] and 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.



[QUOTE="jag2004"]
Hello,[/QUOTE]
[QUOTE]
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.[/QUOTE]
[QUOTE]
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[/QUOTE]
[QUOTE]
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.[/QUOTE]
[QUOTE]
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.[/QUOTE]
[QUOTE]
Any help would be GREATLY APPRECIATED!!!

Private Sub Command0_Click()
'Private Sub cmdSendReport_Click()
On Error GoTo PROC_ERR[/QUOTE]
[QUOTE]
' 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[/QUOTE]
[QUOTE]
' Build our SQL string
strSQL = "SELECT Name, Email From [2008MicroProcReviewtest]"[/QUOTE]
[QUOTE]
' Set our database and recordset objects
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strSQL)[/QUOTE]
[QUOTE]
' Open the report
DoCmd.OpenReport "rpt2008ProcedureReview", acPreview[/QUOTE]
[QUOTE]
'Turn the filter on
Reports![rpt2008ProcedureReview].FilterOn = True[/QUOTE]
[QUOTE]
' Loop the recordset
Do While Not rst.EOF[/QUOTE]
[QUOTE]
' Grab the Email string
strEmail = rst.Fields("email")[/QUOTE]
[QUOTE]
' Grab the UserID string
strUserID = rst.Fields("name")[/QUOTE]
[QUOTE]
' Call the procedure used to filter the report based on the
'Current employee
Call FilterReport("rpt2008ProcedureReview", strUserID)[/QUOTE]
[QUOTE]
' Allow the report to refresh after filtering
DoEvents[/QUOTE]
[QUOTE]
' Send the snapshot of the report to the current employee
fOk = SendReportByEmail("rpt2008ProcedureReview", strEmail)[/QUOTE]
[QUOTE]
' Display message if failure
If Not fOk Then
MsgBox "Delivery Failure to the following email address: " &
strEmail
End If[/QUOTE]
[QUOTE]
' Move and loop
rst.MoveNext
Loop[/QUOTE]
[QUOTE]
' Clean up
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing[/QUOTE]
[QUOTE]
PROC_EXIT:
Exit Sub[/QUOTE]
[QUOTE]
PROC_ERR:
MsgBox Err.Description
Resume PROC_EXIT
End Sub[/QUOTE]
[QUOTE]
***Filter report function****[/QUOTE]
[QUOTE]
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[/QUOTE]
[QUOTE]
'Build SQL String
strSQL = "[Name] " & " = '" & strUserID & "'"
' Filter the report
Reports("rpt2008ProcedureReview").Filter = strSQL
' Turn the filter on
Reports("rpt2008ProcedureReview").FilterOn = True[/QUOTE]
[QUOTE]
PROC_EXIT:
Exit Sub[/QUOTE]
[QUOTE]
PROC_ERR:
MsgBox Err.Description
Resume PROC_EXIT
End Sub[/QUOTE]
[QUOTE]
***Send email function***[/QUOTE]
[QUOTE]
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[/QUOTE]
[QUOTE]
On Error GoTo PROC_ERR[/QUOTE]
[QUOTE]
Dim strRecipient As String
Dim strSubject As String
Dim strMessageBody As String[/QUOTE]
[QUOTE]
'set the mail varaibles
strRecipient = strEmail
strSubject = Reports(strReportName).Caption
strMessageBody = "2008 Procedure Review Attached"[/QUOTE]
[QUOTE]
'send the report as HTML
DoCmd.SendObject acSendReport, strReportName, acFormatHTML,
strRecipient, , , strSubject, strMessageBody, False[/QUOTE]
[QUOTE]
SendReportByEmail = True[/QUOTE]
[QUOTE]
PROC_EXIT:
Exit Function[/QUOTE]
[QUOTE]
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[/QUOTE]
[QUOTE]
End Function- Hide quoted text -[/QUOTE]

- Show quoted text -[/QUOTE]
Click to expand...
 
J

jag2004

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

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, soI
removed that line of code.  I also bypassed your SendReportByEmail code
altogether.  If you want to still use that code, you will have to thinkof
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,


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
Julie,
Click to expand...
1. First thing I would do differently is modify your first SQL string, so
that it selects DISTINCT [Name] and 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.[/QUOTE]
[QUOTE]
strSQL = "SELECT DISTINCT [Name], [Email] FROM [2008MicroProcReviewTest][/QUOTE]
[QUOTE]
2. Create a function fnUserID() that looks like the following. Put thisin
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.[/QUOTE]
[QUOTE]
Public Function fnUserID(Optional SomeValue as Variant = Null, _
Optional Reset as boolean = false) as
Variant[/QUOTE]
[QUOTE]
Static myUserID as Variant[/QUOTE]
[QUOTE]
if Reset OF isempty(myUserID) then myUserID = Null
if not isnull(SomeValue) then myUserID = SomeValue[/QUOTE]
[QUOTE]
fnUserID = myUserID[/QUOTE]
[QUOTE]
End function[/QUOTE]
[QUOTE]
3. Modify the query that you use for your report to look something like:[/QUOTE]
[QUOTE]
SELECT * FROM ....
WHERE UserID = fnUserID()
OR fnUserID() IS NULL[/QUOTE]
[QUOTE]
4. Then, inside the loop (Do While not rst.eof: Wend), I would set
fnUserID
with a line of code that looks like:[/QUOTE]
[QUOTE]
Call fnUserID(rs.Fields("Name"))[/QUOTE]
[QUOTE]
5. Since the reports underlying query looks at fnUserID, you can delete
the
call to FilterReport
email address is invalid
Please reply to newsgroup only.[/QUOTE]
[QUOTE]
[QUOTE="jag2004"]
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 -[/QUOTE][/QUOTE]
[QUOTE]
- Show quoted text -- Hide quoted text -[/QUOTE]

- Show quoted text -[/QUOTE]
Click to expand...
Click to expand...
 
D

Dale Fye

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

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,


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
Julie,
Click to expand...
1. First thing I would do differently is modify your first SQL string, so
that it selects DISTINCT [Name] and 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.[/QUOTE]
[QUOTE]
strSQL = "SELECT DISTINCT [Name], [Email] FROM [2008MicroProcReviewTest][/QUOTE]
[QUOTE]
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.[/QUOTE]
[QUOTE]
Public Function fnUserID(Optional SomeValue as Variant = Null, _
Optional Reset as boolean = false) as
Variant[/QUOTE]
[QUOTE]
Static myUserID as Variant[/QUOTE]
[QUOTE]
if Reset OF isempty(myUserID) then myUserID = Null
if not isnull(SomeValue) then myUserID = SomeValue[/QUOTE]
[QUOTE]
fnUserID = myUserID[/QUOTE]
[QUOTE]
End function[/QUOTE]
[QUOTE]
3. Modify the query that you use for your report to look something like:[/QUOTE]
[QUOTE]
SELECT * FROM ....
WHERE UserID = fnUserID()
OR fnUserID() IS NULL[/QUOTE]
[QUOTE]
4. Then, inside the loop (Do While not rst.eof: Wend), I would set
fnUserID
with a line of code that looks like:[/QUOTE]
[QUOTE]
Call fnUserID(rs.Fields("Name"))[/QUOTE]
[QUOTE]
5. Since the reports underlying query looks at fnUserID, you can delete
the
call to FilterReport
email address is invalid
Please reply to newsgroup only.[/QUOTE]
[QUOTE]
:
Hello,[/QUOTE]
[QUOTE]
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.[/QUOTE]
[QUOTE]
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[/QUOTE]
[QUOTE]
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.[/QUOTE]
[QUOTE]
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.[/QUOTE]
[QUOTE]
Any help would be GREATLY APPRECIATED!!!

Private Sub Command0_Click()
'Private Sub cmdSendReport_Click()
On Error GoTo PROC_ERR[/QUOTE]
[QUOTE]
' 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[/QUOTE]
[QUOTE]
' Build our SQL string
strSQL = "SELECT Name, Email From [2008MicroProcReviewtest]"[/QUOTE]
[QUOTE]
' Set our database and recordset objects
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strSQL)[/QUOTE]
[QUOTE]
' Open the report
DoCmd.OpenReport "rpt2008ProcedureReview", acPreview[/QUOTE]
[QUOTE]
'Turn the filter on
Reports![rpt2008ProcedureReview].FilterOn = True[/QUOTE]
[QUOTE]
' Loop the recordset
Do While Not rst.EOF[/QUOTE]
[QUOTE]
' Grab the Email string
strEmail = rst.Fields("email")[/QUOTE]
[QUOTE]
' Grab the UserID string
strUserID = rst.Fields("name")[/QUOTE]
[QUOTE]
' Call the procedure used to filter the report based on the
'Current employee
Call FilterReport("rpt2008ProcedureReview", strUserID)[/QUOTE]
[QUOTE]
' Allow the report to refresh after filtering
DoEvents[/QUOTE]
[QUOTE]
' Send the snapshot of the report to the current employee
fOk = SendReportByEmail("rpt2008ProcedureReview", strEmail)[/QUOTE]
[QUOTE]
' Display message if failure
If Not fOk Then
MsgBox "Delivery Failure to the following email address: " &
strEmail
End If[/QUOTE]
[QUOTE]
' Move and loop
rst.MoveNext
Loop[/QUOTE]
[QUOTE]
' Clean up
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing[/QUOTE]
[QUOTE]
PROC_EXIT:
Exit Sub[/QUOTE]
[QUOTE]
PROC_ERR:
MsgBox Err.Description
Resume PROC_EXIT
End Sub[/QUOTE]
[QUOTE]
***Filter report function****[/QUOTE]
[QUOTE]
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[/QUOTE]
[QUOTE]
'Build SQL String
strSQL = "[Name] " & " = '" & strUserID & "'"[/QUOTE][/QUOTE][/QUOTE]
Click to expand...
Click to expand...
Click to expand...
 
J

jag2004

Hi Dale,

Sorry for the delay, with the holidays I was busy with other
assignments. Here is the final code:

The error checking portion is still commented out.

Julie

Private Sub Command0_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 [2008MicroProcReview]"


'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, "rpt2008ProcedureReview",
acFormatRTF, 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
End If
If Not dbs Is Nothing Then
dbs.Close
Set dbs = Nothing
End If
Exit Sub


'PROC_ERR:
' MsgBox Err.Description
'Resume PROC_EXIT
'End Sub



End Sub


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 said:
Dale,
Click to expand...
Thank you so much.  That works!  I have been pulling my hair out
trying to get this!
Click to expand...
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:
Click to expand...
Resume PROC_EXIT
Click to expand...
at the very bottom of the code.
Click to expand...
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.
Click to expand...
You do not know how HELPFUL you have been!
Julie
Click to expand...

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,
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 andan
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
Julie,
1. First thing I would do differently is modify your first SQL string, so
that it selects DISTINCT [Name] and 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.
:
Hello,
I have been working on a problem in my spare time for a few weeksnow,
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 employeehas
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 outhow
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[/QUOTE][/QUOTE]

...

read more »- Hide quoted text -

- Show quoted text -[/QUOTE]
Click to expand...
Click to expand...
Click to expand...
 
D

Dale Fye

So, when you turn on the error handler at the beginning of the code, does it
work, or is it erroring out somewhere?

If you are encountering an error, it might be that you have a NULL email
address, and trying to set a string = NULL would generate an error. Try
changing the SQL statment to:

SELECT Distinct Name, Email
FROM [2008MicroProcReview]
WHERE (e-mail address removed)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Help with VB 5
Qualifier Must Be Collection 1
Invalid use of null 1
outlook 2007 warning message when emailing from Access 2
Email Subject Line 12
Attachments 1
Attachments 4
Combine Code 2

Top