CODEWITHSUNDEEP
vbams-access
Graham Warrender
Graham Warrender

Reputation: 365

VBA Recordset - Object Doesn't support this property or method

Can anyone explain what is wrong with this code? There's a runtime error stating that the Object doesn't support this property or method 

Set rst = DataFunctions.CheckCompanyID

If IsNull(rst.Fields("ID")) Then 'Error occurs here
    ContactID = 0 
Else
    ContactID = rst!ID
End If

The CheckCompanyID method does as follows

CompanyValue = GetCurrentRecord 

CheckData = "Select CompanyID, ID From Contacts Where Contacts.CompanyID = " & CompanyID & ";"

CheckCompanyID = CurrentDB.OpenRecordset(CheckData, dbOpenDynaset) 'Returns The Recordset

The If Is Null(rat.Fields("*FieldName*")) Then has been used elsewhere and works ok. I imagine the error is a result of returning the recordset object?

Upvotes: 0

Views: 4007

Answers (2)

Tomalak
Tomalak

Reputation: 338148

A suspected in my question comment, your function DataFunctions.CheckCompanyID does not return a recordset object, but Nothing instead. Nothing is VB's null.

This can happen if there is an unhandled error inside the function and On Error Resume Next is in effect, or simply because the function is implemented that way.

As a general tip: You should not use object references without checking that they are valid (i.e. not Nothing) to avoid run-time errors like this one.

You can check for Nothing easily:

Set rst = DataFunctions.CheckCompanyID

ContactID = 0 

If Not rst Is Nothing
    If IsNull(rst.Fields("ID")) Then 
        ContactID = rst!ID
    End If
End If

Note: Classic VB does not support short-circuiting logical expressions, so you can't use the one-line form that other languages would allow:

If Not rst Is Nothing And Not IsNull(rst.Fields("ID")) Then
    ' This will cause an error because rst.Fields("ID") is always evaluated!
End If

Upvotes: 1

Krish
Krish

Reputation: 5917

check your rst, if it gets an recordset returned with results. and try this:

IF nz(rst("ID"), "") = "" then

instead

If IsNull(rst.Fields("ID")) Then

Upvotes: 0

Related Questions