How to display a message "form" if report has no records

[Guest]

I hope I can explain my need clearly:

I have a query that returns products sold by companies. I have a report
that displays these results. I run the report by clicking a command button
on a main "switchboard"-like form. The command button runs a Macro that has
a Where condition, so the currently displayed CompanyID equals the CompanyID
on the report, so the report is run only for the currently displayed company.

If the report runs for a company that has no products, the report is ugly
(just some headings with a couple of #Error messages sprinkled around).

I would like to create a Macro or filter or statement that will first check
to see if there are actually any records to display in the report. If there
are, then run the report as usual. If there are no records associated with
that CompanyID, then I would like to display a popup form that says, "There
are no products associated with this Company" , *instead of* displaying the
Report.

Any ideas? Do I need to modify the Where condition in the macro with an
if,then,else statement? If so, how do I test for the "null"-ness of records?
The filtering is happening on the report level, not the query level.

Thank you in advance for any help anyone might offer,

Steve Vincent
(e-mail address removed)

 

[Van T. Dinh]

There is a NoData Event on the Report that you can use to run your code /
Macro. This Event will fire if the Report's DatSource is empty.

 

[fredg]

I hope I can explain my need clearly:

I have a query that returns products sold by companies. I have a report
that displays these results. I run the report by clicking a command button
on a main "switchboard"-like form. The command button runs a Macro that has
a Where condition, so the currently displayed CompanyID equals the CompanyID
on the report, so the report is run only for the currently displayed company.

If the report runs for a company that has no products, the report is ugly
(just some headings with a couple of #Error messages sprinkled around).

I would like to create a Macro or filter or statement that will first check
to see if there are actually any records to display in the report. If there
are, then run the report as usual. If there are no records associated with
that CompanyID, then I would like to display a popup form that says, "There
are no products associated with this Company" , *instead of* displaying the
Report.

Any ideas? Do I need to modify the Where condition in the macro with an
if,then,else statement? If so, how do I test for the "null"-ness of records?
The filtering is happening on the report level, not the query level.

Thank you in advance for any help anyone might offer,

Steve Vincent
(e-mail address removed)

Code the Report's OnNoData event:

MsgBox "There are no products associated with this Company."
Cancel = True

Note: the above will generate error 2501. You will need to trap that
error in the switchboard command event code that opens the report.
You can NOT use error handling if you use a macro. You will need to
use code.

On Error GoTo Err_Handler

DoCmd.OpenReport "ReportName" , acViewPreview, , "[CompanyID] = " &
Me![CompanyID]

Exit_This_Sub:
Exit Sub
Err_Handler:
If err = 2501 then
Else
MsgBox "Error #: " & Err.Number & " " & Err.Description
End If
Resume Exit_This_Sub

The above code assumes [CompanyID] is a Number datatype field.
Change "ReportName" to the actual name of your report.

 

[Stuart]

Hi

I have a similar problem, only I have already tried the on no data
Event Procedure, I have entered code to notify the user that there is
no data for the period selected, the date range for the period is
entered via 2 text boxes on a form I designed for selecting my reports.

I have done this a number of times with other reports on the same form,
they work fine, this new report is diffirent in that it is a graph
rather than tabular data, why that should make any diffirence I dont
know!

The code I am using which as I say has worked elswhere in the
application is:

MsgBox "There are no records to display for the selected date period! "
& Format(Forms![frmReports]![txtFrom], "dd mmm yyyy") & " to " &
Format(Forms![frmReports]![txtTo], "dd mmm yyyy"), vbOKOnly, "Procedure
Report"

Cancel = True

DoCmd.Close acForm, "rptChartBypassByMonth"

Any ideas?

thanks

Stuart

I hope I can explain my need clearly:

I have a query that returns products sold by companies. I have a report
that displays these results. I run the report by clicking a command button
on a main "switchboard"-like form. The command button runs a Macro that has
a Where condition, so the currently displayed CompanyID equals the CompanyID
on the report, so the report is run only for the currently displayed company.

If the report runs for a company that has no products, the report is ugly
(just some headings with a couple of #Error messages sprinkled around).

I would like to create a Macro or filter or statement that will first check
to see if there are actually any records to display in the report. If there
are, then run the report as usual. If there are no records associated with
that CompanyID, then I would like to display a popup form that says, "There
are no products associated with this Company" , *instead of* displaying the
Report.

Any ideas? Do I need to modify the Where condition in the macro with an
if,then,else statement? If so, how do I test for the "null"-ness of records?
The filtering is happening on the report level, not the query level.

Thank you in advance for any help anyone might offer,

Steve Vincent
(e-mail address removed)

Code the Report's OnNoData event:

MsgBox "There are no products associated with this Company."
Cancel = True

Note: the above will generate error 2501. You will need to trap that
error in the switchboard command event code that opens the report.
You can NOT use error handling if you use a macro. You will need to
use code.

On Error GoTo Err_Handler

DoCmd.OpenReport "ReportName" , acViewPreview, , "[CompanyID] = " &
Me![CompanyID]

Exit_This_Sub:
Exit Sub
Err_Handler:
If err = 2501 then
Else
MsgBox "Error #: " & Err.Number & " " & Err.Description
End If
Resume Exit_This_Sub

The above code assumes [CompanyID] is a Number datatype field.
Change "ReportName" to the actual name of your report.

 

[Rick Brandt]

Hi

I have a similar problem, only I have already tried the on no data
Event Procedure, I have entered code to notify the user that there is
no data for the period selected, the date range for the period is
entered via 2 text boxes on a form I designed for selecting my
reports.

I have done this a number of times with other reports on the same
form, they work fine, this new report is diffirent in that it is a
graph rather than tabular data, why that should make any diffirence I
dont know!

The On NoData event is referring to the data for the report. The data in your
chart is not the report's data. In this aspect it is similar to a subreport and
has its own data source separate from the report. The report is only a
container for the chart so the NoData event is no good.

You would have to use a domain function or Recordset that uses the same query as
your chart and test to see if that returns any rows before you open the report
(or possibly in the Report's Open event which you can cancel).

 

[Stuart]

Hi Rick

Clearly the code in the report was not firing as I could not even
walkthrough the code as I can usualy do to track the problem down, your
suggestion gives me a good pointer to where the problem is, many thanks
for that.

Stuart

 

[fredg]

Hi

I have a similar problem, only I have already tried the on no data
Event Procedure, I have entered code to notify the user that there is
no data for the period selected, the date range for the period is
entered via 2 text boxes on a form I designed for selecting my reports.

I have done this a number of times with other reports on the same form,
they work fine, this new report is diffirent in that it is a graph
rather than tabular data, why that should make any diffirence I dont
know!

The code I am using which as I say has worked elswhere in the
application is:

MsgBox "There are no records to display for the selected date period! "
& Format(Forms![frmReports]![txtFrom], "dd mmm yyyy") & " to " &
Format(Forms![frmReports]![txtTo], "dd mmm yyyy"), vbOKOnly, "Procedure
Report"

Cancel = True

DoCmd.Close acForm, "rptChartBypassByMonth"

Any ideas?

thanks

Stuart

I hope I can explain my need clearly:

I have a query that returns products sold by companies. I have a report
that displays these results. I run the report by clicking a command button
on a main "switchboard"-like form. The command button runs a Macro that has
a Where condition, so the currently displayed CompanyID equals the CompanyID
on the report, so the report is run only for the currently displayed company.

If the report runs for a company that has no products, the report is ugly
(just some headings with a couple of #Error messages sprinkled around).

I would like to create a Macro or filter or statement that will first check
to see if there are actually any records to display in the report. If there
are, then run the report as usual. If there are no records associated with
that CompanyID, then I would like to display a popup form that says, "There
are no products associated with this Company" , *instead of* displaying the
Report.

Any ideas? Do I need to modify the Where condition in the macro with an
if,then,else statement? If so, how do I test for the "null"-ness of records?
The filtering is happening on the report level, not the query level.

Thank you in advance for any help anyone might offer,

Steve Vincent
(e-mail address removed)

Code the Report's OnNoData event:

MsgBox "There are no products associated with this Company."
Cancel = True

Note: the above will generate error 2501. You will need to trap that
error in the switchboard command event code that opens the report.
You can NOT use error handling if you use a macro. You will need to
use code.

On Error GoTo Err_Handler

DoCmd.OpenReport "ReportName" , acViewPreview, , "[CompanyID] = " &
Me![CompanyID]

Exit_This_Sub:
Exit Sub
Err_Handler:
If err = 2501 then
Else
MsgBox "Error #: " & Err.Number & " " & Err.Description
End If
Resume Exit_This_Sub

The above code assumes [CompanyID] is a Number datatype field.
Change "ReportName" to the actual name of your report.

What is this line supposed to do?

DoCmd.Close acForm, "rptChartBypassByMonth"<

Is "rptChartBypassByMonth" the name of a form (I ask because it
starts with 'rpt', not 'frm')?
Or is it the name of this report?
If it is the name of this report, cancel = true is what closes it, so
you do not need that line. (Besides, your code calls for closing a
form of that name, not a report.)

If it is the name of a form to be closed, (and not the one that opened
the report) , try closing it in the Error Handler of the form event
used to open the report.

If Err = 2501 Then
DoCmd.Close acForm, "rptChartBypassByMonth"
Else
MsgBox etc....
End If
Resume Exit_This_Sub

Does that make a difference?

 

[Stuart]

Hi

Yes, that is a typo it should be acReport, but if I understand you I
dont need that as Cancel = True closes the report?

Stuart

Hi

I have a similar problem, only I have already tried the on no data
Event Procedure, I have entered code to notify the user that there is
no data for the period selected, the date range for the period is
entered via 2 text boxes on a form I designed for selecting my reports.

I have done this a number of times with other reports on the same form,
they work fine, this new report is diffirent in that it is a graph
rather than tabular data, why that should make any diffirence I dont
know!

The code I am using which as I say has worked elswhere in the
application is:

MsgBox "There are no records to display for the selected date period! "
& Format(Forms![frmReports]![txtFrom], "dd mmm yyyy") & " to " &
Format(Forms![frmReports]![txtTo], "dd mmm yyyy"), vbOKOnly, "Procedure
Report"

Cancel = True

DoCmd.Close acForm, "rptChartBypassByMonth"

Any ideas?

thanks

Stuart

I have a query that returns products sold by companies. I have a report
that displays these results. I run the report by clicking a command button
on a main "switchboard"-like form. The command button runs a Macro that has
a Where condition, so the currently displayed CompanyID equals the CompanyID
on the report, so the report is run only for the currently displayed company.
If the report runs for a company that has no products, the report is ugly
(just some headings with a couple of #Error messages sprinkled around).
I would like to create a Macro or filter or statement that will first check
to see if there are actually any records to display in the report. If there
are, then run the report as usual. If there are no records associated with
that CompanyID, then I would like to display a popup form that says, "There
are no products associated with this Company" , *instead of* displaying the
Report.
Any ideas? Do I need to modify the Where condition in the macro with an
if,then,else statement? If so, how do I test for the "null"-ness of records?
The filtering is happening on the report level, not the query level.
Thank you in advance for any help anyone might offer,
Steve Vincent
(e-mail address removed)
Code the Report's OnNoData event:
MsgBox "There are no products associated with this Company."
Cancel = True
Note: the above will generate error 2501. You will need to trap that
error in the switchboard command event code that opens the report.
You can NOT use error handling if you use a macro. You will need to
use code.
On Error GoTo Err_Handler
DoCmd.OpenReport "ReportName" , acViewPreview, , "[CompanyID] = " &
Me![CompanyID]
Exit_This_Sub:
Exit Sub
Err_Handler:
If err = 2501 then
Else
MsgBox "Error #: " & Err.Number & " " & Err.Description
End If
Resume Exit_This_Sub
The above code assumes [CompanyID] is a Number datatype field.
Change "ReportName" to the actual name of your report.

starts with 'rpt', not 'frm')?
Or is it the name of this report?
If it is the name of this report, cancel = true is what closes it, so
you do not need that line. (Besides, your code calls for closing a
form of that name, not a report.)

If it is the name of a form to be closed, (and not the one that opened
the report) , try closing it in the Error Handler of the form event
used to open the report.

If Err = 2501 Then
DoCmd.Close acForm, "rptChartBypassByMonth"
Else
MsgBox etc....
End If
Resume Exit_This_Sub

Does that make a difference?

 

[Van T. Dinh]

Confirmed. The statement:

Cancel = True

in the Report_Open or Report_NoData Event cancels the opening of the Report
and therefore, it is not there to be closed.

 

[Stuart]

Thanks for that.

Stuart

Confirmed. The statement:

Cancel = True

in the Report_Open or Report_NoData Event cancels the opening of the Report
and therefore, it is not there to be closed.

 

[Guest]

Thank you all! Fred, your advice worked perfectly with just the first 2
lines of code (I did not need the error-handling bit).
Thanks again!
Steve

I hope I can explain my need clearly:

I have a query that returns products sold by companies. I have a report
that displays these results. I run the report by clicking a command button
on a main "switchboard"-like form. The command button runs a Macro that has
a Where condition, so the currently displayed CompanyID equals the CompanyID
on the report, so the report is run only for the currently displayed company.

If the report runs for a company that has no products, the report is ugly
(just some headings with a couple of #Error messages sprinkled around).

I would like to create a Macro or filter or statement that will first check
to see if there are actually any records to display in the report. If there
are, then run the report as usual. If there are no records associated with
that CompanyID, then I would like to display a popup form that says, "There
are no products associated with this Company" , *instead of* displaying the
Report.

Any ideas? Do I need to modify the Where condition in the macro with an
if,then,else statement? If so, how do I test for the "null"-ness of records?
The filtering is happening on the report level, not the query level.

Thank you in advance for any help anyone might offer,

Steve Vincent
(e-mail address removed)

Code the Report's OnNoData event:

MsgBox "There are no products associated with this Company."
Cancel = True

Note: the above will generate error 2501. You will need to trap that
error in the switchboard command event code that opens the report.
You can NOT use error handling if you use a macro. You will need to
use code.

On Error GoTo Err_Handler

DoCmd.OpenReport "ReportName" , acViewPreview, , "[CompanyID] = " &
Me![CompanyID]

Exit_This_Sub:
Exit Sub
Err_Handler:
If err = 2501 then
Else
MsgBox "Error #: " & Err.Number & " " & Err.Description
End If
Resume Exit_This_Sub

The above code assumes [CompanyID] is a Number datatype field.
Change "ReportName" to the actual name of your report.