Count number of records in recordset that match criterion 2

[48Highlander]

How can I count the number of records that match my criterion ([ID]= me.txtID)?

I have tried to open a recordset, apply a filter and then use RecordCount. All it returns is one record - even though I know there are more.

If I apply a recordset filter and then "MoveLast" I get all of the records in the recordset.

The process has to be fast because I want to determine the count every time i change a record in the main form.

 

[Zion7]

You can use DCount().

iNumberOfRecords = DCount("NameOfField","NameOfTable","txtField =" & Me.txtID)

With DCount, you can use any field, that has an entry, to determine the count. I usually use the primary Key, since it's guaranteed to have a value, for every record. in other words...
iNumberOfRecords = DCount("PrimaryKeyField","NameOfTable","txtField =" & Me.txtID)

That's one method of determining a record count, of a recordset, based on a criterion.

Good Luck!

 

[RoyVidar]

The domain aggregate functions are slow when working on linked tables, and specially with concurrent users. If that's the case, I'd go for using recordset. For DAO recordset, one need to fully populate the recordset (.MoveLast) to get correct recordcount (info available in help files on recordcount). Another alternative is opening a recordset based on a "count query"; "select count(*) as mycount from mytable where somcriteria = somevalue"... or check out these Domain Aggregate functions Domain Aggregate Functions Replacements by Trevor Best (yes - it's dao)

But - if this is counting the records of the current recordset, then using for instance the control source of =Count(*) (or the ID field)in a text control, or using the recordcount of the forms recordsetclone should also suffice.

Roy-Vidar

 

[48Highlander]

I downloaded the Domaine Aggregate Function Replacements but I have no idea how to use it. The file is a .bas file.

 

[RoyVidar]

In VBE (ALT + F11) - chose File | Import, select basLookup. Then save and compile (Debug | Compile).

Wherever you'd like to use for instance DCount, use TCount with the same syntax (all the replacement functions have the prefix "T" in stead of "D").

Roy-Vidar

 

[48Highlander]

Sorry to be a pest. Followed RoyVidar's instructions and when I compile the module I get the error "Ambiguous Neme detected: tLookupReset"

I am using Access Xp

 

[48Highlander]

Duh. How stupid can I get. (Don't answer that!) I imported baslookup twice.