IsNull(rst.FIelds("field").Value) Giving Error '3021' No Current Record

0
votes

Morning all! I am stuck and would appreciate any help I can get.

Part of my subroutine gets a value from a recordset. If the query that the recordset is based on doesn't return a value then I get the error:

'3021' No Current Record

I'm trying to check for null records and redirect my vba code elsewhere to avoid this error but I'm unable to do so. All references I have seen so far have said to do this same thing, which is what I tried

IsNull(rst.FIelds("field").Value)

and still doesn't work. I'm not sure where to do from here.

Code is below.

At the moment for debugging purposes I have hardcoded values 27 and Schematic but please note that in the real subroutine these will be variables.

All your help is much appreciated! Have a great day :)

Dim dbs As DAO.Database
Dim rst_Trim As DAO.Recordset    
Set dbs = CurrentDb
    
    qrystring = "SELECT tblDocuments.Trim FROM tblDocuments WHERE (tblDocuments.ID = 27 AND tblDocuments.type = 'Schematic')"
    Set rst_Trim = dbs.OpenRecordset(qrystring, dbOpenSnapshot)
    
    If IsNull(rst_Trim.Fields("trim").Value) Then
        'do stuff
        MsgBox "null"
    Else
        trimNumber = rst_Trim.Fields("trim").Value
        'do stuff
        MsgBox trimNumber
    End If
2
vbams-access
Test if recordset is empty: If Not rst_Trim.EOF Then. Can simplify field reference: rst_Trim("trim") or rst_Trim!trim. - June7
However, I just tested the IsNull() when recordset is empty and it works. Ah, it works with my simplified field reference but not with yours. - June7

2 Answers

2
votes

As you expect only one or "a" value, DLookup can do it:

Dim TrimValue   As Variant

TrimValue = DLookup("[Trim]", "[tblDocuments]", "[ID] = 27 And [Type] = 'Schematic'")

If IsNull(TrimValue) Then
    ' Do stuff.
    MsgBox "null"
Else
    trimNumber = TrimValue
    ' Do stuff.
    MsgBox trimNumber
End If
1
votes

Does not like use of .Value property when recordset is empty. Value is default property and not necessary to reference. Just remove it from your code and it should work.

Otherwise, another way to test if recordset has records:

If rs.EOF Then
    MsgBox "No records"
Else
    'do something
End If