?? controlling AUTOINCREMENT value ??

[king_ita]

Hi,
I would like to know the way to control an AUTOINCREMENT value of
table..
I need to do this:
when I delete a row from the table i would like to update th
autoincrement values so that there is no gap between the row before th
deleted one and the row after the deleted one.
This because I need the sequence of number to be continuous..

thanks in advance..

KIN

 

[Steve Schapel]

King

Assuming you are referring to an Access AutoNumber data type field,
there is a way to resert the seed value, but this would be a very highly
un-recommended approach. If you need to control the value, it would be
better to use a number data type filed, and a customised process to
assign the vlaues. In the example you gave, this would involve
something like a Default Value of DMax("[YourField]","YourTable")+1
In the case of a deleted record, you would need to run an Update Query
to update the value of the field to [YourField]-1 for all records where
[YourField]>x where x is the value of the deleted record.
I would assume this field is not a primary key, or that it is not
involved in a relationship with any other table(s), otherwise you would
also have to update the related fields as well.

Sorry, this is a general reply... have a go, and please post back if
you need more specific help.