2427 You entered an expression that has no value

Question

I have a function to split up my report into separate PDF files based on a field id:

Public Function PrintList() As String

    Dim MyPath As String
    Dim MyFilename As String
    Dim myId As String
    Dim filter As String
    MyPath = "C:\reports\"

    Dim rs As DAO.Recordset
    Set rs = CurrentDb.OpenRecordset("SELECT DISTINCT id FROM table")

    'Loop through the set
    Do While Not rs.EOF

        If Not IsEmpty(rs!id) And Not IsNull(rs!id) Then
            myId = rs!id
        Else
            myId = ""
        End If

        filter = "id = '" & myId & "'"

        'Open report preview and auto-save it as a PDF
        DoCmd.OpenReport "myReport", acViewPreview, , filter

        MyFilename = "N" & myId & ".pdf"
        DoCmd.OutputTo acOutputReport, "", acFormatPDF, MyPath & MyFilename, False

        'Close the previewed report
        DoCmd.Close acReport, "myReport"

        rs.MoveNext
    Loop

End Function

However, while running, I would get this error/warning:

2427 You entered an expression that has no value.

This happens when the id is null. I don't even know if this is an error or a warning. After I click Ok, the function continue to runs till the end without any problem, and the output file would be named N.pdf.

Since this message box is not like the usual error message box, and since it doesn't give me the option to debug or end the execution, I guess it is not an error?

I have tried to suppress warning using DoCmd.SetWarnings False but it doesn't work.

How can I fix this or at least suppress it so that it doesn't show up?

enter image description here

RBarryYoung · Answer 1 · 2014-08-28T16:46:49.960

0

There's no short-circuiting in VBA, so try changing this:

    If Not IsEmpty(rs!id) And Not IsNull(rs!id) Then
        myId = rs!id
    Else
        myId = ""
    End If

to this:

If Not rs!Id Is Nothing Then
    If Not IsNull(rs!id) Then
        If Not IsEmpty(rs!id) Then
            myId = rs!id
        Else
            myId = ""
        End If
    Else
        myId = ""
    End If
Else
    myId = ""
End IF

edited Aug 28 '14 at 16:46

answered Aug 27 '14 at 15:41

RBarryYoung

55,398
14
96
137

shouldn't the second `myId = rs!id` be `myId = ""` instead? – Chin Aug 27 '14 at 16:41
Even with no short circuiting wouldn't those expressions still evaluate to a True or False? – ashareef Aug 27 '14 at 16:45
That's true, I haven't seen where the problem comes from. Can you explain it in more details for me RBarryYoung? – Chin Aug 27 '14 at 16:49
@ashareef No, `IsEmpty(..)` will throw the error because it's trying to access the `.Value` property of the `id` field object. – RBarryYoung Aug 27 '14 at 17:36
@Chin yes to your first comment. I will correct it. As for your second comment, this error comes from trying to access a property of a control or object that is binding another object, when that second object doesn't exist. In this case the second object is the returned SQL column value that (apparently) is bound to the ADO Field object "id". For whatever reasons, `NULL` column values manifest as non-created values inside of Field objects, thus the error. – RBarryYoung Aug 27 '14 at 17:42
I tried your solution but I'm still getting the same error/warning. Is there a way to see which line trigger this error/warning? – Chin Aug 27 '14 at 18:45
@Chin it may need a check for `Nothing` also. Try my latest update. To see which line this is occurring on, you would either need to step through the routine, or add an error handler with a breakpoint in it and then step from there using `Resume`. – RBarryYoung Aug 28 '14 at 16:50

2427 You entered an expression that has no value

1 Answers1

Linked