Access Reassigning AutoNumbers - Microsoft Access Forum


	Back to Access Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Access Reassigning AutoNumbers

Kent Jamison

3 years ago

In my Access database, I maintain personnel records.  When an employee leaves, we archive all their records in Archive folders, that mimic same fields and structure as current records.  Somehow one of my users tricked Access to start reassigning old, archived Autonumbers to new records in my current personnel tables. In other words, it's been filling in the blanks in the Autonumbers in the current personnel (my primary keys).  Of course, this is causing havoc with my database, with old and new relationships.  Now Access doesn't allow me to archive or restore names correctly (when an old employee returns), etc.
QUESTION: How do I tell Access to choose the next/end number in the Autonumber sequence, and not "fill in the blanks"?

FYI: I know how to Restore an Autonumber, per the TechHelp video.  But that doesn't stop Access from "filling in the blanks."

	Kent Jamison OP @Reply 3 years ago

	Kent Jamison OP @Reply 3 years ago
The two records circled in red are new records. Access should assign AutoNumbers at the bottom of the list, but instead, it's "filling in the blanks" re-using numbers already archived. Any ideas on how to stop this?

	Kevin Yip @Reply 3 years ago
You need to add dummy records so the autonumber value will increment to the value you want. If you want it to start from 1000 instead of 1, add 999 records. The only way an autonumber field can reset like that is when someone removes the field and adds a new one. If your user can do that, then you have a far bigger problem, one that concerns security.

	Kent Jamison OP @Reply 3 years ago
I seem to remember Richard saying something in one of his videos (TechHelp or Expert, I don't recall) stating how to reset the Autonumber to the last record number used. Anyone remember how to do that?

	Kent Jamison OP @Reply 3 years ago
I found the video I was thinking of. Solution is to append records from backups, make sure they have the right (end of list) Autonumbers, and THEN BUMP THE ID number. This can be found in the Extended Video (for Silver Members and above). Name of TechHelp Video: Restore Deleted AutoNumber: Extended Edition.

	Kent Jamison OP @Reply 3 years ago
You can also Repair and Compact the database to fix the Autonumber if you haven't deleted records at the end of your database.

	Alex Hedley @Reply 3 years ago
Restore Deleted AutoNumber

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Forum.

Next Unseen

New Feature: Comment Live View