Add a new field to a datasheet view

[Guest]

Hi,
I have a sub form (datasheet view) which has an unbounded filed. I would
like this field to display the record number for each record being displayed
within the datasheet. Is this possible and how could I do it? I can perhaps
can calculate the number of record but I do not know how to display it for
each reacord. Your help is greatly appreciated in advance.

Mike

 

[Marshall Barton]

I have a sub form (datasheet view) which has an unbounded filed. I would
like this field to display the record number for each record being displayed
within the datasheet. Is this possible and how could I do it? I can perhaps
can calculate the number of record but I do not know how to display it for
each reacord. Your help is greatly appreciated in advance.

This is not a good thing to do. It will probably slow your
form unacceptably without providing a significant benefit to
users.

One way is to use a subquery in a calculated field in the
subform's record source query. It is dependent on the data
selected in the query having a unique sort order. This is a
simplified example that assumes you have no criteria

LineNum: (SELECT Count(*) FROM [the table] As X
WHERE X.sortfield <= [the table].sortfield)

A different way is to create a function in the subform's
module and use it in a text box:
=LineNum(pkfield)

Private Function LineNum(Key As Long) As Long
With Me.RecordsetClone
.FindFirst "pkfield=" & Key
LineNum = .AbsolutePosition
End With
End Function
Be sure to change the names to the ones you are actually
using.