Results 1 to 7 of 7
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Warrington, Cheshire, England
    Posts
    712
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Zero records for report (2003)

    I have a query which can return an empty datasheet view (because the query criteria has eliminated all the records)
    I want to produce a report for the end user which either :
    a) displays the data which did get through and so requires their attention
    or
    [img]/forums/images/smilies/cool.gif[/img] displays a message 'nothing to worry about' (or somesuch text) if there is no data to display.
    I looked at the Access help for Count which said I could put a text box in the report header with the datasource set to =Count(*) which I was going to test for zero but it only returns #Error.

    What's needed to make the report work in the way I want, please?
    Thanks
    Silverback
    Silverback

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Zero records for report (2003)

    You can put a text box in the report header or page header with a control source like this:
    <code>
    =IIf([HasData],Null,"Sorry, we have no data today")
    </code>
    If there are data, the text box will be empty, but if there are no data, the text "Sorry, we have no data today" (or whatever you specify) will be displayed.

  3. #3
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Zero records for report (2003)

    The other way to handle this is to use the "On No Data" event for the report.

    Private Sub Report_NoData(Cancel As Integer)
    MsgBox "There is no data to display"
    Cancel = True
    End Sub

    But this generates error 2501 in the code that tries to open the report, so you also need to handle that error:

    Private Sub cmdOpenReport_Click()
    Dim strDocName as string
    On Error GoTo Err_cmdOpenReport_Click
    strDocName = "rptsomething"
    DoCmd.OpenReport stDocName, acPreview

    Exit_cmdOpenReport_Click:
    Exit Sub

    Err_cmdOpenReport_Click:
    If Err.Number = 2501 Then
    Resume Exit_cmdOpenReport_Click
    Else
    MsgBox Err.Description
    Resume Exit_cmdOpenReport_Click
    End If

    End Sub
    Regards
    John



  4. #4
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Zero records for report (2003)

    What you've done is fine. The only thing I do slightly differently is that I have an error sub I call that handles checking for that 2501 error and for displaying a msgbox with the error. So my code would look like this:

    Err_cmdOpenReport_Click:
    ErrorHandler err.description
    Resume Exit_cmdOpenReport_Click

    Not better, just easier to type! <img src=/S/smile.gif border=0 alt=smile width=15 height=15>
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  5. #5
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Warrington, Cheshire, England
    Posts
    712
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Zero records for report (2003)

    Once again - many thanks to the loungers.
    I discovered the OnNoData solution via Access Help on reports and got it working last night. (Hans' solution has been put into the brainbox in case it's needed in the future - thanks for that.)
    However, I am worried about this 2501 error - this is not happening on my PC; everything works fine.
    Under what circumstances does error 2501 occur?
    Should I put this error trap into the DB as a 'belt and braces' in case it fails in the future?
    Thanks
    Silverback
    Silverback

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Zero records for report (2003)

    If you open the report by itself, the 2501 error won't occur, even if opening the report is canceled because there are no data.
    If you open the report using code, i.e. DoCmd.OpenReport, canceling it should raise error 2501. If you don't get that, you may already have error handling that suppresses the error message.

  7. #7
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Warrington, Cheshire, England
    Posts
    712
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Zero records for report (2003)

    Thanks to Hans, John and Mark.
    Everything is now working, including the 2501 error catch code, because as a result of posting posting this, I was advised to rewrite a macro as a VBA routine which then meant that the 2501 error happened.
    Thanks to everyone for their help; the lounge is a priceless asset and very much appreciated.
    Silverback
    Silverback

Posting Permissions

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