[AccessD] OnNoData error

Elam, Debbie DElam at jenkens.com
Thu May 24 13:49:29 CDT 2007


Thanks, but that did not short circuit the problem.  The problem is with the
Cancel=true statement.  I cannot handle the error that generates without the
sub stopping in it's tracks for some odd reason.  I never make it to the
error handler and I do not know why.

I worked around this by allowing the report to open and change an indicator
field on the form opening the report.  When this field is 1 the report just
opened is closed and the field reset to 0.  Not the most elegant, but it
does get around the problem.

Debbie

-----Original Message-----
From: A.D.TEJPAL [mailto:adtp at hotmail.com] 
Sent: Thursday, May 24, 2007 12:59 AM
To: Access Developers discussion and problem solving
Cc: ADT
Subject: Re: [AccessD] OnNoData error


Debbie,

    Sample subroutine P_OpenReport(), as given below, called from command
button's click event in form's module, can be used universally for opening
all your reports. It also takes care of NoData situation, providing a
friendly message. It does away with the need for separate code (for message
generation) in report's module. Simply put the following statement (nothing
else) in report's NoData event:

    Cancel = True

Best wishes,
A.D.Tejpal
---------------

Sample subroutine for opening reports
======================================
Sub P_OpenReport(ByVal RepName As Variant, _
                            Optional StrCriteria As Variant)
On Error GoTo ErrTrap
 
    If Len(RepName) > 0 Then
        If IsMissing(StrCriteria) Then
            DoCmd.OpenReport RepName, _
                                    acViewPreview
        Else
            If Len(StrCriteria) > 0 Then
                DoCmd.OpenReport RepName, _
                                    acViewPreview, , StrCriteria
            Else
                DoCmd.OpenReport RepName, _
                                    acViewPreview
            End If
        End If
        
        DoCmd.Maximize                                   ' (A)
        DoCmd.RunCommand acCmdZoom100   ' (B)
    End If
    
ExitPoint:
    On Error GoTo 0
    Exit Sub
        
ErrTrap:
    ' Spl message for NoData (Error 2501)
    ' Otherwise, normal error message
    If Err.Number = 2501 Then
        MsgBox "No Matching Record For " & _
                    "Report " & RepName, vbOKOnly, _
                    "No Matching Record"
    Else
        MsgBox Err.Number & " - " & _
                    Err.Description, vbCritical + vbOKOnly, _
                    "Error Encountered"
    End If
    Resume ExitPoint
    
    ' Note - (a) In case of NoData, statements (A) &
    '                 (B) do not get executed. Hence existing
    '                 restored status of the calling form remains
    '                 undisturbed.
    '            (b) Data type of RepName argument has been
    '                  kept as Variant, to provide for values gathered
    '                  from form controls (text / combo boxes)
End Sub
======================================

  ----- Original Message ----- 
  From: Elam, Debbie 
  To: 'Access Developers discussion and problem solving' 
  Sent: Wednesday, May 23, 2007 22:05
  Subject: [AccessD] OnNoData error


  I am running a series of reports off of one command button.  Some of these
may not have data, so I have used the OnNoData event to cancel that
particular report and send a message box to the user that there was no data
in that one.

  The problem I am encountering is the standard 2501 error will simply not
be suppressed.  I have used 

  If err.number = 2501 then 
  resume next
  Else
  msgbox err.desctiption
  resume ExitTheSub
  End if

  In the on error of the button that runs the reports.  Heck I have even set
all errors to resume next, but I still get the error and no resume next.

  I could even live with a harmless error message, but once this happens,
all subsequent reports do not run.  That is the real problem.

  Debbie
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com



More information about the AccessD mailing list