Autoincrement Number problem in a Tabular Form

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have Tabular Form "OtData", which has 4 fields. The first Field is "OtNo"
and its Data Type is Numeric.

I have set the Default value of "OtNo" on Form as :
Dmax("OtNo","OtDataBank")+1
It works normal.

Problem arises when the user deletes few records some time, which is must to
be deleted. For example he deleted Record No. 3251 and 4256.
But when he goes to new record the number which is produced by Default is
the Highest Number+1, I mean the new number is 5322.
Here the sequence numbers gap comes as the some records has been deleted.

Is there a method/way by which the default value should be missing numbers
of the sequence first and then the highest number.
So that the if the user deletes any record, new record OtNo will be the
missing numbers first and then the highest value+1. This will fill the gap of
the sequence number.

Please advise.

Regards.

Irshad
 
I think this is a really bad idea.

It is possible, but with a table of any size, the performance is going to be
really slow. That is because you will have to scan up to the entire table
to determine if there is a gap. The technique would be to have an index
created on OtNo, set the index to OtNo, and read sequentially until the
current number is higher than the previous number + 1. Then if you read
through the entire table and did not find a gap, add 1 to the last number you
read.

Another method, and though faster, is almost certain to fall apart on you
would be to create a table that would keep the deleted numbers. In other
words, each time you delete a record, write the OtNo to this table. When
you want to add a new record, get the DMin of the deleted numbers table, and
use that, then delete it from the deleted numbers table. If these two tables
got out of sync, it would be a bag of worms getting them straight.

Now from a business perspective, this is a bad idea because once a record
has been deleted then reused, how does a user know if he is dealing with the
old entitiy that had OtNo 123 or the new OtNo123.

To sum it up, this is a bad idea.
 
Back
Top