Retrieving Identity Column Value in Linked ODBC Table

[Phil Hellmuth]

In an Access table, if you start to enter data into a new row in a
table, the value for an AutoNumber column is instantly populated.
However, try this with a linked ODBC table (SQLServer in my instance),
the value for an Identity column isn't populated until the entire row is
saved. Is there any way around this? In a form I've developed, I need
to grab that value to populate another table.

Thanks in advance.

 

[Allen Browne]

The value is available in the AfterInsert (or AfterUpdate) event of the
form.

That's the best event for responding to a newly created record.

 

[Jerry Porter]

Phil,

The record doesn't exist in SQL Server to link to until it is saved. I
think the best bet is to populate your other table after you've saved
the record. You can use the SCOPE_IDENTITY function from SQL Server, if
you access it using the same connection as your record creation.

A less clean option would be to create your own field to use as the
foreign key instead of the identity field. You'd have to make sure two
different users weren't creating the same value at the same time.

Jerry