No current record Error - Why?

Question

Hello Folks -

When in fact there is no record, I'm getting the 'No current record' error when running the following code but I'm not understanding why.

    If tRS.RecordCount > 0 _
        And Mid(tRS.Fields("Parent_Node").value, 1, 4) = "PFR-" Or _
        Mid(tRS.Fields("Parent_Node").value, 1, 4) = "PFI-" Or _
        Mid(tRS.Fields("Parent_Node").value, 1, 4) = "PFG-" Then

However, if I break it up into two If statements, it behaves as expected when is no record:

    If tRS.RecordCount > 0 Then
        If Mid(tRS.Fields("Parent_Node").value, 1, 4) = "PFR-" Or _
            Mid(tRS.Fields("Parent_Node").value, 1, 4) = "PFI-" Or _
            Mid(tRS.Fields("Parent_Node").value, 1, 4) = "PFG-" Then

Why is the first one throwing errors?

Answer 1

And has precedence above Or. Your first code is evaluated as

    If (tRS.RecordCount > 0 _
        And Mid(tRS.Fields("Parent_Node").value, 1, 4) = "PFR-") Or _
        Mid(tRS.Fields("Parent_Node").value, 1, 4) = "PFI-" Or _
        Mid(tRS.Fields("Parent_Node").value, 1, 4) = "PFG-" Then

Change it to

    If tRS.RecordCount > 0 _
        And (Mid(tRS.Fields("Parent_Node").value, 1, 4) = "PFR-" Or _
        Mid(tRS.Fields("Parent_Node").value, 1, 4) = "PFI-" Or _
        Mid(tRS.Fields("Parent_Node").value, 1, 4) = "PFG-") Then

---

Regards, Hans Vogelaar (https://www.eileenslounge.com)

Answer 2

Hi Hans -

I tried that as well, but same issue.

Answer 3

Oh, wait. VBA evaluates all parts of the condition. If there are no records, RS.Fields(...).Value causes an error.

So you do have to use the second code.

---

Regards, Hans Vogelaar (https://www.eileenslounge.com)

Answer 4

HI Hans -

Thank you for the confirmation!

Answer 5

Nice! - you are correct!

as FYI?

A number of languages have what we call "short circuit"

if <some expression> AndAlso <some expression>

If <some expression> OrAlso <some expression>

So, if the first expression fails (evaluates to false), then the additional conditions are NOT evaluated.

So yes, the record count test is required before ANY other expressions are attempted to be evaluated against that reocrdset - since they may not be valid until recordcount > 0

Access is not one of these systems!

Regards,

Albert D. Kallal (Access MVP 2003-2017)
Edmonton, Alberta Canada