@identity to get last entered id from another table

[696796]

Can i do this sql statement?

sql2 = "INSERT INTO tblmaterialSupplier (materialId, SupplierID) values (SELECT @identity FROM tblmaterials, '" & intSuppId & "')"

I can't get this sql to work - i need to get the last enetered materialId from my materials table...

Alex

 

[696796]

PS also tried @@ identity

 

[tperri]

You have to actually insert it to get an @identity. Do the insert, then return the @scope_identity()

 

[jbenson001]

Do it in a stored procedure and use tperri's example