Can't seem to get MoveNext to move 1

[BoxHead]

I'm trying to add a column (MyID) to a table to add a sequential index: 1 to RecordCount. My key field is not sequential.

Basing code on several threads regarding updating values using MoveNext, I can't seem to get it to update more than the form's current field or to update all of the records to MyID = 1.

I found a FAQ on adding such a field by calling a static function from a query, but the results are inconcistent and I end up with duplicate values.

Any help would be appreciated.

TIA John

Use what you have,
Learn what you can,
Create what you need.

 

[JimHorton]

John,
I'm not quite sure what you're wanting...but if you want to have a new Key, that's in sequential, ie, no holes, you could add an autonumber field, then change the type to Lont Integer. A new autonumber will fill from 1 to recordcount, in order of the current key. If you want to order it in some other way, you could remove all indexes, including current primary key, and put an index on the field you want the new sequence ordered by, and then add the autonumber.
I say change to LongInt because you always have more control over that type of field, you can fill holes or prevent them (though resequencing on a delete can take awhile). The extra work in generating a new Key Id for the longint field is minimal, and the headaches it can save are worth it.
--Jim

 

[BoxHead]

Jim,

Thanks for your response. There are actually two things I am trying to accomplish. First of all, I wanted to add a new column to a table with an incremented value. There have been quite a few threads I've seen where people need to compare a value in one row to the same field's value in the next row in order to update another field in the first row. I had come across a way to use DLookUp for this but it requires that there be one field in the table/query that has an uninterrupted sequence of numbers.

I had tried the Alter Table method without success because it didn't recognize 'AutoNumber' as a valid data type. After reading your post, I returned to that with renewed confidence and found that for AutoNumber, the actual data type is 'Counter'.

This works to add the column I need:

ALTER TABLE tblIndex1000 ADD COLUMN NewID  COUNTER;

Then, to check the next record's value of Field3:

NextF3: DLookUp("[Field3]","tblIndex1000","[NewID]=" & [NewID]+1)

Thanks for your help.

The OTHER thing I'm trying to accomplish is to get my first MoveNext statement to work. While the Alter Table method is simple, the MoveNext should have worked.

I've modified it so many times in response to errors, poor results and criteria prompts that I don't even know which one I would paste to have you look at. Do you have any MoveNext code sections (that you know for a fact work without fail) that I could try pasting to see what it is I'm missing?

It would be greatly appreciated.


Thanks again, John

Use what you have,
Learn what you can,
Create what you need.