Count total records of a query

[mubi_masti]

Can any one help what is error in the following function to count the total number of records of a query


i always get error on line rs.close



Function TotalRecords(qryname As String) As Long
Dim dbs As DAO.Database
Dim rs As DAO.Recordset

Set dbs = CurrentDb
On Error GoTo Err_TotalRecords
Set rs = dbs.OpenRecordset(qryname)
If rs.RecordCount = 0 Then
TotalRecords = rs.RecordCount
Exit Function
Else
rs.MoveLast
TotalRecords = rs.RecordCount
End If

Err_TotalRecords:
'My code
rs.Close
Set rs = Nothing
dbs.Close
Set dbs = Nothing
End Function

 

[JANR]

Why use a recordset just to get a count of records, just use DCount() function.

DCount("*", "queryname")

JR

 

[vbaInet]

i always get error on line rs.close

What is the error message

Why not use DCount()?

Or

Create a query to Count the total and use DLookup() to get the total count field.

 

[gemma-the-husky]

using dcount would be quicker -

but anyway, the problem in your code is that you need an exit function, after the end if - as your code is just continuing to run into the error handler - and you will get this issue in other code blocks if this is how you are trying to manage the process


so you ought to have a rs.close before the exit function - and then you get this sort of structure

openrecordset
processrecordset
 
rs.close
exit function
 
errorhandler:
etc

 

[mubi_masti]

thanks
i have usd dcount function and it works fine in function

previously i have used this in button event and got error that's why i have tried this function

thanks for your reply