Autonumber ID field should have numbers in serial order even if I delete a record.

Derek

Registered User.
Local time
Today, 11:19
Joined
May 4, 2010
Messages
234
I have Id field in the table whose data type is set to autonumber. The problem I am getting is suppose the last record added in the table has id=100 . If I delete this record then the new record will have id=101 but in actual I want Id=100 so it should be in serial order .

I want the code to check max id and then next id will be maxid+1.

Can I get this done with autonumber field?

Thanks
 
More info on autonumbers

Good luck with your project.
 
you can use dmax, since you have break in sequence.

you need to create another long integer field (for your sequence).

create a query to get the next number in sequence:

select count(*)+1 from table;
 
The detail of llocating sequential numbers is quite involved.

One common technique is the DMax + 1. It uses the DMax function to find the highest number in the existing records the adds one. However it matter when the number is allocated.

It can be allocated immediately on the new record using the Form BeforeInsert Event. The record must then be saved immediately so the next record by another user will have accounted for it. Once started, the record cannot be abandoned or there will be a break in the sequence.

Another alternative is to apply the number as the completed record is saved thus allowing a record to be abandoned before saving without causing a break. This technique makes it unsuited to being primary key since related records could not have a key until the record is saved. Best use a surrogate key and apply the sequenced number to another field as it is saved.

On a very busy database with many users, it is quite possible for the DMax + 1 technique to allocate the same number to two records from different users. The most reliable technique stores the next number in a table. It is accessed by opening a recordset with a lock to prevent other users connecting. The number is read, incremented then the lock released. It also requires a loop to keep trying in case it is locked.
 
in addition to the foregoing, which, you ought to reconsider why you want the autonumber sequence to be an intact sequence.

Personally, I would leave it as it is, and add another field to store the sequential numbers you need.

Another idea is never to delete records, to avoid the gaps. Just add an "ignore" or "deleted" flag to records to manage the deletions.

Note however that an autonumber MAY decide to jump a block of numbers at any point. You shouldn't use an autonumber to guarantee an intact sequence.
 
Using DMax() for this kind of thing is fine...as long as you're deleting the last Record that was added...as in your example! But what if you've enter 100 Records, then decide to delete Record #10, or Record #29, etc.? Dmax() won't help in that situation!

Maybe the pertinent question here is...why? Why do you need these numbers to be consecutive? We might be able to offer an alternative if we knew this.

Linq ;0)>
 
Derek - the central issue is that Autonumber guarantees a unique number but does not guarantee anything else. If you needed a sequential number that automatically renumbers to close gaps after a deletion, that is NOT an autonumber and in fact it probably isn't really a good candidate for a prime key either, particularly if a child record will ever depend on it as a match for a foreign key.

So... if you need this simply because you are counting records, you need to do something clever with a query, not a table.

As the others have noted, we will not be able to help you until we know why you need that number to be scrupulously sequential.
 
Thanks Guys for all your help. Very much appreciated.
 

Users who are viewing this thread

Back
Top Bottom