ACC2002: @@IDENTITY Returns Incorrect Identity Column Value (275090)

RESOLUTION

To work around this issue, use one of the following methods.

Manually Refresh the Records

To view the data that you have just entered, click Refresh on the Records menu. By doing so, you update your display with the most current data.

Automatically Refresh the Records

Alternatively, you can use a form to add records and use the form's AfterInsert event to refresh the form. By doing so, the cursor is repositioned on the first record after the record is added. You can use the GoToRecord method to move to a new record.

In the database window, click the table where the problem is occurring to select it.
On the Insert menu, click Form.
Click AutoForm: Datasheet in the New Form dialog box, and then click OK.
On the View menu, click Design View.
On the View menu, click Code to display the Visual Basic Editor.

Type or paste the following code in the Visual Basic Editor:

Private Sub Form_AfterInsert()
    ' Refresh the form after records are added
    Me.Refresh

    ' OPTIONAL: This code will place the cursor on
    ' the first record.  Use the GoToRecord method
    ' to move the cursor to a new record.
    DoCmd.GoToRecord , , acNewRec
End Sub

On the Visual Basic Editor File menu, click Close and Return to Microsoft Access.

MORE INFORMATION

If the current session adds records to other tables, for example, by using a trigger, the @@IDENTITY function may return the value inserted into the other table and therefore display incorrect records for the current table.

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.

Start the SQL Query Analyzer on any Microsoft SQL Server computer, and then type or paste the following T-SQL code:

USE Northwind
GO

-- Create the first table (TableXX)
CREATE TABLE TableXX
(
IDCol int NOT NULL IDENTITY (1,1) Primary Key,
F1 Char(25)
)
GO

-- Add three records into TableXX
INSERT INTO TableXX(F1) VALUES ('Blue')
INSERT INTO TableXX(F1) VALUES ('Green')
INSERT INTO TableXX(F1) VALUES ('Pink')
GO

-- Create the second table (TableYY)
CREATE TABLE TableYY
(
IDCol int NOT NULL IDENTITY (1,1) Primary Key,
F1 Char(25)
)
GO

-- Create INSERT TRIGGER for TableXX
-- to add a record to TableYY when
-- records are entered in TableXX
CREATE TRIGGER TrigOnTblXX ON dbo.TableXX
FOR INSERT
AS
INSERT TableYY VALUES(' ')
GO

This procedure creates two tables, TableXX and TableYY, inserts three rows of data into TableXX, and creates an insert trigger on TableXX that inserts one row into TableYY every time that a new row is inserted into TableXX.

Start Microsoft Access and open a new Access project based on the same Northwind database that you used in step 1.
Open TableXX in Datasheet view.
Add a new record with the value Red in the F1 field and press ENTER.

Note that the value Red is replaced with the value Blue, and its corresponding IDCol value is 1 instead of 4.
Add another record with the value Purple in the F1 field.

Note that when you press ENTER, your entry is replaced with the value Green and the IDCol value is changed to 2.

In this example, the @@IDENTITY function returns the value of the identity column inserted into TableYY by the insert trigger on TableXX. In this case, the value is 1 because there were no records in TableYY when the value was entered.

Note that this issue affects only the way data is displayed and has no bearing on the integrity of the data stored in the underlying table.

ACC2002: @@IDENTITY Returns Incorrect Identity Column Value (275090)

SYMPTOMS

CAUSE