How can I sequentually number records in continuous forms view?

  • Thread starter Thread starter Dave
  • Start date Start date
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?
 
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.
 
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.
 
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]
 
Back
Top