Numbering records

[Sam]

I need to put a number to each record in the table. The numbering should
always start from 1 and increase (1,2,3,4...) until there are no records
left. Autonumber seem not to be appropriate for this matter. What would be
the easiest way or code of doing this?

 

[Steve Schapel]

Autonumber would certainly be the simplest. Why is this not appropriate?

 

[Sam]

I'm sorry I didn't describe the situation more specificly. The table which
contains the records to be numbered is updated (when needed) by deleting the
content and rewriting it. This happens automatically and when it rewrites the
table the Autonumber is increased to next number. So the list no longer
starts with 1.

 

[Steve Schapel]

Sam,

You won't be able to do this with a macro. It will require a VBA
procedure. There are 3 possible approaches:

1. Use an Autonumbwer field, and write code to Compact the database
between the steps of deleting the existing records, and appending the
new ones, so the Autonumber seed is re-set to 1.
2. Leave the numbering field blank at the time of appending the
records, and then write code to open a Recordset based on the table, and
loop through the records to update the newly appended records with
sequential numbers.
3. Use looping code to append the new records one at a time, and
increment the number for each record as it is appended.