SQL Server record key

[croydon]

I am working on an Access 2003 application where the data has been upsized to SQL Server 2005.

I need to make a change to a form's processing when a new record is added. This requires inserting a record in a second table using the same key.

The key for the original table is identity (autonumber). The problem is that I cannot determine the key of the new record added. I assume this may be related to the fact that SQL Server allocates the key after the record is created.

I have searched various forums and cannot find an answer. I understand that @@Identity returns the key of the last record added. This is of no use as it is a multi-user application.

Any suggestions would be appreciated.

 

[JoeAtWork]

I suggest you use a stored procedure to create the record, and at the end of it send the @@Identity as a return value (or you could put it in an output parameter).

Even better is to use SCOPE_IDENTITY( ) instead of @@Identity, as it returns the most recent autonumber created within the scope of the stored procedure.

 

[croydon]

My understanding is that the record-id should be available after the record has been saved in Access or the user moves to a different record.

I have been playing with this function over the weekend and sometimes it has been working as expected, and other times (worryingly) it has not.

I have a text field and have set the LostFocus event to perform a Save (using DoCmd). Originally this worked ok but after adding some other controls (that do nothing special), when LostFocus-Save is run the record shows #Deleted in every field. Paging through the records in the subform, the new record cannot be found, but if the function is closed and reopened, the record is present. I can only assume the link between the main form and subform record has been lost.

I don't know whether this problem is linked to the way SQL Server processes with Access or whether it is a problem/corruption that deserves a separate thread.