Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Sunrise, Florida
    Thanked 0 Times in 0 Posts

    Report with no records (Access 2K)

    My database tracks the investigations that have been assigned to me. When the investigation is over and I get my supervisor's approval to close, I enter the date of his approval on a "date" field. Once in a while a run a report that basically tells me which records do not have an approval date, so I can follow up with my supervisor. The report, which is run by a macro, uses a query as the source. Many times there are no pending records. How can I set up the report so that it does not execute if the query produces no records? Thanks for any assistance!

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Thanked 31 Times in 31 Posts

    Re: Report with no records (Access 2K)

    Open the report in design view.
    Activate the Event tab of the Properties window.
    Select [Event Procedure] in the dropdown list in the On No Data event.
    Click in the ... button to the right of the dropdown arrow.
    Make the code look like this:

    Private Sub Report_NoData(Cancel As Integer)
    MsgBox "Nothing to see here!", vbInformation
    Cancel = True
    End Sub

    If you open the report in code, you'll now get error 2501 if it is canceled for lack of data. To get around this, create an error handler that ignores error 2501, for example in the On Click event of a command button on a form:

    Private Sub cmdReport_Click()
    On Error GoTo ErrHandler

    DoCmd.OpenReport "rptMyReport", acViewPreview
    Exit Sub

    If Err = 2501 Then
    ' Report canceled, ignore
    MsgBox Err.Description, vbExclamation
    End If
    End Sub

  3. #3
    Gold Lounger
    Join Date
    Jun 2001
    Crystal Beach, FL, Florida, USA
    Thanked 40 Times in 39 Posts

    Re: Report with no records (Access 2K)

    I basically do the same thing Hans suggested, but instead of code (which would have to be copied into every report), I made a macro to handle this (one of the few times I use macros). I make the selection from the dropdown in the Report's OnNoData event. It includes a message box that says "Your criteria selection produced an empty report", and then it closes the report.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts