Prevent reports printing when no data exists.

**MikeWaring** · 12-11-2010, 06:36 AM

Hi, I have a database that produces our daily sales invoices, shipping labels and product picking lists. We have a seperate report for each method of shipping (eg 2nd class post UK, 1st Class post European, UK Parcelforce etc etc) which enables us to print in batches to different printers.

The invoices are created from reports that in turn take the data from its own underlying query. The problem is that we always have blank invoices printing off when there is no data for that particular type of invoice (example, on a particular day there may be no sales to go by 1st Class European post).
However, we still get a shipping label and invoice with no product or address details, just the parts of the invoice that appear as part of the report header.

I need to prevent this from happening, but I don't know at what level to prevent it. I'm guessing at the query level, ie if no data is found by the query, skip the report.

The whole process above is run by a single macro that opens and runs 16 seperate reports in turn and sends the outputs to the various printers.

I have also thought of running each report by a seperate macro and then include these 16 seperate macros in the main one. Then I could possibly make the main macro skip one of the individual report macros when there's no data in the underlying report / query.

That's the theory, all I need is the wisdom to be able to do it!

If anyone can help I'd really appreciate it. I invite any other methods as well.....
Thanks again in advance
Mike

**alansidman** · 12-11-2010, 09:27 PM

What is the record source for your reports? A query, I hope. If that is the case, can you not make the query set to not return results if a certain field is null? Just a thought

Alan

**MikeWaring** · 12-12-2010, 04:04 AM

Hi Alan, yes each report is based on a query.
However, the main macro still runs the report even if there's no data in the detail section of the report. When this happens, it still prints off the report (which could be either an invoice, or a picking list etc) but all it contains is details like our company name, address etc, which are just label fields on the report and not taken from the query.
So even if the query doesn't run, the main macro still opens and churns out the report(s). In fact the macro doesn't run any of these queries, just the reports, so I'm assuming that when the report is opened it somehow "runs" the query as the reports always show the latest data.

I've read about the "On No Data" option, but unfortunately the Access Bible doesn't explain it that well and so far I've not been able to get it to work, probably due to my inexperience.
Rgds
Mike

**MikeWaring** · 12-13-2010, 08:39 AM

Originally Posted by MikeWaring

Hi Alan, yes each report is based on a query.
However, the main macro still runs the report even if there's no data in the detail section of the report. When this happens, it still prints off the report (which could be either an invoice, or a picking list etc) but all it contains is details like our company name, address etc, which are just label fields on the report and not taken from the query.
So even if the query doesn't run, the main macro still opens and churns out the report(s). In fact the macro doesn't run any of these queries, just the reports, so I'm assuming that when the report is opened it somehow "runs" the query as the reports always show the latest data.

I've read about the "On No Data" option, but unfortunately the Access Bible doesn't explain it that well and so far I've not been able to get it to work, probably due to my inexperience.
Rgds
Mike

Alan, I've managed to get this to work now using conditional actions in the macro, but in doing I would like to have a more useful feature; I would like to somehow prevent the reports running if there is less than say 2 records in the underlying form.

I imagine that this will entail some kind of statement to the effect "if count [Form(Form name)]![Fieldname] <2, stop macro, else run macro and all following steps".

But this is getting quite deep for me now so any advice on how to achieve this would be gratefully received.
Thanks again

**alansidman** · 12-13-2010, 09:15 AM

Firstly, I don't use macros at all. I use VBA and so I cannot answer any questions about Macros. You can convert your macros to VBA.

http://office.microsoft.com/en-us/ac...005186676.aspx

Now as to determining how your reports are generated. Look at the properties for each of the reports. It will list the record source for each report and if in fact it is a query, you will have to open each individual query and adjust the criteria to not print records which have null data.

Are your queries developed in the QBE or are they dependent on a SQL statement in VBA code? If they are dependent on the SQL statement, you will have to adjust the criteria in the SQL statement. If they were developed in the QBE, you will have to adjust the criteria in the QBE. In both cases, you will have to determine which field you want to be the one that determines if there is no data to have Access ignore.

I hope this gets you started in the right direction in resolving your issue. Also, if you are using macros, I would urge you to convert all of them to VBA as you will have more flexibility in adjusting and changing the code for your needs and expectations.

Lastly, I would look at the videos on this web site -- very informative
http://www.datapigtechnologies.com/AccessMain.htm

and this is a great resource site
http://www.databasedev.co.uk/general.html

Alan

**MikeWaring** · 12-13-2010, 10:48 AM

Hi Alan, thanks for your valuable advice.
I have developed the db from scratch but as I'm not versed in VBA, everything has been done using the simple procedures in the various panes (ie queries have been developed mainly from design view, some from the wizards). Likewise I've created all the macros in the design pane.
I am going to deploy the database in its present form as we need to get our processes streamlined urgently, but I'm keeping a copy for further development. I can use this to get to understand VBA and to follow your suggestions re converting the macros to VBA.
Can you suggest a book that may help me on the way with VBA?
I'll close this thread for now and will open another when I've gained more knowledge of VBA.
Once again, thank you for your help - I've just tried to add to your reputation, but it tells me I "have to spread it around more" before I can add further to yours...