Unique autonumber field (1 Viewer)

[Roni Sutton]

I have an autonumber field which increments by one set as a unique key id. Nightly, I strip off records by input date and store them in an archive. I DO NOT want the key id associated with those records to EVER be used again. However, I discovered that the old 'compact twice and it starts over rule' is hitting my table. After a second compact (not even done on the same day), my auto number fields starts over at the first integer that is not being used whether or not it was used previously. Is there a setting I can change so that it will never pick up a number it's used before. Would the random setting be better than the incremental one?

 

[Pat Hartman]

The random assignment setting won't help you. It works when all the data remains in the table because if the random number generator happens to generate a number that has already been used, it simply generates another until it comes up with one it can use. Since you are removing all the rows nightly, you won't have this fail safe.

If you can control the order of events so that you compact BEFORE the rows are deleted, you'll always have data in the table when the db is compacted so the autonumber won't reset. If you can't control the sequence, you'll need to change the table structure. You can create a new table that has only the autonumber. You would then change your current table so that the autonumber is a long integer. Then create a 1-to-1 relationship between the two tables. Check the enforce RI box and select Cascade Delete. You'll need to change your form so that it is based on a query that joins the two tables. In the BeforeInsert event of the Form copy the autonumber field from the autonumber table into the long integer field of your original table. Now your delete query can delete the records from the main table but the autonumbers remain in the new table.

 

[Roni Sutton]

Thanks. I was afraid I would have to do something like that.

You're a great resource here. Thank you!