Checking if no records in report or query (1 Viewer)

[helent24]

Hi,

I'm writing a macro to automatically print a report. I don't want to print the report if there are no records in it.

How can I get the macro to check the report or underlying query, and print only if there are records?

I know I should be able to use the 'Where Condition' field in the macro design view, but can't work out what syntax to use...

Thanks, Helen

 

[pbaldy]

The most common method is to use the No Data event of the report, using

Cancel = True

in that event. You would also need to trap for error 2501 in the code that opens the report.

 

[helent24]

Thanks Paul, with a bit of fiddling, I have this worked out.

(I'm very much a self-taught, work it out as I go, Access user, so sorry if I'm asking silly questions...)

 

[helent24]

Oops, I answered too soon! Still more questions...

The macro I am trying to set up prints a report, then brings up a dialog box (form) checking if the report printed correctly. If the user clicks yes, it runs an update query.

With the report amended as per Paul's suggestion above, the macro no longer prints the report when there is no data, but it still follows the second step of asking the user if printed ok.

Basically, the two steps of the macro are:
OpenReport
OpenForm

How can I stop it from proceeded to step 2, when the report has no data and therefore does not open?

Cheers,

 

[Velosai]

I presume you are firing the macro off on the on_Click event of a button?

If so what I would suggest is modify the code for the on click event to something like this

Open a recordset against the query of the report
If the recordsets RecordCount > 0 then open the report and perform update query
Else display an error message saying there arn't any records to display

 

[pbaldy]

If you use code instead of a macro you could probably just let the error handler get you around the second step. As Velosai has suggested, you could also use a recordset or DCount to test the underlying query, and only proceed if there are records.

 

[Velosai]

I would personally use recordsets. Even though they have a couple additional layers to them (eg defining varibles), they are quicker than DCount and when you start using them with Forms, are a lot more versatile and more powerful.

 

[helent24]

Thanks for your answers. I'm afraid I... couldn't work out how to do this... However, I've worked out another option, so it's all good!

 

[gemma-the-husky]

i'm not sure if there are limitations with macros in this area

its things like this that persuade users to switch to code, as anything you can do in a macro, you can do in code, but not necessarily vice versa

the only exceptions to this are, I believe

autokeys macros (which can be quite useful)
autoexec macro (although this can be worked around)