Results 1 to 7 of 7
  1. #1
    Paul H's Avatar
    Paul H is offline Expert
    Windows 10 Access 2016
    Join Date
    Sep 2011
    Location
    Richmond, VA
    Posts
    573

    On No Data Error

    I was trying to add a On No Data event to my report with this snippet of code.



    Code:
    Private Sub Report_NoData(Cancel As Integer)
        MsgBox "There is no data for this Trans Type and Date Range."
        Cancel = True
    End Sub
    The report is called by this standard command which worked just fine till I tried to add the On No Data Event.

    Code:
    DoCmd.OpenReport strReportName, acViewPreview
    Specifically I get this error (see picture below) on the Cancel = True line.
    I have many other reports that use this method. Any ideas what's causing this?


    Click image for larger version. 

Name:	On No Data.JPG 
Views:	16 
Size:	16.7 KB 
ID:	45901

  2. #2
    isladogs's Avatar
    isladogs is offline Very idle programmer
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,078
    The error is expected behaviour in the calling form event which tried to open the report.
    You've cancelled the report opening which triggers error 2501

    Add code to the form event to handle that error

    Code:
    Err_Handler:   
        'err 2501 - report no data
        If Err = 2501 Then Exit Sub
    Colin (Mendip Data Systems): Website, email
    Try again. Fail again. Fail better.
    A
    sking for help isn't giving up. Its refusing to give up.

  3. #3
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    2,054
    I've always found this "feature" of access frustrating - you should be able to supress the error if the call is cancelled, or at least have a flag you could set to not raise that specific error.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  4. #4
    isladogs's Avatar
    isladogs is offline Very idle programmer
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,078
    The form error handler I used does suppress the error.
    Colin (Mendip Data Systems): Website, email
    Try again. Fail again. Fail better.
    A
    sking for help isn't giving up. Its refusing to give up.

  5. #5
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    2,054
    Quote Originally Posted by isladogs View Post
    The form error handler I used does suppress the error.
    Colin, I get that - but why make the on no data function return the action cancelled error, that's the annoyance.
    It's almost as easy (and a similar amount of code) to check for records yourself and simply not open the report in the first place.

    Think I need more coffee
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  6. #6
    isladogs's Avatar
    isladogs is offline Very idle programmer
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,078
    I have at times used that approach to count the records in the calling form. Something like...

    Code:
    Dim lngCnt As Long  strSQL As String
    strSQL = "your report record source"
    
     lngCnt=Count("*", strSQL)
    
    If lngCnt>0 Then
       DoCmd.OpenReport "ReportName, acViewPreview
    Else
       MsgBox "No data for the report"
    End If
    
    End Sub
    As you say, a similar amount of code overall...but it has to be customised for each report record source so perhaps takes a bit more thought /time to do.

    Anyway, having got involved in a fairly fruitless discussion about DateValue yesterday, i certainly don't intend to argue the case for one no data method over the other.
    Colin (Mendip Data Systems): Website, email
    Try again. Fail again. Fail better.
    A
    sking for help isn't giving up. Its refusing to give up.

  7. #7
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    2,054
    Quote Originally Posted by isladogs View Post
    As you say, a similar amount of code overall...but it has to be customised for each report record source so perhaps takes a bit more thought /time to do.

    Anyway, having got involved in a fairly fruitless discussion about DateValue yesterday, i certainly don't intend to argue the case for one no data method over the other.
    Never fruitless here Colin, I like an apple.
    And I'm not disagreeing with you, I just think it's an oversight from MS to make you have to deal with an error caused by the very function you are using to try and prevent an error in the first place.
    (if that makes sense)
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Data Filter Error
    By Kiran Prakash in forum Reports
    Replies: 5
    Last Post: 10-27-2018, 07:48 PM
  2. Error while exporting the data
    By riaarora in forum Access
    Replies: 2
    Last Post: 08-11-2012, 11:21 AM
  3. Replies: 5
    Last Post: 07-26-2012, 09:42 PM
  4. Replies: 7
    Last Post: 06-08-2012, 09:55 PM
  5. Replies: 1
    Last Post: 05-11-2012, 10:59 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums