Share via

Access 2016 - ERROR "The changes you requested to the table were not successful because they would create duplicate values"

Anonymous
2020-01-26T05:10:17+00:00

Received ERROR "The changes you requested to the table were not successful because they would create duplicate values" when attempting to insert a New Record into a table with the ID field defined as the Primary key with an AutoNumber data type.

I thought this was because of the way I was coding Mainforms and subforms, however, I believe I have uncovered the root cause. 

I have a procedure which adds a batch of records to a transaction table; the transactionID (Primary Key, Auto Number) is not part of the APPEND query and therfore the TransactionID is automatically incremented as it should. 

BUT

When I attempt to enter a new Transaction via a Master/Detail form, I can see that the TransactionID created by the AutoNumber feature does not recognize that there is a new "Max" TrasnactionID number and creates a TransactionID which already exists. SInce it is a Primary key and does not accept duplicate values, the ERROR above is observed.

For example, before the batch APPEND, the highest TransactionID was 2122. The batch process added 200 records and now the highest TransactionID was 2322. When I attempt to add a new record via a form, it attempts to create the new record with a TransactionID of 2123, which already exists as a result of the batch APPEND process.

This seems like a bug. Has anyone else experienced this scenario?

Microsoft 365 and Office | Access | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

14 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2020-01-26T19:07:59+00:00

    Hi Gina,

    Great discussion as we attempt to get to the bottom of what is happening here.

    I am trying to utilize the features of Access and limit the amount of code necessary since there are few resources to maintain these applications.

    I do not wish to "control" autoNumber at all. I would like it to work as it is intended. I understand that this is not a "sequential" numbering option. It's purpose is to generate a "unique" ID. And, I do see that depending on user actions, the number could skip but that's fine as long as the ID is unique. At least, it's OK for me since I am working with a small data set.

    Was this answer helpful?

    3 people found this answer helpful.
    0 comments
  2. Anonymous
    2020-01-27T15:17:27+00:00

    Hi,

    I did not perform a Requery. The Access DB was closed after the APPEND. Several days later, I opened ACCESS and attempted to enter the new record. Come to think of it, I believe I was able to add new records successfully after the 1st APPEND.

    One other detail that came to mind is that since I am testing this application, I deleted the batch via a query and APPENDED again. I will add this step in my testing next week.

    Was this answer helpful?

    1 person found this answer helpful.
    0 comments
  3. Anonymous
    2020-01-26T17:55:19+00:00

    Hmm, well when I want to control *Autonumber* I use a field I create ( https://regina-whipp.com/blog/?p=704 ) and let Autonumber do what it was intended to do.

    If you want to make an enhancement request see...

    https://access.uservoice.com/

    Was this answer helpful?

    0 comments
  4. Anonymous
    2020-01-26T14:01:56+00:00

    Hello Gina,

    I understand the solution but I do feel this should be addressed. It appears to me that the auto generator for Access is flawed. 

    Access has no problem auto incrementing when using APPEND and this is clearly documented by Microsoft. So why not have the auto generator recognize that the MAX value has changed?

    The most efficient functionality for my application is via an APPEND which involves the AutoNumber field. So, I will handle this anomaly programmatically.

    How do we communicate enhancement requests to Microsoft?

    Was this answer helpful?

    0 comments
  5. Anonymous
    2020-01-26T08:03:13+00:00

    If you APPEND to an Autonumber field this will happen.  So to fix just APPEND the highest number to the Autonumber field (no need to save that query) in said table then delete that record and you should be fine.  You just need to remember never APPEND to Autonumber.

    Was this answer helpful?

    0 comments