D
Darb
Thanks for taking the time to read my question.
I am having problems with my error handling.
I am opening several recordsets. Upon opening, I want to
go to the first record of each. The recordsets are based
on sql statements. Sometimes the statements return no
records which is fine. In order to handle this, I
created some integer variables that if equal to 1, send
the execution of the code back up, one line past where
the error occurred using line lables. This works well
for me... the first time the error occurs. This error
handling does not work the second time. The message I
get from Access is Run Time Error 3021, No Current
Record, which is exactly what I have programmed to work
around and that I was able to handle the first time. Why
doesn't it work again?
Darb
p.s. sorry for the long code. I'm just learning.
Variables not defined are Globals defined in a module.
Private Sub cmdWklyInspRpt_Click()
Dim dbs As Database, rst As Recordset, rst1 As Recordset,
rst2 As Recordset
Dim ServRepEMail As String
Dim x As Integer, y As Integer, e As Integer
Dim PeopleNotEmailed As String
Dim KeepSending As String
On Error GoTo ErrorLine
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("tblEMailTo")
rst.MoveFirst
Do Until rst.EOF
MsgBox rst![EMailToID] & ", " & rst![EMailToServiceRep]
& ", " & rst![EMailAddress] & " is the current record."
On Error GoTo ErrorLine
x = 0
y = 0
e = 0
ServRepName = rst![EMailToServiceRep]
ServRepEMail = rst![EMailAddress]
Set rst1 = dbs.OpenRecordset("SELECT
tblEMailTo.EMailToServiceRep, tblEMailTo.EMailAddress,
tblEMailToDetail.EMailToSowBarnName,
tblEMailToDetail.EMailToNurseryBarnName " & _
"FROM tblEMailTo
INNER JOIN tblEMailToDetail ON tblEMailTo.EMailToID =
tblEMailToDetail.EMailToIDInEMailToDetail " & _
"Where
tblEMailTo.EMailToServiceRep = '" & ServRepName & "' AND
(tblEMailToDetail.EMailToNURSERYBarnName) IS NOT NULL;")
Set rst2 = dbs.OpenRecordset("SELECT
tblEMailTo.EMailToServiceRep, tblEMailTo.EMailAddress,
tblEMailToDetail.EMailToSowBarnName,
tblEMailToDetail.EMailToNurseryBarnName " & _
"FROM tblEMailTo
INNER JOIN tblEMailToDetail ON tblEMailTo.EMailToID =
tblEMailToDetail.EMailToIDInEMailToDetail " & _
"Where
tblEMailTo.EMailToServiceRep = '" & ServRepName & "' AND
(tblEMailToDetail.EMailToSOWBarnName) IS NOT NULL;")
'This works the first time but not after that. In my DB
e gets set to 1, the error occurs, the error handling
works and the execution returns to e1: Next time
through, the error handling doesn't work and I get the
message stated above
e = 1
rst1.MoveFirst
e1:
Do Until rst1.EOF
x = x + 1
rst1.MoveNext
Loop
MsgBox x & " Nursery farms were chosen."
e = 2
rst2.MoveFirst
e2:
Do Until rst2.EOF
y = y + 1
rst2.MoveNext
Loop
MsgBox y & " Sow farms were chosen."
If x > 0 And y > 0 Then
'Filter for Reports. Don't erase this.
It doesn't affect the code, but you can paste this into
the filter line if it ever gets erased in the propeties.
'"EMailToServiceRep = '" & ServRepName
& "'"
OpenSmall = True
DoCmd.SendObject
acSendReport, "rptqryServRepWeeklyReportNURSERY",
acFormatRTF, ServRepEMail, , , "Easywean Inspection Forms
Report: " & Me.StartDate & " To " & Me.EndDate
DoCmd.SendObject
acSendReport, "rptqryServRepWeeklyReportSOW",
acFormatRTF, ServRepEMail, , , "Easywean Inspection Forms
Report: " & Me.StartDate & " To " & Me.EndDate
Else
If x > 0 And y = 0 Then
OpenSmall = True
DoCmd.SendObject
acSendReport, "rptqryServRepWeeklyReportNURSERY",
acFormatRTF, ServRepEMail, , , "Easywean Inspection Forms
Report: " & Me.StartDate & " To " & Me.EndDate
End If
If y > 0 And x = 0 Then
OpenSmall = True
DoCmd.SendObject
acSendReport, "rptqryServRepWeeklyReportSOW",
acFormatRTF, ServRepEMail, , , "Easywean Inspection Forms
Report: " & Me.StartDate & " To " & Me.EndDate
End If
End If
If x = 0 And y = 0 Then
If PeopleNotEmailed = "" Then
PeopleNotEmailed = ServRepName
Else
PeopleNotEmailed = PeopleNotEmailed
& ", " & ServRepName
End If
End If
GetBackToStart:
rst.MoveNext
Loop
If Not IsNull(PeopleNotEmailed) Then
MsgBox PeopleNotEmailed & " were not sent a report."
& Chr(13) & Chr(13) & " You may want to check the records
for the time period the report was based on to make sure
these people were not supposed to receive a report.", 48
End If
rst.Close
Set rst = Nothing
Set dbs = Nothing
ResumeFromError:
OpenSmall = False
Exit Sub
ErrorLine:
If Err.Number = 3021 Then
If e = 1 Then
GoTo e1
End If
If e = 2 Then
GoTo e2
End If
Else
If Err.Number = 2501 Then
KeepSending = MsgBox("You have stopped sending
the report. Do you want to continue to send the rest of
the reports?", 36)
If KeepSending = vbYes Then
GoTo GetBackToStart
Else
MsgBox "You have halted this procedure.
Click the 'Wkly Insp Rpt' button to run the reports
again."
Exit Sub
End If
Else
MsgBox Err.Description
GoTo ResumeFromError
End If
End If
End Sub
I am having problems with my error handling.
I am opening several recordsets. Upon opening, I want to
go to the first record of each. The recordsets are based
on sql statements. Sometimes the statements return no
records which is fine. In order to handle this, I
created some integer variables that if equal to 1, send
the execution of the code back up, one line past where
the error occurred using line lables. This works well
for me... the first time the error occurs. This error
handling does not work the second time. The message I
get from Access is Run Time Error 3021, No Current
Record, which is exactly what I have programmed to work
around and that I was able to handle the first time. Why
doesn't it work again?
Darb
p.s. sorry for the long code. I'm just learning.
Variables not defined are Globals defined in a module.
Private Sub cmdWklyInspRpt_Click()
Dim dbs As Database, rst As Recordset, rst1 As Recordset,
rst2 As Recordset
Dim ServRepEMail As String
Dim x As Integer, y As Integer, e As Integer
Dim PeopleNotEmailed As String
Dim KeepSending As String
On Error GoTo ErrorLine
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("tblEMailTo")
rst.MoveFirst
Do Until rst.EOF
MsgBox rst![EMailToID] & ", " & rst![EMailToServiceRep]
& ", " & rst![EMailAddress] & " is the current record."
On Error GoTo ErrorLine
x = 0
y = 0
e = 0
ServRepName = rst![EMailToServiceRep]
ServRepEMail = rst![EMailAddress]
Set rst1 = dbs.OpenRecordset("SELECT
tblEMailTo.EMailToServiceRep, tblEMailTo.EMailAddress,
tblEMailToDetail.EMailToSowBarnName,
tblEMailToDetail.EMailToNurseryBarnName " & _
"FROM tblEMailTo
INNER JOIN tblEMailToDetail ON tblEMailTo.EMailToID =
tblEMailToDetail.EMailToIDInEMailToDetail " & _
"Where
tblEMailTo.EMailToServiceRep = '" & ServRepName & "' AND
(tblEMailToDetail.EMailToNURSERYBarnName) IS NOT NULL;")
Set rst2 = dbs.OpenRecordset("SELECT
tblEMailTo.EMailToServiceRep, tblEMailTo.EMailAddress,
tblEMailToDetail.EMailToSowBarnName,
tblEMailToDetail.EMailToNurseryBarnName " & _
"FROM tblEMailTo
INNER JOIN tblEMailToDetail ON tblEMailTo.EMailToID =
tblEMailToDetail.EMailToIDInEMailToDetail " & _
"Where
tblEMailTo.EMailToServiceRep = '" & ServRepName & "' AND
(tblEMailToDetail.EMailToSOWBarnName) IS NOT NULL;")
'This works the first time but not after that. In my DB
e gets set to 1, the error occurs, the error handling
works and the execution returns to e1: Next time
through, the error handling doesn't work and I get the
message stated above
e = 1
rst1.MoveFirst
e1:
Do Until rst1.EOF
x = x + 1
rst1.MoveNext
Loop
MsgBox x & " Nursery farms were chosen."
e = 2
rst2.MoveFirst
e2:
Do Until rst2.EOF
y = y + 1
rst2.MoveNext
Loop
MsgBox y & " Sow farms were chosen."
If x > 0 And y > 0 Then
'Filter for Reports. Don't erase this.
It doesn't affect the code, but you can paste this into
the filter line if it ever gets erased in the propeties.
'"EMailToServiceRep = '" & ServRepName
& "'"
OpenSmall = True
DoCmd.SendObject
acSendReport, "rptqryServRepWeeklyReportNURSERY",
acFormatRTF, ServRepEMail, , , "Easywean Inspection Forms
Report: " & Me.StartDate & " To " & Me.EndDate
DoCmd.SendObject
acSendReport, "rptqryServRepWeeklyReportSOW",
acFormatRTF, ServRepEMail, , , "Easywean Inspection Forms
Report: " & Me.StartDate & " To " & Me.EndDate
Else
If x > 0 And y = 0 Then
OpenSmall = True
DoCmd.SendObject
acSendReport, "rptqryServRepWeeklyReportNURSERY",
acFormatRTF, ServRepEMail, , , "Easywean Inspection Forms
Report: " & Me.StartDate & " To " & Me.EndDate
End If
If y > 0 And x = 0 Then
OpenSmall = True
DoCmd.SendObject
acSendReport, "rptqryServRepWeeklyReportSOW",
acFormatRTF, ServRepEMail, , , "Easywean Inspection Forms
Report: " & Me.StartDate & " To " & Me.EndDate
End If
End If
If x = 0 And y = 0 Then
If PeopleNotEmailed = "" Then
PeopleNotEmailed = ServRepName
Else
PeopleNotEmailed = PeopleNotEmailed
& ", " & ServRepName
End If
End If
GetBackToStart:
rst.MoveNext
Loop
If Not IsNull(PeopleNotEmailed) Then
MsgBox PeopleNotEmailed & " were not sent a report."
& Chr(13) & Chr(13) & " You may want to check the records
for the time period the report was based on to make sure
these people were not supposed to receive a report.", 48
End If
rst.Close
Set rst = Nothing
Set dbs = Nothing
ResumeFromError:
OpenSmall = False
Exit Sub
ErrorLine:
If Err.Number = 3021 Then
If e = 1 Then
GoTo e1
End If
If e = 2 Then
GoTo e2
End If
Else
If Err.Number = 2501 Then
KeepSending = MsgBox("You have stopped sending
the report. Do you want to continue to send the rest of
the reports?", 36)
If KeepSending = vbYes Then
GoTo GetBackToStart
Else
MsgBox "You have halted this procedure.
Click the 'Wkly Insp Rpt' button to run the reports
again."
Exit Sub
End If
Else
MsgBox Err.Description
GoTo ResumeFromError
End If
End If
End Sub