sql statement not working. 1

[dotolee]

hi.
i'm trying to select some records for reporting purposes. i know i have more than 1 record that matches my selection criteria, but the recordcount is always 1.
My code looks like:
Dim Rs As DAO.Recordset
Set Rs = CurrentDb.OpenRecordset("SELECT * FROM [Client Info] WHERE [sgroup]=2", dbOpenDynaset)
Do While Not Rs.EOF
If Rs.EOF Then Exit Do
totalWL = Rs.RecordCount
exit do
'add logic to loop through and extract names.
Loop
Debug.Print totalWL
Set Rs = Nothing
Any suggestions?

 

[PHV]

Use the MoveLast method before the RecordCount property.
BTW, why using a loop ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dotolee]

Thank you!

i'll have to loop through the data later on to get more details.

 

[dotolee]

one other question re: efficiency.
i have to do multiple queries on the same set of data... for counts/total etc.
I was thinking about getting everything in one recordset and then using the .filter to make subqueries against the data. But the MS Access help says the following:

"In many cases, it's faster to open a new Recordset object by using an SQL statement that includes a WHERE clause."

Does you have any experience with this to agree / disagree?
or any suggestions in general? just don't want to create any major design bugs.
thanks.

 

[JoeAtWork]

It's generally better to use SQL statements then filtering a recordset.

Joe Schwarz
Custom Software Developer

http://www.plandev.net