Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.
A standard AutoNumber type field (with its NewValue property set to Increment) should start at 1 (though it is possible to start with a higher number), and each new record should be assigned the next higher number, so the AutoNumber type ID field should have values of 1, 2, 3 and so on, possibly with some gaps because of deleted records. Each record should have a unique AutoNumber ID value – that is, after all, one of the points of using an AutoNumber field as a table's key field. But sometimes the process of creating new, incremented, unique AutoNumber values fails. I have seen this happen several times over the many years I have been working with Access.
I do not know what causes AutoNumbers to fail, but when it occurs, the symptom is that when a new record is created in the table with the problem AutoNumber field, it reuses an ID value that was already used in a previous record, which of course results in an error message about the key value not being unique when the new record is saved.
The sample database contains a table (tblContactsProblem) with a broken ContactID AutoNumber field, which needs to be recreated. To see the problem, open tblContactsProblem in datasheet view, scroll down to the bottom, and start entering a new record. You will see that instead of the next number in sequence, which would be 3841, another number (3304) appears in the ContactID field. This number has already been used (you can scroll up in the table to see its record), so you will get an error on tabbing away from the new record, and you won't be able to save it.
If you are lucky, there may be a quick fix for this problem: make a copy of the problem table, and check whether it increments AutoNumbers correctly. If it does, you can delete or rename the old table, and give the copy the same name as the original table. But if this doesn't work, the recovery process is more complex. To get your AutoNumbers to increment and be unique as they should be, you need to:
The #3 append query is shown below, in Design view:
There are two other tables linked to tblContacts by the ID field, tblContactAddresses and tblContactLetters. The update query that replaces old ContactID values with new ones from tblContactsNew is shown below (note that the link is between OldContactID in tblContactsNew and ContactID in tblContactAddresses):
The other linked table, tblContactLetters, can be updated similarly. After running the queries, now tblContactsNew increments its new ContactID AutoNumber field correctly – though possibly with a gap in numbering, as shown below.
After verifying that the AutoNumber field in the new table works as it should, you can rename tblContactsNew to tblContacts; you may wish to leave the OldContactID field in the new table, for reference purposes, for example when checking the ContactID value on printed documents.