update question

[samotek]

I have a table where the first field is an autonumber.it is called orderid. As a result of appending i have a row that does not follow the autonumber of the tables.
For example the first row is orderid = 1
and the second row is orderid = 5


I want to update the table so that the row that has orderid = 5 to get the next autonumber, i.e. 2.
How can i make my update query ?

 

[avolkert]

You can't do this. The last increment of the Autonumber field is stored with the table. The only way to reset the autonumber is to use VB to do so, but then, what was once a 2 may end up being a 17 or vice versa.

Bottom Line: You should not use autonumbers. They not worth the hassle. I suggest that you Dim a Recordset and use an ordinary counter to update sequentially.

Second Bottom Line: Avoid the grief and avoid AutoNumbers !

Alan J. Volkert
Fleet Services
GE Commercial Finance Capital Solutions
(World's longest company title)
Eden Prairie, MN

 

[TheDude53]

Auto numbers do not always follow in sequence. If you had records 1,2,3 and 4 then deleted 2,3 and 4 the next record it will create will have a value of 5. I wouldn’t worry to much about trying to get them in sequence. Rule of thumb with auto numbers is that they are used as a unique id. As far as I am aware it is not possible to force this auto number to be the next one in sequence without messing about changing the field data type from autonumber then back again.

Hope this helps,
Rob