Report_NoData

[yessir]

I am opening a report froma form using the following code:

Private Sub cmdFedRPT_Click()

    Dim dd As Integer
    
    Dim MyDatabase As Database
    Dim MyQueryDef As QueryDef
    Dim where As Variant
        
    If Nz(Me.cboFedDateBegin, "") = "" Then
        MsgBox "You must select a date!", vbOKOnly
        Me.cboFedDateBegin.SetFocus
    ElseIf Not (Nz(Me.cboFedDateBegin, "") = "") And Nz(Me.cboFedDateEnd, "") = "" Then
        'Based on a single date
        Set MyDatabase = CurrentDb()
        
        ' Delete the existing dynamic query; trap the error if the query does
        ' not exist.
        If ObjectExists("Queries", "qry_RPT_Fed_QBF") = True Then
          MyDatabase.QueryDefs.Delete "qry_RPT_Fed_QBF"
          MyDatabase.QueryDefs.Refresh
        End If
    
        Set MyQueryDef = MyDatabase.CreateQueryDef("qry_RPT_Fed_QBF", _
            "SELECT tblWater_Sample_Results.ID, tblWater_Sample_Info.SampleDate, tblWater_Sample_Info.Outlet_ID, tblAnalyte.Analyte, tblWater_Sample_Results.Analyte_ID, tblWater_Sample_Results.Result, tblWater_Sample_Results.Unit_ID, tblWater_Sample_Results.DL, tblAnalyte.INC_FED, tblLocation.INC_FED, [DL] & "" "" & [Result] AS Resultant, tblWater_Sample_Results.LOQ, tblFed_Limits.Max_Month, tblFed_Limits.M_Unit_ID, tblFed_Limits.IND_Month, tblFed_Limits.Max_Comp, tblFed_Limits.C_Unit_ID, tblFed_Limits.IND_Comp, tblFed_Limits.Max_Grab, tblFed_Limits.G_Unit_ID, tblFed_Limits.IND_Grab " & _
            "FROM (tblLocation INNER JOIN tblWater_Sample_Info ON tblLocation.ID = tblWater_Sample_Info.Outlet_ID) INNER JOIN ((tblAnalyte INNER JOIN tblFed_Limits ON tblAnalyte.ID = tblFed_Limits.Analyte_ID) INNER JOIN tblWater_Sample_Results ON tblAnalyte.ID = tblWater_Sample_Results.Analyte_ID) ON tblWater_Sample_Info.ID = tblWater_Sample_Results.ID " & _
            "WHERE (((tblAnalyte.INC_FED)=True) AND ((tblLocation.INC_FED)=True) AND ((tblWater_Sample_Info.SampleDate)=#" & Me.cboFedDateBegin & "#));")

[B]            DoCmd.OpenReport "rptFed", acViewPreview, "qry_RPT_Fed_QBF"[/B]
        
        Me.Form.Visible = False
    ElseIf Me.cboFedDateBegin > Me.cboFedDateEnd Then
        MsgBox "END DATE must be AFTER START DATE!", vbOKOnly
        Me.cboFedDateEnd = ""
        Me.cboFedDateEnd.SetFocus
    Else
        Set MyDatabase = CurrentDb()
        
        ' Delete the existing dynamic query; trap the error if the query does
        ' not exist.
        If ObjectExists("Queries", "qry_RPT_Fed_QBF") = True Then
          MyDatabase.QueryDefs.Delete "qry_RPT_Fed_QBF"
          MyDatabase.QueryDefs.Refresh
        End If
    
        Set MyQueryDef = MyDatabase.CreateQueryDef("qry_RPT_Fed_QBF", _
            "SELECT tblWater_Sample_Results.ID, tblWater_Sample_Info.SampleDate, tblWater_Sample_Info.Outlet_ID, tblAnalyte.Analyte, tblWater_Sample_Results.Analyte_ID, tblWater_Sample_Results.Result, tblWater_Sample_Results.Unit_ID, tblWater_Sample_Results.DL, tblAnalyte.INC_FED, tblLocation.INC_FED, [DL] & "" "" & [Result] AS Resultant, tblWater_Sample_Results.LOQ, tblFed_Limits.Max_Month, tblFed_Limits.M_Unit_ID, tblFed_Limits.IND_Month, tblFed_Limits.Max_Comp, tblFed_Limits.C_Unit_ID, tblFed_Limits.IND_Comp, tblFed_Limits.Max_Grab, tblFed_Limits.G_Unit_ID, tblFed_Limits.IND_Grab " & _
            "FROM (tblLocation INNER JOIN tblWater_Sample_Info ON tblLocation.ID = tblWater_Sample_Info.Outlet_ID) INNER JOIN ((tblAnalyte INNER JOIN tblFed_Limits ON tblAnalyte.ID = tblFed_Limits.Analyte_ID) INNER JOIN tblWater_Sample_Results ON tblAnalyte.ID = tblWater_Sample_Results.Analyte_ID) ON tblWater_Sample_Info.ID = tblWater_Sample_Results.ID " & _
            "WHERE (((tblAnalyte.INC_FED)=True) AND ((tblLocation.INC_FED)=True) AND ((tblWater_Sample_Info.SampleDate) Between #" & Me.cboFedDateBegin & "# And #" & Me.cboFedDateEnd & "#));")

            DoCmd.OpenReport "rptFed", acViewPreview, "qry_RPT_Fed_QBF"
        
        Me.Form.Visible = False
    End If

cmdFedRPT_Click_Exit:
    Exit Sub

cmdFedRPT_Click_Err:
    If Err = 2501 Then 'The RunCommand action was canceled
        Exit Sub
    Else
        MsgBox Err.Number & " - " & Err.Description
        Resume cmdFedRPT_Click_Exit
    End If

End Sub

but if there is no data it does the msgbox in the popup then it shows

Runtime error '2501'

The OpenReport action was cancelled

This is the code for the report on no data:

Private Sub Report_NoData(Cancel As Integer)
On Error GoTo Report_NoData_Err
    
    MsgBox "Your request has no data to print." & vbCrLf & vbLf & "Your print request has been canceled.", vbOKOnly + vbInformation
    Cancel = True
    
Report_NoData_Exit:
    Exit Sub
    
Report_NoData_Err:
    MsgBox Err.Number & " - " & Err.Description
    Resume Report_NoData_Exit
    
End Sub

 

[daveUK]

Before

  DoCmd.OpenReport "rptFed", acViewPreview, "qry_RPT_Fed_QBF"

type:

on error resume next

This should stop any error messages being displayed

 

[Rich]

cmdFedRPT_Click_Err:
If Err = 2501 Then
Resume cmdFedRPT_Click