Count Records While Opening a RecordSet 1

[Happy2]

Hi all,

Please help me how to count records when opening a recordset. I want to know if CA is in my State field already of tblManager table.

Function ForCount()

Dim rs As New ADODB.Recordset
Set rs = New ADODB.Recordset

rs.Open "Select * From tblManager", CurrentProject.Connection, adOpenKeyset, adLockOptimistic

If Not .EOF Then


'HOW TO COUNT !State


End If

rs.Update
rs.Close
rs.ActiveConnection = Nothing

End Function

Thanks in advance for your help!!!

 

[gol4]

1. only bring back the count

"Select count(*) as cnttotal From tblManager where state ='CA'"

rst.movefirst
debug.print rst!cnttotal
rst.close

if you want another option then

dim x a integer
Dim rs As New ADODB.Recordset
Set rs = New ADODB.Recordset

rs.Open "Select * From tblManager", CurrentProject.Connection, adOpenKeyset, adLockOptimistic

If Not .EOF Then
if rst!state = 'CA" then x=x+1

End If

rs.Close
rs.ActiveConnection = Nothing
debug.print x

 

[Happy2]

Hi Gol4,

Yes, I got it as your suggestion that I have to count in the select statement. Thanks a lot for your help!