ACC2002: Identity Increment Values Greater Than One Are Not Calculated Until the New Record Is Saved (282389)


The information in this article applies to:

  • Microsoft Access 2002
This article was previously published under Q282389
Novice: Requires knowledge of the user interface on single-user computers.

This article applies only to a Microsoft Access project (.adp).

SYMPTOMS

When you add a new record to a Microsoft Access project (.adp) table, and the primary key identity column has an increment value of 2 or more, the value in the primary key column appears to be incremented only by 1 until you save the record. When you save or commit the record, the primary key column value is calculated correctly by using the increment that you specified in the Identity Increment property.

CAUSE

This behavior occurs because the value of the primary key column identity is not calculated until the AfterUpdate or AfterCommitTransaction events have occurred.

RESOLUTION

In the case where, in a second table, you are generating records that use the value of the identity column in the current new record, be sure that you force the current new record to be saved before you create the dependent record. Otherwise, the dependent record may use the incorrect identity column value, which results in a data-integrity problem.

STATUS

Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.

MORE INFORMATION

Steps to Reproduce the Problem

CAUTION: If you follow the steps in this example, you modify the sample Access project NorthwindCS.adp. You may want to back up the NorthwindCS.adp file and follow these steps on a copy of the project.

  1. Start Microsoft Access and open the sample project NorthwindCS.adp.
  2. In the Database window, under Objects, click Tables, and then click New.
  3. Create the following table:
       Column Name     Data Type     Length
   -------------------------------------
     KeyID           int           4
     Data            char          50
  4. Select the KeyID column, and then, on the Columns tab, set the Identity property to Yes and the Identity Increment property to 2.
  5. On the Table Design toolbar, click Primary Key to make KeyID the primary key for the table.
  6. On the File menu, click Save, name the table Table1, and then click OK.
  7. On the View menu, click Datasheet View.
  8. In the Data column, type Alpha, and then press TAB twice.

    Notice that the first record is assigned a KeyID value of 1.
  9. In the Data column of the next record, type Bravo.

    Notice that the KeyID value for this record is only 2.
  10. Press TAB and notice that the KeyID value for the second record is now 3, as expected.
Modification Type:MajorLast Reviewed:10/23/2003
Keywords:kbbug kbpending KB282389
©2003 Microsoft Corporation. All rights reserved.