Compile Error in VBA code in MS Access

Question

I have the following function in access, which was working fairly well. But now suddenly i am starting to get a compile error : Method or data member not found

Function Serialize(qryname As String, keyname As String, keyvalue) As Long
Dim dbs As Database
Dim rs As Recordset

Set dbs = CurrentDb
On Error GoTo Err_Serialize
Set rs = dbs.OpenRecordset(qryname, dbOpenDynaset, dbReadOnly)


   On Error GoTo Err_Serialize

     'Find the current record.'
     Select Case rs.Fields(keyname).Type
        ' Find using numeric data type key value?'
        Case DB_INTEGER, DB_LONG, DB_CURRENCY, DB_SINGLE, _
        DB_DOUBLE, DB_BYTE
           rs.FindFirst "[" & keyname & "] = " & keyvalue
        ' Find using date data type key value?'
        Case DB_DATE
           rs.FindFirst "[" & keyname & "] = #" & keyvalue & "#"
        ' Find using text data type key value?'
        Case DB_TEXT
           rs.FindFirst "[" & keyname & "] = '" & keyvalue & "'"
        Case Else
           MsgBox "ERROR: Invalid key field data type!"

     End Select

    Serialize = Nz(rs.AbsolutePosition, 0) + 1


Err_Serialize:
        'Add your own Error handler'
        rs.Close
        dbs.Close
        Set rs = Nothing
        Set dbs = Nothing

End Function

The error highlights rs.Findfirst.

Is this a bug by any chance?

Answer 1

Try:

Dim rs As DAO.Recordset

And if that does not compile, ensure that you still have a reference to Microsoft DAO x.x Object Library.

Answer 2

There are two possible data interface libraries in Access, DAO (the native library) and ADO, and both have Recordset objects. Only the DAO has a FindFirst method -- in ADO, it's Find. As @Remou points out in his answer, you can specify the library and avoid the ambiguity.

For the most part, there's very little reason to contemplate using anything other than DAO as your default interface in an Access application (MDB/ACCDB). For ADPs, of course, ADO is the only choice at all (since ADPs are Jet-less).

In the case of your code, you're clearly using DAO (since you use a database variable, which doesn't exist in ADO -- you use connection objects instead), so you probably either have the wrong reference (ADO) or you have both ADO and DAO and they are in the order with ADO first.

In general, there is almost never a case where it's appropriate to have both references, in my opinion -- it just makes everything harder. ADO can be used without a reference (DAO can be, too), but it involves weak typing of your object variables and loss of Intellisense on them. Normal practice is to use mostly DAO and perhaps a sprinkling of the occasional dip into ADO for things that DAO lacks (or does less efficiently than ADO). For those occasional ADO needs, you can use the CurrentProject.Connection object and type your variables as objects and do without the ADO reference entirely.