By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,285 Members | 1,622 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,285 IT Pros & Developers. It's quick & easy.

No Data Stop Report - How to tell VBA to Run Next Report?

P: n/a
Hi -
I have a button that runs 2 reports. If there is no data on the
report, I use the No Data event, and tell the user, and Cancel the
execution of that report.

However, if the first report has no data, the second report doesn't
run. All execution stops.

Is there a way in VBA to go on to the next report after the No Data?

thanks -
Sara

(Code Below)

Case Is = 8

strDocName = "rptEstFreight0ActualNOT0"
strStepErrorMsg = "Tell IT there was a problem with Request
Est 0 Actual Not"

strFile = strDocName & ".snp"
strPathAndFile = strPath & strFile
DoCmd.OutputTo acOutputReport, strDocName, "snapshot format",
_
strPathAndFile

strDocName = "rptImpactofFreightChanges-Range"
strStepErrorMsg = "Tell IT there was a problem with Request
Impact Freight Change by dates"

strFile = strDocName & ".snp"
strPathAndFile = strPath & strFile
DoCmd.OutputTo acOutputReport, strDocName, "snapshot format",
_
strPathAndFile

Report No Data:
Private Sub Report_NoData(Cancel As Integer)
Dim WeekEndDate As Date
Dim WeekStartDate As Date

WeekStartDate = [Forms]![frmPrintPOReports]![getSaleDate]
WeekEndDate = [Forms]![frmPrintPOReports]![getWkMoDate]

'Tell the user there is no data

MsgBox "There are no POs for the Period Between " & _
WeekStartDate & " And " & WeekEndDate & _
" With Estimated Freight 0 and Actual Freight on file"

Cancel = True

End Sub

Oct 6 '06 #1
Share this Question
Share on Google+
3 Replies


P: n/a
Hi Sara,

you could simplify your system a little bit by doing the checks before
openeing the report. You did not describe your scenario with much
detail, but the standard scenario is that you have user form with
buttons for selecting various reports. The report datasource is
established at desing time. In the user form you can check to see if
the datasource has data first. If not, give the user a message that
there is no data for the given parameters. If there is data then

DoCmd.OpenReport RptName, acViewPreview

Or if you have parameters for the report you can do this:

DoCmd.OpenReport RptName, acViewDesign
Report(RptName).InputParameters = "@parm1='x',@parm2='y'"
DoCmd.Close acReport, RptName, acSaveYes
DoCmd.OpenReport RptName, acViewPreview

Rich

*** Sent via Developersdex http://www.developersdex.com ***
Oct 6 '06 #2

P: n/a
sara wrote:
Hi -
I have a button that runs 2 reports. If there is no data on the
report, I use the No Data event, and tell the user, and Cancel the
execution of that report.

However, if the first report has no data, the second report doesn't
run. All execution stops.

Is there a way in VBA to go on to the next report after the No Data?

thanks -
Sara

(Code Below)

Case Is = 8

strDocName = "rptEstFreight0ActualNOT0"
strStepErrorMsg = "Tell IT there was a problem with Request
Est 0 Actual Not"

strFile = strDocName & ".snp"
strPathAndFile = strPath & strFile
DoCmd.OutputTo acOutputReport, strDocName, "snapshot format",
_
strPathAndFile

strDocName = "rptImpactofFreightChanges-Range"
strStepErrorMsg = "Tell IT there was a problem with Request
Impact Freight Change by dates"

strFile = strDocName & ".snp"
strPathAndFile = strPath & strFile
DoCmd.OutputTo acOutputReport, strDocName, "snapshot format",
_
strPathAndFile

Report No Data:
Private Sub Report_NoData(Cancel As Integer)
Dim WeekEndDate As Date
Dim WeekStartDate As Date

WeekStartDate = [Forms]![frmPrintPOReports]![getSaleDate]
WeekEndDate = [Forms]![frmPrintPOReports]![getWkMoDate]

'Tell the user there is no data

MsgBox "There are no POs for the Period Between " & _
WeekStartDate & " And " & WeekEndDate & _
" With Estimated Freight 0 and Actual Freight on file"

Cancel = True

End Sub
If you Cancel, it probably returns back to your error routine with a
2501 error.

If so, see if this works in the error routine
If Err.Number = 2501 then
resume next
else
...process error as usual
Endif

Oct 6 '06 #3

P: n/a
You did it again - that's all I needed! (Of course, I feel like a
genious right now)
And it was really so simple!

thanks so much -
Sara

salad wrote:
sara wrote:
Hi -
I have a button that runs 2 reports. If there is no data on the
report, I use the No Data event, and tell the user, and Cancel the
execution of that report.

However, if the first report has no data, the second report doesn't
run. All execution stops.

Is there a way in VBA to go on to the next report after the No Data?

thanks -
Sara

(Code Below)

Case Is = 8

strDocName = "rptEstFreight0ActualNOT0"
strStepErrorMsg = "Tell IT there was a problem with Request
Est 0 Actual Not"

strFile = strDocName & ".snp"
strPathAndFile = strPath & strFile
DoCmd.OutputTo acOutputReport, strDocName, "snapshot format",
_
strPathAndFile

strDocName = "rptImpactofFreightChanges-Range"
strStepErrorMsg = "Tell IT there was a problem with Request
Impact Freight Change by dates"

strFile = strDocName & ".snp"
strPathAndFile = strPath & strFile
DoCmd.OutputTo acOutputReport, strDocName, "snapshot format",
_
strPathAndFile

Report No Data:
Private Sub Report_NoData(Cancel As Integer)
Dim WeekEndDate As Date
Dim WeekStartDate As Date

WeekStartDate = [Forms]![frmPrintPOReports]![getSaleDate]
WeekEndDate = [Forms]![frmPrintPOReports]![getWkMoDate]

'Tell the user there is no data

MsgBox "There are no POs for the Period Between " & _
WeekStartDate & " And " & WeekEndDate & _
" With Estimated Freight 0 and Actual Freight on file"

Cancel = True

End Sub
If you Cancel, it probably returns back to your error routine with a
2501 error.

If so, see if this works in the error routine
If Err.Number = 2501 then
resume next
else
...process error as usual
Endif
Oct 6 '06 #4

This discussion thread is closed

Replies have been disabled for this discussion.