I work at a ministry that provides housing for people. My database has a table called ResidentT which contains all of our clients personal info. I also have a table called ResidentArchiveT. When a client moves out (or moves back in), with one click of a button on the Main Menu, I can switch all of their info from one table to the other, and vice versa. This keeps our main ResidentT from getting too cumbersome for basic day-to-day operations, as combo boxes, reports and lists are not populated with people no longer living here. Yet, we can keep their info in the archive table, in case they come back to live with us at some point.

In both tables, each Resident has their own unique ResidentID. It is the primary key, an AutoNumber, and is set to "Yes (No Duplicates)" on both tables.

Everything has worked fine until this past Friday. I do not know what I did wrong, but for some reason I am now unable to create a NEW AutoNumber on the ResidentT. When I create a new record on the ResidentT, instead of getting a NEW ResidentID, it uses an already claimed ResidentID AutoNumber (from the ResidentArchiveT).

What did I do wrong? How do I fix this?

Thank you for your help!