Printint report with no data

[Aubs010]

on a switchboard form, I have a button that when pressed, will print each report out, one at a time, in order.

The third to last report is dependant on data being present.

I have an event when the report is opened with no data:

Private Sub Report_NoData(Cancel As Integer)
On Error GoTo ErrorNoData
    MsgBox "There is no data available to generate this report", vbOKOnly, ""
    Cancel = True
ExitNoData:
    Exit Sub
ErrorNoData:
    MsgBox Err.Description
    Resume ExitNoData
End Sub
[code]

The problem is, I use the following to print the report:

[code]
DoCmd.SetWarnings False
    DoCmd.OpenQuery "quyElementsNonCompliantCreateTable"
    DoCmd.OpenQuery "quyElementsNonCompliantDeleteRedundentZZs"
DoCmd.SetWarnings True

stDocName = "rptElements-NonCompliant"
MsgBox "Err: " & Err & vbcr & "Desc: " & Err.Description
DoCmd.OpenReport stDocName, acNormal

(the queries need to be run in order to compile the data)

The printing dialog (with the cancel button) comes up:

"Now Printing..."

Then an error comes up, that I trap using "MsgBox" code above:

Err: 13
Desc: Type mismatch

Then my message saying:

"There is no data available to generate this report"

then a VB message:

Run-time error '2501':
The OpenReport action was canceled.
[End] [Debug]

Does anyone have any suggestions as to how I can get it to MsgBox the warning about no data BEFORE anything prints, and then halting everything?

Or any other suggestions that may help!

Thanks in advance,



Aubs

 

[chickey]

This is how I call a report and trap (and discard) the
no data error Access throws up:

DoCmd.OpenReport v_Report, v_Options
        
'Don't display error message twice (i.e. report already displays it)
If Err Then
   vErrNo = Err.Number
   vErrDesc = Err.Description
   If vErrNo = 2501 Then
       'Cancelled because of no data
       Exit Sub
   Else
       Err.Raise vErrNo, , vErrDesc
   End If
End If

Reply if you still have problems.

Regards...

 

[Aubs010]

Thanks for your reply,

What I think is causing the problem is the line:

DoCmd.OpenReport stDocName, acNormal

after where I have shown the msgbox line.

In any case, ignoring the above, is there an easy way that I can get access to check if a specific report (Report 4) has data, and if it does, print report 1, 2, 3, 4, 5, 6

If it doesnt have data, then nothing gets printed.

Thanks again in advance!



Aubs

 

[chickey]

Only way I am aware of checking this is to either run
the report and check for nodata as you are doing or run
the report's underlying query and then check for its
recordcount.

If you are going to do the second method, and you are
running a complicated query, then you will essentially be
running it twice (once to check the recordcount and then
again to run the report). Obviously, this is going to
majorly impact on the time required. Caching will help
but will not be as fast as only running it once.

So, the easiest method would be to continue doing it the
way you are specifying above and use the code I outlined
to catch and discard the nodata error.

Regards...

 

[Aubs010]

Yep, I see what you mean.

Okies, thanks for your help, I may, oneday get it to check the data first then print everything, or not as the case may be!

Thanks v.much for your help Chickey, very much appreciated.



Aubs

 

[jdegeorge]

Chickey

I'm trying to use your code:

'Don't display error message twice (i.e. report already displays it)
If Err Then
vErrNo = Err.Number
vErrDesc = Err.Description
If vErrNo = 2501 Then
'Cancelled because of no data
Exit Sub
Else
Err.Raise vErrNo, , vErrDesc
End If
End If

I'm purposely selecting a report filter where I know the report would have no data. I have a MSGBOX in my report's NO DATA property and it comes up just fine, but I still get the 2501 message from Access. Your code isn't stopping it. Am I missing something?

Jim DeGeorge

 

[jdegeorge]

Chickey

I did a little more searching and found a FAQ that solved this: faq703-1594

Sorry to bother you with this. Thanks!

Jim DeGeorge