G
GVR_Mike
This is quite strange and I can't figure it out. When I run this form
manually and click the Yes button each time it asks if I want to send
out the e-mail then it works fine, but when I put the database on my
report server it will only send out the first e-mail. It will go
through all the other motions and it doesn't send me an error, it just
won't send anymore e-mails after the first one. I'm using a small
program called ClickYes on the report server which automatically
clicks the "Yes" button when the Outlook pop-up appears to ask if
you're sure you want to send the e-mail. Maybe it has something to do
with that program but I wanted to make sure there wasn't something
wrong with my code. Like I said it works fine when I manually
acknowledge each e-mail. I know the queries are running properly and
they are always getting some results so RecordCount > 0 always.
Thanks in advance
Private Sub Form_Load()
On Error GoTo Err_Form_Load
Dim qryDetails, qryError, qryCount As String
Dim con As ADODB.Connection
Dim rsDetails, rsError, rsCount As ADODB.Recordset
Dim i As Integer
Dim pausetime, start
qryDetails = "Agent Audit Details 2 Final"
qryError = "Agent Error Details"
qryCount = "Agent Count"
Form.Visible = True
DoCmd.SetWarnings False
DoCmd.OpenQuery qryError, acNormal, acEdit
DoCmd.OpenQuery qryCount, acNormal, acEdit
DoCmd.OpenQuery qryDetails, acNormal, acEdit
Set con = CurrentProject.Connection
Set rsDetails = New ADODB.Recordset
Set rsError = New ADODB.Recordset
Set rsCount = New ADODB.Recordset
rsDetails.Open "tblAgentAuditDetails", con, adOpenStatic,
adLockReadOnly, adCmdTable
rsError.Open "tblAgentErrorDetails", con, adOpenStatic,
adLockReadOnly, adCmdTable
rsCount.Open "tblAgentCount", con, adOpenStatic, adLockReadOnly,
adCmdTable
If rsDetails.RecordCount > 0 Then
DoCmd.SendObject acSendTable, "tblAgentAuditDetails",
acFormatXLS, _
"<emailaddress>", _
"<emailaddress>", , _
"Agent Audit Summary", "This e-mail, and it's contents,
are auto-generated. " & _
"Please see Mike Bruesch with questions.", False
End If
If rsError.RecordCount > 0 Then
DoCmd.SendObject acSendTable, "tblAgentErrorDetails",
acFormatXLS, _
"<emailaddress>", _
"<emailaddress>", , _
"Agent Audit Error Details", "This e-mail, and it's
contents, are auto-generated. " & _
"Please see Mike Bruesch with questions.", False
End If
rsCount.MoveFirst
For i = 1 To rsCount.RecordCount
DoCmd.RunSQL "SELECT VRSC_ALARMS_INCOMING.ALI_USER_ID AS
Agent, tblSCAgents.Email, tblLIAudit.NotifyNum, " & _
"VRSC_ALARM_TYPES.ALT_TYPE_DESC AS [Alarm Desc],
tblScore.ScoreDesc, tblLIAudit.Comments " & _
"INTO tblIndvAgentAudits " & _
"FROM (((tblLIAudit INNER JOIN
VRSC_ALARMS_INCOMING ON tblLIAudit.NotifyNum = " & _
"VRSC_ALARMS_INCOMING.ALI_ALARM_NOTIFY_NBR) INNER
JOIN tblSCAgents ON " & _
"VRSC_ALARMS_INCOMING.ALI_USER_ID =
tblSCAgents.UserID) INNER JOIN VRSC_ALARM_TYPES " & _
"ON (VRSC_ALARMS_INCOMING.ALI_ALARM_CATEGORY =
VRSC_ALARM_TYPES.ALT_ALARM_CAT) AND " & _
"(VRSC_ALARMS_INCOMING.ALI_ALARM_TYPE =
VRSC_ALARM_TYPES.ALT_ALARM_TYPE)) " & _
"INNER JOIN tblScore ON tblLIAudit.Score =
tblScore.ScoreID " & _
"WHERE (((VRSC_ALARMS_INCOMING.ALI_USER_ID)='" &
rsCount!Agent & "'));"
DoCmd.SendObject acSendTable, "tblIndvAgentAudits",
acFormatXLS, _
rsCount!Email, "<emailaddress>", , _
"Agent Audit Details for " & rsCount!Agent, "This e-mail,
and it's contents, are auto-generated. " & _
"Please see Mike Bruesch with questions.", False
rsCount.MoveNext
Next i
DoCmd.SetWarnings True
rsDetails.Close
rsError.Close
rsCount.Close
Set rsDetails = Nothing
Set rsError = Nothing
Set rsCount = Nothing
con.Close
Set con = Nothing
DoCmd.Quit
Exit_Form_Load:
DoCmd.Quit
Err_Form_Load:
DoCmd.SetWarnings True
DoCmd.SendObject , , , "<emailaddress>", , , _
"ERROR OCCURRED - Agent Audit Results", "Error Message: "
& Err.Description, False
Resume Exit_Form_Load
End Sub
manually and click the Yes button each time it asks if I want to send
out the e-mail then it works fine, but when I put the database on my
report server it will only send out the first e-mail. It will go
through all the other motions and it doesn't send me an error, it just
won't send anymore e-mails after the first one. I'm using a small
program called ClickYes on the report server which automatically
clicks the "Yes" button when the Outlook pop-up appears to ask if
you're sure you want to send the e-mail. Maybe it has something to do
with that program but I wanted to make sure there wasn't something
wrong with my code. Like I said it works fine when I manually
acknowledge each e-mail. I know the queries are running properly and
they are always getting some results so RecordCount > 0 always.
Thanks in advance
Private Sub Form_Load()
On Error GoTo Err_Form_Load
Dim qryDetails, qryError, qryCount As String
Dim con As ADODB.Connection
Dim rsDetails, rsError, rsCount As ADODB.Recordset
Dim i As Integer
Dim pausetime, start
qryDetails = "Agent Audit Details 2 Final"
qryError = "Agent Error Details"
qryCount = "Agent Count"
Form.Visible = True
DoCmd.SetWarnings False
DoCmd.OpenQuery qryError, acNormal, acEdit
DoCmd.OpenQuery qryCount, acNormal, acEdit
DoCmd.OpenQuery qryDetails, acNormal, acEdit
Set con = CurrentProject.Connection
Set rsDetails = New ADODB.Recordset
Set rsError = New ADODB.Recordset
Set rsCount = New ADODB.Recordset
rsDetails.Open "tblAgentAuditDetails", con, adOpenStatic,
adLockReadOnly, adCmdTable
rsError.Open "tblAgentErrorDetails", con, adOpenStatic,
adLockReadOnly, adCmdTable
rsCount.Open "tblAgentCount", con, adOpenStatic, adLockReadOnly,
adCmdTable
If rsDetails.RecordCount > 0 Then
DoCmd.SendObject acSendTable, "tblAgentAuditDetails",
acFormatXLS, _
"<emailaddress>", _
"<emailaddress>", , _
"Agent Audit Summary", "This e-mail, and it's contents,
are auto-generated. " & _
"Please see Mike Bruesch with questions.", False
End If
If rsError.RecordCount > 0 Then
DoCmd.SendObject acSendTable, "tblAgentErrorDetails",
acFormatXLS, _
"<emailaddress>", _
"<emailaddress>", , _
"Agent Audit Error Details", "This e-mail, and it's
contents, are auto-generated. " & _
"Please see Mike Bruesch with questions.", False
End If
rsCount.MoveFirst
For i = 1 To rsCount.RecordCount
DoCmd.RunSQL "SELECT VRSC_ALARMS_INCOMING.ALI_USER_ID AS
Agent, tblSCAgents.Email, tblLIAudit.NotifyNum, " & _
"VRSC_ALARM_TYPES.ALT_TYPE_DESC AS [Alarm Desc],
tblScore.ScoreDesc, tblLIAudit.Comments " & _
"INTO tblIndvAgentAudits " & _
"FROM (((tblLIAudit INNER JOIN
VRSC_ALARMS_INCOMING ON tblLIAudit.NotifyNum = " & _
"VRSC_ALARMS_INCOMING.ALI_ALARM_NOTIFY_NBR) INNER
JOIN tblSCAgents ON " & _
"VRSC_ALARMS_INCOMING.ALI_USER_ID =
tblSCAgents.UserID) INNER JOIN VRSC_ALARM_TYPES " & _
"ON (VRSC_ALARMS_INCOMING.ALI_ALARM_CATEGORY =
VRSC_ALARM_TYPES.ALT_ALARM_CAT) AND " & _
"(VRSC_ALARMS_INCOMING.ALI_ALARM_TYPE =
VRSC_ALARM_TYPES.ALT_ALARM_TYPE)) " & _
"INNER JOIN tblScore ON tblLIAudit.Score =
tblScore.ScoreID " & _
"WHERE (((VRSC_ALARMS_INCOMING.ALI_USER_ID)='" &
rsCount!Agent & "'));"
DoCmd.SendObject acSendTable, "tblIndvAgentAudits",
acFormatXLS, _
rsCount!Email, "<emailaddress>", , _
"Agent Audit Details for " & rsCount!Agent, "This e-mail,
and it's contents, are auto-generated. " & _
"Please see Mike Bruesch with questions.", False
rsCount.MoveNext
Next i
DoCmd.SetWarnings True
rsDetails.Close
rsError.Close
rsCount.Close
Set rsDetails = Nothing
Set rsError = Nothing
Set rsCount = Nothing
con.Close
Set con = Nothing
DoCmd.Quit
Exit_Form_Load:
DoCmd.Quit
Err_Form_Load:
DoCmd.SetWarnings True
DoCmd.SendObject , , , "<emailaddress>", , , _
"ERROR OCCURRED - Agent Audit Results", "Error Message: "
& Err.Description, False
Resume Exit_Form_Load
End Sub