Reputation:
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
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
Reputation: 16433
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