Invalid Operation on recordset.movefirst

B

Brad

Thanks for reading my question.

My code breaks at the If Not rst2.EOF Then rst2.MoveFirst
line. It passes the If not rst2.EOF part, but errors on
the Then rst2.MoveFirst.

I am getting an Invalid Operation error (err.number =
5003219).
I am not sure why because I do exactly the same thing
with rst1 just prior.

Any suggestions?

Thanks again for the help, it is greatly appreciated.

Brad




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
Dim PeopleNotEmailed As String
Dim KeepSending As String
Dim NurseryNames As String, SowNames As String

On Error GoTo ErrorLine

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("tblEMailTo")
rst.MoveFirst

Do Until rst.EOF

x = 0
y = 0

ServRepName = rst![EMailToServiceRep]
ServRepEMail = rst![EMailAddress]
MsgBox ServRepName & " is the current service rep,
and " & ServRepEMail & " is the address that the selected
information will be sent to."

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;",
dbOpenForwardOnly)

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;",
dbOpenForwardOnly)

If Not rst1.EOF Then rst1.MoveFirst
Do Until rst1.EOF
x = x + 1
NurseryNames = NurseryNames & ", " & rst1!
[EMailToNurseryBarnName]
rst1.MoveNext
Loop
MsgBox x & " Nursery farms were chosen. They
are " & Chr(13) & Chr(13) & NurseryNames
NurseryNames = ""

If Not rst2.EOF Then rst2.MoveFirst
Do Until rst2.EOF
y = y + 1
SowNames = SowNames & ", " & rst2!
[EMailToSowBarnName]
rst2.MoveNext
Loop
MsgBox y & " Sow farms were chosen. They are " &
Chr(13) & Chr(13) & SowNames
SowNames = ""

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, , False
DoCmd.SendObject
acSendReport, "rptqryServRepWeeklyReportSOW",
acFormatRTF, ServRepEMail, , , "Easywean Inspection Forms
Report: " & Me.StartDate & " To " & Me.EndDate, , False
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, , False
End If
If y > 0 And x = 0 Then
OpenSmall = True

DoCmd.OpenReport "rptqryServRepWeeklyReportSOW",
acViewPreview
DoCmd.SendObject
acSendReport, "rptqryServRepWeeklyReportSOW",
acFormatRTF, ServRepEMail, , , "Easywean Inspection Forms
Report: " & Me.StartDate & " To " & Me.EndDate, , False
End If
End If

If x = 0 And y = 0 Then
If PeopleNotEmailed = "" Then
PeopleNotEmailed = ServRepName
Else
PeopleNotEmailed = PeopleNotEmailed
& ", " & ServRepName
End If
End If
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 = 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
x = 0
y = 0
Resume Next
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 & " " & Err.HelpFile & " " &
Err.HelpContext
GoTo ResumeFromError
End If
End Sub
 
T

TC

Brad

