Inserting record with Microsoft Access

[Chris Smith]

>>>>> "Frank" == Frank Millman <frank at chagford.com> writes:

    Frank> SELECT @@IDENTITY returns the most recent of all
    Frank> inserts. If you have a complex transaction which triggers
    Frank> inserts into other tables, it may not return the one you
    Frank> want.

    Frank> This one allows you to specify the tablename, and it will
    Frank> return the most recent key inserted into that table.

    Frank> I got this from the built-in help for SQLServer. I cannot
    Frank> say whether it works for Access as well.

    Frank> Frank

I've used SELECT @@IDENTITY to good effect, but I agree that there is
no telling what the scope of the variable holding  @@IDENTITY is, and
it could turn out to be a kick in the naughty bits.

Depending on the criteria, you might effect the INSERT for a parent
record, and contiue any child record INSERTs by means of the DLookup()
function (Access-only syntactic sugar around nested SELECTs) without
ever having to know the key you would have discovered through 
SELECT @@IDENTITY.

R,
Chris