Microsoft KB Archive/275090

= ACC2002: @@IDENTITY Returns Incorrect Identity Column Value =

Article ID: 275090

Article Last Modified on 11/5/2003

-

APPLIES TO


 * Microsoft Access 2002 Standard Edition

-



This article was previously published under Q275090



Advanced: Requires expert coding, interoperability, and multiuser skills.

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



SYMPTOMS
When you enter new data into a Microsoft Access database, your entry is immediately replaced by incorrect values.

-or-

When you enter new data, you receive the following error message:

The data was added to the database but the data won't be displayed in the form because it doesn't satisfy the criteria in the underlying record source.



CAUSE
This behavior occurs because the @@IDENTITY function is used to resynchronize data after new records are added. This function returns the last identity column value created in the current session. When you add a new record, the Cursor Engine uses the @@IDENTITY function to resynchronize all records and ensure that all data is available.



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 </li> On the Visual Basic Editor File menu, click Close and Return to Microsoft Access.</li></ol>

<div class="status_section">

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

<div class="moreinformation_section">

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.

<ol>  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.

</li> Start Microsoft Access and open a new Access project based on the same Northwind database that you used in step 1.</li> Open TableXX in Datasheet view.</li> 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.</li> 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.</li></ol>

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.

<div class="references_section">