I've stared at your code for a few minutes & I can't see anything clearly
wrong. (And I've started at *lots* of VBA code!)

Here's what I personally would do next.

(1) Build each of the SELECT statements into a string variable & use the
variable in the openrecordset. Then you can debug.print the content of the
string variable; cut that text directly from the debug window; create a new
query; go to the SQL window; and paste the text directly into that window.
This will give you an independent check of whether there is something wrong
with the SELECT statement, that is being hidden when you run it from code.
(Building SELECT statements into strings, is a good general-purpose
technique, precisely so you can print them out for debugging purposes, like
here.)

(2) When you run each of the two queries from step (1), see if you can
manually navigate through each record of each query's datasheet. The error
sounds like data corruption to me, & this might be evident by bad data or
other problems moving through the datasheets.

(3) If nothing comes out in the wash, I would import everything into a new
db, & try again. That because the error sounds like a data corruption fault,
to me.

Once you've fixed the problem, consider storing the two SELECT statements as
saved queries, & using Parameters to pass the values that change at runtime.
That would be a bit more efficient, & would make your code look simpler. It
seperates the complication of the SELECT statements, from the complication
of the VBA code. "Divide & conquor" is the aim, for making code
maintainable.

HTH,
TC


Brad said:
Thanks for reading my question.

My code breaks at the If Not rst2.EOF Then rst2.MoveFirst
line. It passes the If not rst2.EOF part, but errors on
the Then rst2.MoveFirst.

I am getting an Invalid Operation error (err.number =
5003219).
I am not sure why because I do exactly the same thing
with rst1 just prior.

Any suggestions?

Thanks again for the help, it is greatly appreciated.

Brad




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
Dim PeopleNotEmailed As String
Dim KeepSending As String
Dim NurseryNames As String, SowNames As String

On Error GoTo ErrorLine

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("tblEMailTo")
rst.MoveFirst

Do Until rst.EOF

x = 0
y = 0

ServRepName = rst![EMailToServiceRep]
ServRepEMail = rst![EMailAddress]
MsgBox ServRepName & " is the current service rep,
and " & ServRepEMail & " is the address that the selected
information will be sent to."

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;",
dbOpenForwardOnly)

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;",
dbOpenForwardOnly)

If Not rst1.EOF Then rst1.MoveFirst
Do Until rst1.EOF
x = x + 1
NurseryNames = NurseryNames & ", " & rst1!
[EMailToNurseryBarnName]
rst1.MoveNext
Loop
MsgBox x & " Nursery farms were chosen. They
are " & Chr(13) & Chr(13) & NurseryNames
NurseryNames = ""

If Not rst2.EOF Then rst2.MoveFirst
Do Until rst2.EOF
y = y + 1
SowNames = SowNames & ", " & rst2!
[EMailToSowBarnName]
rst2.MoveNext
Loop
MsgBox y & " Sow farms were chosen. They are " &
Chr(13) & Chr(13) & SowNames
SowNames = ""

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, , False
DoCmd.SendObject
acSendReport, "rptqryServRepWeeklyReportSOW",
acFormatRTF, ServRepEMail, , , "Easywean Inspection Forms
Report: " & Me.StartDate & " To " & Me.EndDate, , False
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, , False
End If
If y > 0 And x = 0 Then
OpenSmall = True

DoCmd.OpenReport "rptqryServRepWeeklyReportSOW",
acViewPreview
DoCmd.SendObject
acSendReport, "rptqryServRepWeeklyReportSOW",
acFormatRTF, ServRepEMail, , , "Easywean Inspection Forms
Report: " & Me.StartDate & " To " & Me.EndDate, , False
End If
End If

If x = 0 And y = 0 Then
If PeopleNotEmailed = "" Then
PeopleNotEmailed = ServRepName
Else
PeopleNotEmailed = PeopleNotEmailed
& ", " & ServRepName
End If
End If
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 = 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
x = 0
y = 0
Resume Next
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 & " " & Err.HelpFile & " " &
Err.HelpContext
GoTo ResumeFromError
End If
End Sub
Click to expand...
 
B

Brad

Hi TC,

Thanks for the tips,

I figured out my error.

I had both rst1 and rst2 as dbOpenForwardOnly. If it is
set to this, it can't move back to the first record.

Thanks again for taking a look at my problem.

Brad
-----Original Message-----
Brad

