Can I get a record count of this???

[Southern at Heart]

I've got this nice little recordset up and running now, and get get things
from it like:
rst!Phone or rst!Name
....but I need to know the total number of records in it. I tried
rst.RecordCount, but that doesn't work. Is there a way?

thanks.


Function Phone_List() As Recordset
'This opens a recordset from the Table [Name]
'This recordset contains records that have 3 or more phone #'s
Dim strSQL As String
strSQL = "SELECT Name.Name, Name.Phone " & "FROM Name " & _
"WHERE (((Name.Phone) Like ""*"" & Chr(13) & Chr(10) & ""*"" & Chr(13) &
Chr(10) & ""*""));"
Set Phone_List = CurrentDb().OpenRecordset(strSQL)
End Function

 

[Allen Browne]

See:
VBA Traps: Working with Recordsets
at:
http://allenbrowne.com/ser-29.html#RecordCount_without_MoveLast

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

message
news[email protected]...

 

[Marshall Barton]

I've got this nice little recordset up and running now, and get get things
from it like:
rst!Phone or rst!Name
...but I need to know the total number of records in it. I tried
rst.RecordCount, but that doesn't work. Is there a way?

thanks.


Function Phone_List() As Recordset
'This opens a recordset from the Table [Name]
'This recordset contains records that have 3 or more phone #'s
Dim strSQL As String
strSQL = "SELECT Name.Name, Name.Phone " & "FROM Name " & _
"WHERE (((Name.Phone) Like ""*"" & Chr(13) & Chr(10) & ""*"" & Chr(13) &
Chr(10) & ""*""));"
Set Phone_List = CurrentDb().OpenRecordset(strSQL)
End Function

What do you mean that "doesn't work"?

if your question is that it doesn't always have the total
number of records (and is frequently just 1). then it's
because you haven't accessed all the records yet. You can
easily access all the records by adding:

Phone_List.MoveLast
Phone_List.MoveFirst

to the end of your function.