Problem with report automatically emailing?

[cruford]

My problem is that when I push the button on my form to open my report it sends the report before loading the print preview on screen, then loads the print preview on screen and sends the report a second time. What am I doing wrong?
Here is the code I have so far:


Private Sub Report_Activate()
On Error GoTo Err_Report_Activate

Dim strDocName As String
Dim strOutputFormat As String
Dim strRecipName As String
Dim strCCName As String
Dim strSubject As String
strOutputFormat = "Snapshot Format"
strRecipName = "someone@somewhere.com"
strSubject = UCase([Patient Name]) & " " & [Customer ID] & " " & UCase([State])
strDocName = "Patient Information"
DoCmd.SendObject acSendReport, stDocName, strOutputFormat, strRecipName, , , strSubject, , False

Exit_Report_Activate:
Exit Sub

Err_Report_Activate:
MsgBox Err.Description
Resume Exit_Report_Activate

End Sub


This report is being opened from a command button on a form. Here is the code for the command button calling it:


Private Sub cmdPatientInfoReport_Click()
On Error GoTo Err_cmdPatientInfoReport_Click

Dim strDocName As String
Dim strWhere As String
strDocName = "Patient Information"
strWhere = "[Customer ID]=" & Me![Customer ID]
DoCmd.OpenReport strDocName, acViewPreview, , strWhere

Exit_cmdPatientInfoReport_Click:
Exit Sub

Err_cmdPatientInfoReport_Click:
MsgBox Err.Description
Resume Exit_cmdPatientInfoReport_Click

End Sub



Any Help on this would be greatly appreciated. I am using Access 2000 and Outlook 2000 for the email. Thank you.

 

[dysphoric]

By placing the SendObject command in the Activate Event, you're actually opening the report again before the first report becomes visible.

One possible workaround to prevent the report from being sent twice is to insert a counter to identify how many times the report is opened. If the report is already opened, the SendObject command is skipped.

I've managed to do this by creating a global variable [gintOpenCount].

In the report's Open Event (which occurs before the activate event):
If gintOpenCount = 0 then
gintOpenCount = 1
else
gintOpenCount = gintOpenCount + 1
end if

In the report's Activate Event:
If gintOpenCount <= 1 Then
'insert procedure to send the email here
end if

In the report's Close Event:
gintOpenCount = 0

This allows the report to email the first time it is opened, but when the report is opened from the SendObject command, it doesn't run the SendObject command again.

Of course, this may not be the best way to handle it, but it's all that I can come up with right now.

 

[cruford]

Thanks dysphoric, that worked.