Record number - Easy

[Steph]

Is there a way to show the row number in the select of a form
So if there's 15 records, the first field would be 1,2,3,4... to 15

 

[Dirk Goldgar]

Is there a way to show the row number in the select of a form
So if there's 15 records, the first field would be 1,2,3,4... to 15

You have to understand first that the "record number" is essentially
meaningless and circumstantial, since it is nothing inherent in the
record, but instead is entirely dependent on the order in which the
records happen to be sorted at the moment.

On a form in single-form view, you can get the "record number" easily,
by setting a text box's ControlSource to

=[CurrentRecord]

But that doesn't work in continuous forms view, because there is only
one current record at a time, so all the text boxes will show the same
thing. I only know of two solutions, both rather cumbersome. Both
solutions require that the form's recordsource have a unique key. The
traditional solution is to set up a calculated control (or a calculated
field in the query) that counts the number of records that have a sort
field(s) that is less than the sort field(s) of the current record.
Thus, this solution also requires that you know in advance what sequence
the records will be sorted in. Then you either set up the form's
recordsource query to do the counting, by means of a subquery, like this
....

SELECT *
1 +
(SELECT Count(*) FROM MyTable T
WHERE T.SortField < MyTable.SortField)
AS RecNo
FROM MyTable
ORDER BY SortField

.... or else you use a calculated control on the form with a DCount
expression, like this:

=1 + DCount("*", "MyTable",
"SortField < " & [SortField])

These are both implementations of the same basic approach, and are
fairly rigid.

I just thought of, and tested, another approach, and it seems to work.
You can define a function like this:

'----- start of function code -----
Function FormRecordNumber( _
frm As Form, _
ctlPK As Access.Control) _
As Variant

' Return the "record number" for each record on a
' continuous form. <frm> is a reference to the form
' itself, <ctlPK> is a reference to the control on
' that form that is bound to the primary key of the
' form's recordsource.

Dim strControlSource As String
Dim strCriteria As String

If IsNull(ctlPK.Value) Then Exit Function

With frm.RecordsetClone
strControlSource = ctlPK.ControlSource
strCriteria = _
Application.BuildCriteria( _
strControlSource, _
.Fields(strControlSource).Type, _
"=" & ctlPK.Value)
.FindFirst strCriteria
FormRecordNumber = .AbsolutePosition + 1
End With

End Function

'----- end of function code -----

Then you can put a calculated control on the form that calls the
function, like this:

=FormRecordNumber([Form], [RecID])

where "RecID" is the name of the control on the form that is bound to
the recordsource's primary key (or in fact any unique key field). Note
that the recordsource must have such a key field, and the form must have
a control bound to it.

I haven't tested the above function thoroughly, but it seems to work in
preliminary tests. It seems to me to be a bit more flexible than the
other approach, as you don't need to make any changes to the form's
recordsource, and it responds automatically to any sorting or filtering
you do to the form.