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

Hey Bill,

This is great. Save me hell of lot of extra VBA & SQL coding to determine if zero record.

Thanks much.

Thomas

--- In MS_Access_Professionals@yahoogroups.com, "Bill Mosca" <wrmosca@...> wrote:
>
> 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
> >
>