ACCESS FEED: [MS_AccessPros] Re: How do you stop the report if the query produces no records

Selasa, 13 September 2011

[MS_AccessPros] Re: How do you stop the report if the query produces no records

Thomas

This involves 2 steps. One will be in your code that opens the report; the other is in the report itself.

The code opening the report from a form:

Private Sub cmdOpenReport_Click()
On Error GoTo err_PROC
DoCmd.OpenReport "rptMyReport", acViewPreview

exit_PROC:
On Error Resume Next
Exit Sub

err_PROC:
Dim strErrMsg As String
Dim lngErrIconBtn As Long

Select Case Err.Number
Case 2501
'Report has no data and is cancelled.
strErrMsg = "No data available for this report."
Case Else
'All other errors.
strErrMsg = "Error " & Err.Number & " (" & Err.Description & ") " _
& "occurred in procedure cmdOpenReport_Click Event"
End Select

lngErrIconBtn = vbOKOnly + vbInformation

MsgBox strErrMsg, lngErrIconBtn

Resume exit_PROC

End Sub

code in the report's NoData event:

Private Sub Report_NoData(Cancel As Integer)
Cancel = -1
End Sub

Regards,
Bill Mosca, Founder - MS_Access_Professionals
http://www.thatlldoit.com
Microsoft Office Access MVP
https://mvp.support.microsoft.com/profile/Bill.Mosca

--- In MS_Access_Professionals@yahoogroups.com, "Thomas" <thomas_lam_us@...> wrote:
>
> Running 2007 Access, how can you turn off printing the report if the query result produce no records? Is there some simple way in the report to stop printing a blank page when no records are return?
>
> Thanks,
> Thomas
>