You may receive an mistake message when you lot try to insert a new tape in a tabular array that contains an Autonumber field in Access

  • Article
  • 2 minutes to read
  • Applies to:
    Access for Office 365, Access 2019, Access 2016, Admission 2013, Admission 2010, Microsoft Office Access 2007, Microsoft Office Admission 2003, Microsoft Office Access 2000

Original KB number: 884185

Symptoms

When you try to insert a new record in a table that has an Autonumber field, you may receive the following error message:

The changes you requested to the table were non successful because they would create duplicate values in the index, chief key, or relationship. Modify the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit indistinguishable entries and try again.

Screenshot of the error message after inserting a new record in a table.

Note

The tabular array may not have whatever relationships or whatsoever indexes.

Crusade

This trouble occurs when the Autonumber field has been incorrectly seeded.

Resolution

There are multiple methods that may resolve this trouble.

Method one: Perform Compact and Repair

To compact the database, follow these steps:

  1. Starting time Access.

  2. Open the Admission database.

    Note

    If yous see the Security Alert dialog box, click Open.

  3. Click the Database Tools ribbon tab and then click Meaty and Repair Database from within the Tools group.

    Note

    Previous versions of Admission may have the Meaty and Repair Database option located elsewhere, see the documentation for your specific version of Access to locate this selection.

To manually reset the Autonumber field seed, use one of the post-obit methods.

Method 2: Utilize a Data Definition query

Open the database that has the table (back-cease database) in Access:

  1. On the Create tab, click Query Design in the Queries group.

  2. In the Show Table dialog box, click Close.

  3. On the Pattern tab, click SQL view in the Results group.

  4. Type the post-obit in the Query1 window:

                      Modify Table TableName ALTER Cavalcade AutoNumFieldName COUNTER(iMaxID,1);

    Note

    <TableName> is a placeholder for the name of the <table.AutoNumFieldName> is a placeholder for the name of the Autonumber field. iMaxID is a placeholder for the electric current maximum value in the field plus one.

  5. On the Design tab, click Run in the Results group.

Method 3: Run Visual Basic for Applications code

  1. On the Create tab, click the down arrow under Macro, and so click Module.

  2. Paste the post-obit lawmaking in the Visual Bones Editor.

                      Sub ResetAuto()   Dim iMaxID Every bit Long   Dim sqlFixID Equally Cord   iMaxID = DMax("<AutonumberFieldName>", "<TableName>") + 1   sqlFixID = "Change Tabular array <TableName> ALTER Column <AutonumberFieldName> COUNTER(" & <iMaxID> & ",1)"   DoCmd.RunSQL sqlFixID Cease Sub

    Note

    The placeholder <AutonumberFieldName> represents the name of the Autonumber field. The placeholder <TableName> represents the proper noun of the table.

  3. On the Run card, click Run Sub/UserForm.

    Annotation

    Y'all must close the table before yous use either method. Y'all do not have to salvage the query or the module after y'all successfully use either method

Steps to reproduce the beliefs

  1. Create a new blank database

  2. Create a new tabular array that is named Table1 that contains the following two fields:

    Field1: Autonumber (Main Central) Field2: Text

  3. Add the following six records to Table1.

    Field1 Field2
    one A
    2 B
    3 C
    4 D
    5 E
    6 F

  4. Delete the record where Field1 has the value 3.

  5. On the Create tab, click Query Pattern in the Queries group.

  6. In the Prove Table dialog box, click Close.

  7. On the Design tab, click SQL view in the Results group.

  8. Type the following in the Query1 window:

                      INSERT INTO Table1 (Field1, Field2) SELECT 3 AS Field1, "C" AS Field2;

  9. On the Design tab, click Run in the Results group.

  10. Open Table1, and so try to add together a new record. Yous receive the error message that is mentioned in the "Symptoms" section.