Get Identity number after insert record

[gravyboy]

NB imagine the ID field is called MainID . . . . .

rst.Open "SELECT * FROM tblMain WHERE 1=2", gconnSQL, adOpenKeyset, adLockPessimistic ' Blank ADO recordset
rst.AddNew
rst.Fields("MainDate") = DateSerial(2002, 2, 25) 
rst.Fields("MainTimestamp") = DateSerial(2002, 2, 25) & " " & TimeSerial(8, 0, 0)
rst.Fields("MainDetail") = rstSQL.Fields("MachineSerial")
rst.Update

Then the value for rst.Fields("MainID") will be the value you require.

[JoshT]

If you're using a MS-SQL stored procedure to do the insert, you can immediately get the @@IDENTITY value after the insert and save it to an output parameter.

[gravyboy]

Quite true . . .

[TheBao]

Both work.

Thanks.