updating a blank field with its record number

[RichO]

There are a number of threads in the forum for a similar question about numbering records in forms and reports but I can't find my answer.

I have a table with a field that is currently blank. I want to fill this field with its corresponding record number. After opening the recordset I have tried the following:

Do While Not rs.EOF
     RecCnt = RecCnt + 1
     rs.edit
     rs!RecNumber = RecCnt
     rs.update
Loop

But I get an overflow error because it just keeps repeating the first record in the set to infinity.

If I use "If Not rs.EOF Then.... End If" it updates only the first record and then exits. Why does it not get beyond the first record in the set?

I'm sure there is a very simple way to do this that I am not aware of.

Thanks for the help guys.

 

[Dugantrain]

The loop will not automatically move to the next record, you have to tell it to by:

rs.MoveNext

 

[Pat Hartman]

If you want to process all the records of a recordset, you need to use the .MoveNext Method to move the current record pointer to the next record.

BTW, if you just want to number a set of existing data, add an autonumber to the table. When you save the table definition, the autonumber will populate for each record.

 

[RichO]

Thanks. I noticed about 5 minutes after I posted this that the rs.MoveNext was missing.

As far as autonumbering the fields, sometimes the fields need to be numbered in a hand selected order, other times automatically, so I have set up a command button that will autonumber when necessary.

I got it working now. Thanks.