Autonumber problem

[chubbychap]

Hello all - I have a problem with the autonumbering in my database which I will try to explain.

My database uses the autonumber to create a Client number when a new record is created. The database also has the facility to remove records to a seperate table when certain criteria are met (this works fine). Therefore my main table (in which records are created) has gaps in the sequence of numbers shown. Until recently, when creating a new record, the autonumber has continued to select the next available number.

However, now it is selecting numbers that have already been generated. If that number is one that has been removed to the separate table, it allows the record to be created and saved - thus 2 records exist with the same number - one in each table. If the number it selects is already in the main table, it won't allow the save to happen. In essence I can't create any more new clients.

I have checked the properties of the autonumber field and it still says "Indexed (No Duplicates)". I don't understand how it could just suddenly stop working. Any ideas out there? Thanks

 

[Dugantrain]

The issue that you're running into is pretty common when attempting to use Autonum as meaningful data. See, the Autonum field should only be used to give a unique identity to every record, nothing more. If it's not too late in the project's life, what you should do is redevelop the methodology of this client number so that it takes the Max Client number that is currently in the database and adds one to it (basically, develop your own Client Number). To answer your original question, a Compact and Repair on the database will reset the Autonum to its original Seed (by Default, this Seed is 1). Also, if someone reset the Autonum option from Increment to Random then your results will be just that.

 

[chubbychap]

Dugantrain

Thanks for the response. I hadn't realised Autonum could be so fragile. To be honest, I had mentioned to the DB users that we may need to dump the autonumber and use a process which identified the previous highest number used, to enable the next record to be created. However, my knowledge doesn't stretch as far as being able to implement this. Do you know of a function or process that I could use to achieve this? How do I identify the previous highest number used?

Thank you again

Chubbychap