CODEWITHSUNDEEP
vbams-access
user1670773
user1670773

Reputation:

VBA - Compile Error: Invalid use of property

I am trying to count the no of elements in a recordset after a query. I am doing this

Function Test() As Variant
    Dim rst As DAO.Recordset
    rst = CurrentDb.OpenRecordset("SELECT salary_total FROM CompSal")
    rst.MoveLast
    rst.MoveFirst
    Test = rst.RecordCount
End Function

I get this compile error:

invalid use of property

How to make this work?

Upvotes: 1

Views: 10019

Answers (2)

PaulFrancis
PaulFrancis

Reputation: 5819

rst is declared as a Recordset object. Any object need to be Set before it is being used. Unlike variables, they cannot be assigned they need to be Set. Also if possible, try and clean up the objects. So try,

Function Test() As Variant
    Dim rst As DAO.Recordset
    Set rst = CurrentDb.OpenRecordset("SELECT salary_total FROM CompSal")
    rst.MoveLast
    rst.MoveFirst
    Test = rst.RecordCount
    Set rst = Nothing
End Function

However, you can also simply use a DCount, which does exactly the same. 

Test = DCount("*", "CompSal")

This would be a lot easier than creating an object, Moving in the Recordset, all to get a count.

Upvotes: 5

Martin
Martin

Reputation: 16443

Your code is fine apart from one minor error, you forgot to put Set in front of the rst = code:

...
Set rst = CurrentDb.OpenRecordset("SELECT salary_total FROM CompSal")
...

It should work like a charm after that

Upvotes: 2

Related Questions