How can I sequentually number records in continuous forms view?

D

Dave

I have a subform in continuous forms view (i.e., multiple records are
displayed on the subform).

If this were a report rather than a form, I could create a textbox control,
set its control source to "=1" and then set the running sum property to
"over all".

But a form does not have a "running sum" property.

How can I accomplish a "running sum" using a subform?
 
K

Ken Snell [MVP]

You will need to add a calculated field to the query that is the subform's
Record Source, and use that calculated query to "calculate" the position
(rank) of the record in the query. A bit convoluted, but it should work.

However, in order to provide a specific suggestion on how to do this,
provide the SQL statement of the query that currently is your subform's
record source.
 
A

Albert D.Kallal

You can put the follwing code in the sub form:

Function Rpos(vId As Variant) As Long

Rpos = 0
If IsNull(vId) = False Then
Me.RecordsetClone.FindFirst "id = " & vId
If Me.RecordsetClone.NoMatch = False Then
Rpos = Me.RecordsetClone.AbsolutePosition + 1
End If
End If

End Function

Then, you can put a un-bound text box in the continoues form, and

=(rpos([id]))

The above assumes you have a key field called id. It also assumes dao.
 
J

John Vinson

I have a subform in continuous forms view (i.e., multiple records are
displayed on the subform).

If this were a report rather than a form, I could create a textbox control,
set its control source to "=1" and then set the running sum property to
"over all".

But a form does not have a "running sum" property.

How can I accomplish a "running sum" using a subform?
Click to expand...

You'll need to have some field in the subform's recordsource which is
in strictly ascending order, no ties - an Autonumber, a date/time
field, or something of the like; I'll call it Sortkey.

Put a calculated field in the Subform's Recordsource

=DSum("[FieldToSum]", "[queryname]", "[Sortkey] <= " & [Sortkey])

where queryname is the recordsource query for the subform - i.e.
you're calling DSum on the query within the query itself. This can be
a pretty major performance hit!

John W. Vinson[MVP]
 
Top