Run-time error 3021

Question

Not entirely sure how to fix this code that is giving me a run-time error 3021: No Current Record.

The purpose is to click this button and it will populate a few text boxes with the results of the first record of the query. It is then supposed to populate another two text boxes with the next record of results from the query using the MoveNext.

Private Sub btnSCC07_Click()

Dim dbs As DAO.Database
Dim rsTable As DAO.Recordset
Dim rsQuery As DAO.Recordset


Set dbs = CurrentDb

Set rsQuery = dbs.OpenRecordset("SELECT * FROM qrySccS2 WHERE PAYORNAME = 'Medicaid' AND OVERRIDECODESALL = 7", dbOpenDynaset)

rsQuery.FindFirst ("[PAYORNAME]='Medicaid'")
num1 = rsQuery!PAYPLANNAME
num2 = rsQuery!Count
txtMedicaid9 = num1
txtMedicaidCount9 = num2
Debug.Print (txtMedicaid9)


rsQuery.MoveNext
Debug.Print ("Test")
num3 = rsQuery!PAYPLANNAME
num4 = rsQuery!Count
txtMedicaid10 = num3
txtMedicaidCount10 = num4


rsQuery.Close
Set rsQuery = Nothing

So the first section works, but it's when it reaches the line num3 = rsQuery!PAYPLANNAME is where it throws the error. Thank you in advance for any insight you may be able to provide.

you need to check the record count to make sure you have results returned. rsQuery.RecordCount or rsQuery.EOF — Sorceri
@Sorceri hmm so I just tried that and the debug.print returned 1...... but when I run the query itself with the parameters it returns 12 results? — N.Ha
Use lines just after opening the recordset rsQuery.MoveLast rsQuery.MoveFirst to force the count. Why do you have a MoveNext without a loop? — June7
@June7 So I inserted rsQuery.MoveLast and rsQuery.MoveFirst right after the Set rsQuery = dbs.OpenRecordset line and it is still return 1 in the immediate window and throwing the error. — N.Ha
Review allenbrowne.com/ser-29.html#RecordCount_without_MoveLast — June7

Gustav Gustav · Accepted Answer · 2017-09-09T11:04:45

You should play safe using NoMatch and EOF:

Set rsQuery = dbs.OpenRecordset("SELECT * FROM qrySccS2 WHERE PAYORNAME = 'Medicaid' AND OVERRIDECODESALL = 7", dbOpenDynaset)

If rsQuery.RecordCount > 0 Then
    rsQuery.FindFirst "[PAYORNAME]='Medicaid'"
    If Not rsQuery.NoMatch Then
        num1 = rsQuery!PAYPLANNAME.Value
        num2 = rsQuery!Count.Value
        txtMedicaid9 = num1
        txtMedicaidCount9 = num2
        Debug.Print txtMedicaid9

        If Not rsQuery.EOF Then
            rsQuery.MoveNext
            Debug.Print "Test"
            num3 = rsQuery!PAYPLANNAME.Value
            num4 = rsQuery!Count.Value
            txtMedicaid10 = num3
            txtMedicaidCount10 = num4
        End If
    End If
End If
rsQuery.Close

Set rsQuery = Nothing

Run-time error 3021

1 Answers