Can primary key autonumbering be reset to lowest available number? 1

[Shinken]

I have the primary key of a table set as type autonumber, to (obviously) automatically set an incremental key number. If I delete the latest 1 or more rows (records), the primary key numbering skips the now-deleted row numbers and leaves gaps in the numbering. The Autonumbering function doesn't appear to allow a manual override.

Perhaps I'm too OC (Obsessive Compulsive, not Orange County), but the gaps in numbers bother me.

1. Is there a way to get the autonumbering function to select one number above the highest number in use (in that column/key)?

2. Alternately, is there a way to override the autonumbering function and manually enter a number when desired?

3. Is there a way to get the autonumbering function to fill in gaps in the numbering, rather than using the highest previously auto-assigned number?

Thanks,

S

 

[PHV]

Take a look here:

http://www.tek-tips.com/faqs.cfm?fid=184

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Shinken]

PS - I do realize that I can avoid autonumbering by coding something, such as the number of records plus one, or seeking the highest number in the row, etc., but if there's an argument or parameter to the autonumbering, it would save me a little time. I'm not really compulsive about this and realize that every <esc> or delete will create gaps in the numbering, but it's worth asking.

Fortunately, the table is new and has only a few rows in it at this time.

Thx,

S

 

[Shinken]

To PHV - yikes that was fast! I wrote my PS only a few minutes after the original post, and after submitting the PS I noticed you had replied. The reference was a nice piece of code and interesting to look at, but I think that if I decide to change from autonumber to number and write some code, it need only be a few lines. Something like checking for the highest num in the column and then assigning a number one higher.

In any case, thanks for taking the time to reply. It's appreciated.

S

 

[KenReay]

If you just want get rid of skipped numbers at the "end" of the table, try compacting the mdb

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[PHV]

The bottom line is: NEVER use an autonumber as a PrimaryKey referenced as a ForeignKey in another table.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Shinken]

To Ken Reay: Compacting will be good for a cleanup, but isn't an ongoing solution and it looks like changing the key to a incremented number using code is the way to go. Thanks for taking the time.

To PHV: The table is related using a different column, so it's not a ForeignKey. I've never tried using autonumber before, but decided to try it. All in all, it doesn't seem to be worth the effort.

Regards,

S