Opening a report

Question

I am using a command button to open a report. How can I close the report automatically if there's no data returned by the underlying query? I am getting this message when I clicked the button:

Answer 1

Hi, What does your code look like? Perhaps you can check first if the query will return any records before opening the report or use Open event of the report and use Cancel=True if HasData=False. Hope it helps...

Answer 2

Hi Gordon Swan,

you just posted the error message.

try to post the code so that we can provide you suggestion to solve the issue.

I agree with the suggestion given by .theDBguy to check for the data before opening a report.

for further suggestions , please provide more information regarding your issue.

Regards

Deepak

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].

Answer 3

I am using a crosstab query to produce a student grade report. If all the grades are entered the report is produced but if grades are not entered then the error message comes up. I am trying to prevent the Microsoft error message. This is the report:

Answer 4

Hi Gordon,

Can you show us your code just in case we can adjust it to handle the error? Thanks.

Answer 5

I am not using any code to open the report. I am simply using the On Click event of a button (Submit Options) on a form to run a macro.

This is form in Design View:

Answer 6

When the Submit Options button is clicked this is the macro that runs:

Answer 7

Hi Gordon,

Thanks for the additional information. Okay, so like I was saying, you can modify the macro to check first if the form will have any records to show and only open it if there are. For example, it might look something like this:

Some people don't like this approach because it hits the table twice. So, the other idea I mentioned earlier is to use the Report's Open event to check if the report "HasData" and set the Cancel argument to True if we don't want to report to open.

Hope it helps...

Answer 8

I tried modifying the macro but I got an error message. Could you supply me with the code for the second option i.e. using "HasData" Is that another Event? The error message I got looked like this.

Answer 9

Hmm, the name of your query was "rptStudentTermReport_Crosstab?" Are you saying your report and query have the same name?

Just curious...

Answer 10

The problem with using a crosstab query as the RecordSource of a report is that if there is no data to return the columns in the query, the controls in the report bound to those columns cannot reference the column whose name is their ControlSource property.  The columns simply don't exist in the query's result table.  You will then get the error which you've experienced.

The solution to this is to include in the PIVOT clause a value list of all possible column headings as the argument for the IN operator.  This will ensure that all columns are returned regardless of whether there is any data in the base table to return those columns.  The following is a simple example which I produced for another poster here some time ago:

TRANSFORM NZ(SUM(Sales.value),0)
SELECT FORMAT(Sales.SaleDate,"mmmm yyyy") AS [Month],
SUM(value*IIF(salestype In ("Cash","Credit"),1,0)) AS TotalSales
FROM Sales
GROUP BY FORMAT(Sales.SaleDate,"mmmm yyyy"),
YEAR(Sales.SaleDate), MONTH(Sales.SaleDate)
ORDER BY YEAR(Sales.SaleDate), MONTH(Sales.SaleDate)
PIVOT Sales.salestype IN ("Cash","Complimentary","Credit");

I have to confess, however, that I'm puzzled by your use of a crosstab query as the layout of the report you posted does not look like the results of a crosstab query, but of a conventional query which returns one row per subject?

BTW, there are alternatives to a crosstab query, and you'll find an example as an attendances report in StudentLog.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

In this little demo file the attendances report uses one subreport to return the 'column headings' of course names, and another to return each students attendance figures for each course.  As the 'column headings' are entirely data driven, this avoids the usual problem with reports based on crosstab reports which can deliver different column headings depending on the data in the rows in the base tables used by the crosstab query.

---

Ken Sheridan, Stafford, England