Store the current record number

[Sally Green]

Strange request and it must be quite simple but I don't know how to do it.
I need to run a procedure, either update query or code, that will run
through every record (either from within a form or directly to the table)
that will pick up the current record number and place that value into a field.

IE the primary key value might be 20240 but due to archived data it might be
record number 1167. I need to store the 1167 in a field for use in another
program.

I know that the record number is dependant on the sort order and is liable
to change, but I can re-run the query or code.

Could someone please give me some help. Thanks.

 

[boblarson]

The record number has no meaning outside of the current recordset. You
should NOT use it for anything, period. Use the primary key of the row as
that is what identifies a row of data.
--
Bob Larson

Free Tutorials and Samples at http://www.btabdevelopment.com

__________________________________

 

[John W. Vinson]

Strange request and it must be quite simple but I don't know how to do it.
I need to run a procedure, either update query or code, that will run
through every record (either from within a form or directly to the table)
that will pick up the current record number and place that value into a field.

IE the primary key value might be 20240 but due to archived data it might be
record number 1167. I need to store the 1167 in a field for use in another
program.

I know that the record number is dependant on the sort order and is liable
to change, but I can re-run the query or code.

Could someone please give me some help. Thanks.

Since an Access (JET) table does not have a defined sort order nor does it
have a record number, the only answer I can suggest is You Can't.

You can get the .AbsolutePosition property of records in a Recordset, which
does obviously depend on the recordset's sort order. What I suppose you could
do is code like:

Dim rs As DAO.Recordset
Me.Filter = "" ' clear any filters on the form
Me.FilterOn = False
' maybe also clear the form's OrderBy unless you want it applied
Set rs = Me.Recordsetclone
rs.MoveFirst
Do Until rs.EOF
rs.Edit
rs!RecordNumber = rs.AbsolutePosition
rs.Update
rs.MoveNext
Loop
rs.Close

Note that this will VERY rapidly bloat your database (since you're editing
every record, every day) and you'll need to compact regularly.

What is this "other program" and how does it use this value!?