Report with Empty Query - Error 2427

[jrhessey]

I'm trying to make a report, based on a query, that will most of the tie contain data, but at times could also not contain any data. I'm performing some conditional formatting based on a field in the query. If the query is empty I get the error 2427 you entered an expression that has no value. If the query has data, the report runs fine. Is there any way through vba to tell the report if there is no data in the query to bypass all of the code (below) and simply pop up a prompt that says "There are currently no orders that have been released."? I've tried the on empty data property but I get my error and the 2427 error. Thanks for any ideas!

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    
    Dim lngRed As Long, lngBlue As Long, lngOrange As Long
    
    lngRed = RGB(255, 0, 0)
    lngBlue = RGB(0, 0, 255)
    lngOrange = RGB(255, 128, 0)
        
    If Me.OrderType = "S" Or Me.OrderType = "M" Or Me.OrderType = "R" Or Me.OrderType = "Q" Then
        Me.ShipExpireDate.ForeColor = lngBlack
        Me.SalesOrderNo.ForeColor = lngBlack
        Me.ShipToName.ForeColor = lngBlack
        Me.OrderType.ForeColor = lngBlack
        Me.ShipVia.ForeColor = lngBlack
        Me.CustomerPONo.ForeColor = lngBlack
        Me.Order_Released_Brass.ForeColor = lngBlack
        Me.Brass_Printed.ForeColor = lngBlack
    End If

    If Me.OrderType = "B" Then
        Me.ShipExpireDate.ForeColor = lngBlue
        Me.SalesOrderNo.ForeColor = lngBlue
        Me.ShipToName.ForeColor = lngBlue
        Me.OrderType.ForeColor = lngBlue
        Me.ShipVia.ForeColor = lngBlue
        Me.CustomerPONo.ForeColor = lngBlue
        Me.Brass_Printed.ForeColor = lngBlue
        Me.Order_Released_Brass.ForeColor = lngBlue
    End If

    If Me.Hot_Order = "-1" Then
        Me.ShipExpireDate.ForeColor = lngRed
        Me.SalesOrderNo.ForeColor = lngRed
        Me.ShipToName.ForeColor = lngRed
        Me.OrderType.ForeColor = lngRed
        Me.ShipVia.ForeColor = lngRed
        Me.CustomerPONo.ForeColor = lngRed
        Me.Brass_Printed.ForeColor = lngRed
        Me.Order_Released_Brass.ForeColor = lngRed
    End If

    If Me.Credit_Hold1.Value = "Y" Then
        Me.ShipExpireDate.ForeColor = lngOrange
        Me.SalesOrderNo.ForeColor = lngOrange
        Me.ShipToName.ForeColor = lngOrange
        Me.OrderType.ForeColor = lngOrange
        Me.ShipVia.ForeColor = lngOrange
        Me.CustomerPONo.ForeColor = lngOrange
        Me.Brass_Printed.ForeColor = lngOrange
        Me.Order_Released_Brass.ForeColor = lngOrange
    End If

End Sub

 

[pbaldy]

The report has a No Data event. Set Cancel = True there, and you will have to trap for error 2501 where you call the report. You can add a message box to either place.