Simple RecordSet question but I'm stuck

[letmeoutofhere]

I have created the following code to retrieve a set of records from a recordset. I am returning 8 records (as expected) however, I want to know how many I have retrieved instead of the actual records.

The value of "F" is always 1 as the recordcount option never returns the number of records returned just the number of the record selected.

I have played around and referenced all of the records - using the .next logic - and when I do that the recordcount will reflect the correct number of records. This is fine to do for small databases' but not for any thing that contains any records at all..

Any ideas?

Thanks.
Jim..

********************************************************

strsql = "SELECT [lastname] from [MemberShipRoll] where [LastName] = '" & txLastName & "'"

Set FamilyRecordSet = dbsname.OpenRecordset(strsql, dbOpenDynaset)

F = FamilyRecordSet.RecordCount

********************************************************

 

[MrEGuest]

When you're using DAO technology the recordset wont know how many records he has until he's traversed them.

This is what must be done to get around that:

FamilyRecordSet.MoveLast
FamilyRecordSet.MoveFirst
F = FamilyRecordSet.RecordCount 'Now you'll get the right count

Hope this helps!
Josh

 

[Cocheez]

If you just want to get a count of how many records meet your criteria then you can use one of SQL's Aggregate Functions called Count(). So in your case you would use it like so:

strsql = "SELECT Count([lastname]) As iCount From [MemberShipRoll] where [LastName] = '" & txLastName & "'"

Set FamilyRecordSet = dbsname.OpenRecordset(strsql, dbOpenDynaset)

F = FamilyRecordSet("iCount&quot

This way the recordset set holds only one record with the count information rather than then entire recordset.

Hope it helps.

MattU

 

[letmeoutofhere]

Thanks! I got two solutions and both look like they will work. I had tried the Select Count option, but was stuck on the syntax so I gave up.

One reponse mentioned "Using DAO Technology"... what should I be using instead? ADO? What is the difference/benefits?

I have 20 years of mainframe experience...working on branching out with my skills...

Thanks