Display record count

[jgelpi16]

I would like to display the count of records on the form. I don't want to display this in the query/table, just on the form. Any suggestions? I feel as though this should be simple, but haven't been able to find the solution yet.

[ajetrumpet]

how about a recordset clone? or alllen browne's ecount function with a unique field? or using a dcount function with any table field? any of those will be fine.

[jgelpi16]

Not sure if DCount() will work. Maybe I can explain what I'm trying to accomplish and you'll have a better method of doing it...

What I want to do is display (Count/Sum in report footer) the first 996 records. However, I don't want to alter the data in the underlying query/table.

[ajetrumpet]

you want to aggregate the first 996 records? hmmm....I doubt you can do it easily. what's the source? a query? table?

more than likely, you'll have to create another query or something similar in code to get the number you want. you can always destroy it after you get from it what you need to, because a control source on an unbound control can always be set from temporary memory, and at that point it's permanent on the report until changed again.

e.g.

control source: "="my value""

[jgelpi16]

The source is a table. How would I go about obtaining the record count from the table?

My thought was to assign the text-box in the record the count, then run a filter for anything less than or equal to 996.

[ajetrumpet]

jg,

as I said, it might have to be dynamic. I'm not sure if you can run TOP with an aggregate function, but you might try it. so, for example, create a dynamic query and aggregate the data, then reference it in a tbox. first, maybe:

dim q as dao.querydef
dim s as string

s = "select TOP 996 field1, sum(field2) as mysum " & _
     "from table group by field1"

set q = currentdb.createquerydef("qtemp", s)

then reference it:

=qtemp!field2

or, a lookup function might be needed, with no 3rd parameter, as the direct reference may not work in a control source like that.