ms access - VBA - Compile Error: Invalid use of property -

i trying count no of elements in recordset after query. doing this

function test() variant     dim rst dao.recordset     rst = currentdb.openrecordset("select salary_total compsal")     rst.movelast     rst.movefirst     test = rst.recordcount end function 

i compile error:

invalid use of property

how make work?

rst declared recordset object. object need set before being used. unlike variables, cannot assigned need set. if possible, try , clean objects. try,

function test() variant     dim rst dao.recordset     set rst = currentdb.openrecordset("select salary_total compsal")     rst.movelast     rst.movefirst     test = rst.recordcount     set rst = nothing end function 

however, can use dcount, same.

test = dcount("*", "compsal") 

this lot easier creating object, moving in recordset, count.