Stop report if there is no data 1

[GenieTR]

Is there a way to stop the report from displaying or printing, when there is no data. I have created a dialog form, where there are two options: first option is
All and the second option is specific which have a combo box for the user to select. On the report there is a nodata event option, the code is:

Private Sub Report_NoData(Cancel As Integer)
On Error Goto Report_NoData_Err
If DCount("*",Me.RecordSource)=O Then
MsgBox "No Data for this report",vbOKOnly,"Sorry No Data"
Cancel = True
End If
Report_NoData_Exit:
Exit Sub
Report_NoData_Err:
MsgBox Error$
Resume Report_NoData_Exit
End Sub

It still display the blank report with only the heading.

Thanks in advance

 

[VicM]

GenieTR:

Try moving your code to the report's OnOpen event. That might do it. Or don't set the cancel option to true; that would imply canceling the NoData event.

Haven't tried either, but sounds like either should work. (However, I've been known to be wrong.)

Hope this helps,

Vic

 

[ajdesalvo]

I belive you do not need to check again that there is no data to report. So change the code to the following:

Private Sub Report_NoData(Cancel As Integer)
On Error Goto Report_NoData_Err

MsgBox "No Data for this report",vbOKOnly,"Sorry No Data"
Cancel = True

Report_NoData_Exit:
Exit Sub
Report_NoData_Err:
MsgBox Error$
Resume Report_NoData_Exit
End Sub


Good luck! Anthony J. DeSalvo
President - ScottTech Software
"Integrating Technology with Business"

 

[GenieTR]

Thanks again for the help

 

[FoxProProgrammer]

I want to do something similar to this, and tried the code in the samples posted here as well as in the Access Help file. After the Cancel occurs, Access displays a Run-time error 2501 The Open Report Action was canceled... The window that pops up has two buttons (Debug and End). Obviously I don't want users to see this message. I tried to disable it with DoCmd.SetWarnings False but that didn't work. Does anyone know how to disable the error, or better yet, code this so the error doesn't occur?

Thanks,
dz
dzaccess@yahoo.com

 

[ajdesalvo]

You could test to see if any records will be returned prior to opening the report.

In the OnClick event for the report:

If Dcount("[SomeField]","ReportsRecordSource&quot = 0 then
MsgBox Blah, Blah
Exit Sub
Else
Docmd.OpenReport "YourReport"
End If

Anthony J. DeSalvo
President - ScottTech Software
"Integrating Technology with Business"

 

[FoxProProgrammer]

Thanks for the idea, Anthony. It works great. dz
dzaccess@yahoo.com

 

[GenieTR]

Private Sub cmdButton_Click()
On Error GoTo ErrorButton

DoCmd.OpenReport "rptName", acViewPreview

ExitButton:
Exit Sub

ErrorButton:

' This traps the Output to Error message
If Err = 2501 Then
Resume Exit_cmdButton

Else
MsgBox Err.Description
Resume ExitButton

End If

End Sub

This will take care of the 2501 error message.

 

[FoxProProgrammer]

Thanks, GenieTR. I'll keep that in mind if I want to trap an error in the future. dz
dzaccess@yahoo.com