I've stared at your code for a few minutes & I can't see anything clearly
wrong. (And I've started at *lots* of VBA code!)

Here's what I personally would do next.

(1) Build each of the SELECT statements into a string variable & use the
variable in the openrecordset. Then you can debug.print the content of the
string variable; cut that text directly from the debug window; create a new
query; go to the SQL window; and paste the text directly into that window.
This will give you an independent check of whether there is something wrong
with the SELECT statement, that is being hidden when you run it from code.
(Building SELECT statements into strings, is a good general-purpose
technique, precisely so you can print them out for debugging purposes, like
here.)

(2) When you run each of the two queries from step (1), see if you can
manually navigate through each record of each query's datasheet. The error
sounds like data corruption to me, & this might be evident by bad data or
other problems moving through the datasheets.

(3) If nothing comes out in the wash, I would import everything into a new
db, & try again. That because the error sounds like a data corruption fault,
to me.

Once you've fixed the problem, consider storing the two SELECT statements as
saved queries, & using Parameters to pass the values that change at runtime.
That would be a bit more efficient, & would make your code look simpler. It
seperates the complication of the SELECT statements, from the complication
of the VBA code. "Divide & conquor" is the aim, for making code
maintainable.

HTH,
TC


Thanks for reading my question.

My code breaks at the If Not rst2.EOF Then rst2.MoveFirst
line. It passes the If not rst2.EOF part, but errors on
the Then rst2.MoveFirst.

I am getting an Invalid Operation error (err.number =
5003219).
I am not sure why because I do exactly the same thing
with rst1 just prior.

Any suggestions?

Thanks again for the help, it is greatly appreciated.

Brad




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
Dim PeopleNotEmailed As String
Dim KeepSending As String
Dim NurseryNames As String, SowNames As String

On Error GoTo ErrorLine

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("tblEMailTo")
rst.MoveFirst

Do Until rst.EOF

x = 0
y = 0

ServRepName = rst![EMailToServiceRep]
ServRepEMail = rst![EMailAddress]
MsgBox ServRepName & " is the current service rep,
and " & ServRepEMail & " is the address that the selected
information will be sent to."

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;",
dbOpenForwardOnly)

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;",
dbOpenForwardOnly)

If Not rst1.EOF Then rst1.MoveFirst
Do Until rst1.EOF
x = x + 1
NurseryNames = NurseryNames & ", " & rst1!
[EMailToNurseryBarnName]
rst1.MoveNext
Loop
MsgBox x & " Nursery farms were chosen. They
are " & Chr(13) & Chr(13) & NurseryNames
NurseryNames = ""

If Not rst2.EOF Then rst2.MoveFirst
Do Until rst2.EOF
y = y + 1
SowNames = SowNames & ", " & rst2!
[EMailToSowBarnName]
rst2.MoveNext
Loop
MsgBox y & " Sow farms were chosen. They are " &
Chr(13) & Chr(13) & SowNames
SowNames = ""

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, , False
DoCmd.SendObject
acSendReport, "rptqryServRepWeeklyReportSOW",
acFormatRTF, ServRepEMail, , , "Easywean Inspection Forms
Report: " & Me.StartDate & " To " & Me.EndDate, , False
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, , False
End If
If y > 0 And x = 0 Then
OpenSmall = True

DoCmd.OpenReport "rptqryServRepWeeklyReportSOW",
acViewPreview
DoCmd.SendObject
acSendReport, "rptqryServRepWeeklyReportSOW",
acFormatRTF, ServRepEMail, , , "Easywean Inspection Forms
Report: " & Me.StartDate & " To " & Me.EndDate, , False
End If
End If

If x = 0 And y = 0 Then
If PeopleNotEmailed = "" Then
PeopleNotEmailed = ServRepName
Else
PeopleNotEmailed = PeopleNotEmailed
& ", " & ServRepName
End If
End If
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 = 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
x = 0
y = 0
Resume Next
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 & " " & Err.HelpFile & " " &
Err.HelpContext
GoTo ResumeFromError
End If
End Sub
Click to expand...


.
Click to expand...
 
T

TC

Well done. I clearly didn't stare hard enough!

TC


Brad said:
Hi TC,

Thanks for the tips,

I figured out my error.

I had both rst1 and rst2 as dbOpenForwardOnly. If it is
set to this, it can't move back to the first record.

Thanks again for taking a look at my problem.

Brad
-----Original Message-----
Brad

