Hi all,

I have deleted some records to clean up data, so the auto-number has got messed up. Can anyone suggest ways to straighten the issue so that the auto-numbering gets continous again? The field CompID which was autonumbered was the primary key of the table and foreign key of the other table. Of course I want to maintain the relationship also :)

I'm a newbie to VB so I don't know much on VB.

Thanks,

Autonumbers are not intended to be contiguous. Their ONLY purpose is to provide a unique identifier for a row in a table. Don't worry about the gaps. They are natural and don't cause any problems.

Do this only on a backup copy of your data to make certain it works as expected.

Assuming one primary and related secondary table...

Delete the relationship between the tables.

Open the primary table in design view and add a new field beneath the autonumber field called OldValues, number type.

Save the table, open it in datasheet view, and select the entire original autonumber field, and copy the data, and paste it into the new field. Make sure that every record has copied the original autonumber value into the new field.

Open the primary table in design view and delete the autonumber field. Save the table. Re-open in design view and add the autonumber field back again. Save the table.

You should now have a new autonumber field with all sequential valeus, and the new number field that contains all of the original autonumber values.

Create an update query to change the value in the secondary related table foreign key field to the new autonumber values, where the foregin key value equals the old value.

Re-create the table relations, and test to make sure that you have no orphaned records in the secondary table.

Simple. ;-B

Although this will resequence the existing data, it will not prevent the same problem from occuring in the future. Any time you delete a row, you create a gap. Any time you start a new record but cancel before finishing it, you create a gap. There are other situations that cause gaps also but these are the two most common.

The bottom line is - it makes absolutely no sense to renumber existing records. It can only lead to confusion. Are you also going to change paper records and filed reports. What about documents that have been sent off site?

Thank you,

I shall try it out. Pat, I also considered your opinion. I shall get back on the issue.

Thanks once again.

Well Pat,

I think that I shall not need to fix the auto-numbering problem as they are now planning to carry forward the previous numbers. Thanks for all your advice.