VBA Loop stalling on db.openrecordset when recordcount is 0 (1 Viewer)

[mh123]

Hi guys

A really bizarre issue I haven't walked into before. Let me try and explain;

I am having issues with this small part of code here;

Set rst = CurrentDb.OpenRecordset(sSql1, dbOpenSnapshot)
    With rst
        If .RecordCount > 0 Then
            i = i + 1
            .MoveFirst
            FTA = Nz(!Accounts, 0): FTAT = ![avgofest time]: FTB = Nz(!sumofBal, 0): PS = !sumofPay
            .Close
        Set rst = Nothing
        Else
            rst.Close
            Set rst = Nothing
            Jumpout = True
                DoEvents
            Exit Function
        End If
    End With

Now I have a looping procedure that this sits in which calls a lot of other procedures and all works perfectly fine, apart from the procedure stalling on this part when the recordset it's trying to open has 0 records. I am absolutely stumped on this one and haven't encountered it before.

Anyone encountered this before or can anyone shed some light on my problem? Very very confused!!

 

[MarkK]

What does "stalling" mean? Is there an error? Do you get an unexpected result?

I would not set the variable to nothing inside the with block . . .

   With rst
[COLOR="Green"]        ' . . .[/COLOR]
        Set rst = Nothing
[COLOR="Green"]        ' . . .[/COLOR]
   End With

. . . but I'm not certain that will cause a problem, it just doesn't make sense to me to do it.

For clarity and simplicity I would amend that code as follows . . .

    Set rst = CurrentDb.OpenRecordset(sSql1, dbOpenSnapshot)
    With rst
        If .RecordCount > 0 Then
            i = i + 1
            FTA = Nz(!Accounts, 0)
            FTAT = ![avgofest time]
            FTB = Nz(!sumofBal, 0)
            PS = !sumofPay
        Else
            jumpout = True
        End If
        .Close
    End With
    
    If jumpout Then Exit Function

. . . and it's unlikely you need that DoEvents call.

 

[mh123]

Unlikely indeed - just throwing straws at it.

When I say stalling - no error message nothing happens it just stays in 'running' mode and no matter how long I leave it for if I pause and debug it ends up highlighting the openrecordset line, but only when it's trying to open a recordset with 0 values ;o

 

[MarkK]

This sounds like an endless loop because code doesn't pause. Maybe it takes long to open the recordset, and therefore whenever you ctrl+break into code, it happens to be executing that line, but execution won't pause and wait there unless other code is running. A query you use to open a recordset might call a function, for instance, and that function might have an endless loop.

Set a breakpoint prior to that line, step through the execution, and see what happens.

Also, what is the text of that query?

 

[mh123]

God damnit I am a fool. I didn't reset a value properly and had an error in a date function following an empty recordset further up the line which gave it this effect.

Thanks for your help, going to take my GF's advice and stop working..... and play with my cat!!!

 

[Frothingslosh]

It's always a good idea to explicitly check for an empty recordset immediately after opening a recordset, and to include code to handle that when it happens, even if the 'handling' merely consists of a message box and an abort.