I've stared at your code for a few minutes & I can't see anything clearly
wrong. (And I've started at *lots* of VBA code!)

Here's what I personally would do next.

(1) Build each of the SELECT statements into a string variable & use the
variable in the openrecordset. Then you can debug.print the content of the
string variable; cut that text directly from the debug window; create a new
query; go to the SQL window; and paste the text directly into that window.
This will give you an independent check of whether there is something wrong
with the SELECT statement, that is being hidden when you run it from code.
(Building SELECT statements into strings, is a good general-purpose
technique, precisely so you can print them out for debugging purposes, like
here.)

(2) When you run each of the two queries from step (1), see if you can
manually navigate through each record of each query's datasheet. The error
sounds like data corruption to me, & this might be evident by bad data or
other problems moving through the datasheets.

(3) If nothing comes out in the wash, I would import everything into a new
db, & try again. That because the error sounds like a data corruption fault,
to me.

Once you've fixed the problem, consider storing the two SELECT statements as
saved queries, & using Parameters to pass the values that change at runtime.
That would be a bit more efficient, & would make your code look simpler. It
seperates the complication of the SELECT statements, from the complication
of the VBA code. "Divide & conquor" is the aim, for making code
maintainable.

HTH,
TC


Thanks for reading my question.

My code breaks at the If Not rst2.EOF Then rst2.MoveFirst
line. It passes the If not rst2.EOF part, but errors on
the Then rst2.MoveFirst.

I am getting an Invalid Operation error (err.number =
5003219).
I am not sure why because I do exactly the same thing
with rst1 just prior.

Any suggestions?

Thanks again for the help, it is greatly appreciated.

Brad




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
Dim PeopleNotEmailed As String
Dim KeepSending As String
Dim NurseryNames As String, SowNames As String

On Error GoTo ErrorLine

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("tblEMailTo")
rst.MoveFirst

Do Until rst.EOF

x = 0
y = 0

ServRepName = rst![EMailToServiceRep]
ServRepEMail = rst![EMailAddress]
MsgBox ServRepName & " is the current service rep,
and " & ServRepEMail & " is the address that the selected
information will be sent to."

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;",
dbOpenForwardOnly)

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;",
dbOpenForwardOnly)

If Not rst1.EOF Then rst1.MoveFirst
Do Until rst1.EOF
x = x + 1
NurseryNames = NurseryNames & ", " & rst1!
[EMailToNurseryBarnName]
rst1.MoveNext
Loop
MsgBox x & " Nursery farms were chosen. They
are " & Chr(13) & Chr(13) & NurseryNames
NurseryNames = ""

If Not rst2.EOF Then rst2.MoveFirst
Do Until rst2.EOF
y = y + 1
SowNames = SowNames & ", " & rst2!
[EMailToSowBarnName]
rst2.MoveNext
Loop
MsgBox y & " Sow farms were chosen. They are " &
Chr(13) & Chr(13) & SowNames
SowNames = ""

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, , False
DoCmd.SendObject
acSendReport, "rptqryServRepWeeklyReportSOW",
acFormatRTF, ServRepEMail, , , "Easywean Inspection Forms
Report: " & Me.StartDate & " To " & Me.EndDate, , False
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, , False
End If
If y > 0 And x = 0 Then
OpenSmall = True

DoCmd.OpenReport "rptqryServRepWeeklyReportSOW",
acViewPreview
DoCmd.SendObject
acSendReport, "rptqryServRepWeeklyReportSOW",
acFormatRTF, ServRepEMail, , , "Easywean Inspection Forms
Report: " & Me.StartDate & " To " & Me.EndDate, , False
End If
End If

If x = 0 And y = 0 Then
If PeopleNotEmailed = "" Then
PeopleNotEmailed = ServRepName
Else
PeopleNotEmailed = PeopleNotEmailed
& ", " & ServRepName
End If
End If
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 = 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
x = 0
y = 0
Resume Next
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 & " " & Err.HelpFile & " " &
Err.HelpContext
GoTo ResumeFromError
End If
End Sub
Click to expand...


.
Click to expand...
Click to expand...
 

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

Another Error Handling Problem 1
Error Handling Problems 4
Excel hangs 1

Top