Get Autonumber after SQL insert

[Zigzag]

After inserting a record into a table that has an Auto number I retrieve it using the Dlast(field,table)

Is Dlast(field,table) infallible in the way that I am using it in the code below and is there a better way?

ssql = ("INSERT INTO ARF ([requisitioner],[Department Rqd],[FaultCode],[FaultPhrase],[Priority],[Request],[status],[ARF Raise Time],[Plant Number],[Drop Dead Time])" & _
"values ('SentinelUK PM Schedule',[Forms]![PM Schedule Due]![PMDepartment],'PM','Planned Maintenance','4 - PM',[Forms]![PM Schedule Due]![PMRequest],'Job Open',now(),[Forms]![PM Schedule Due]![ProgressPlantNo],[Forms]![PM Schedule Due]![DueDate]) ;")

DoCmd.RunSQL ssql

Mynewautonumber = DLast("arfid", "arf")

Any help would be appreciated.

Garry

 

[RuralGuy]

From MVP Allen Browne

Function ShowIdentity() As Variant
    Dim db As DAO.Database
    Dim rs As DAO.Recordset

    Set db = DBEngine(0)(0)
    db.Execute "INSERT INTO MyTable ( MyField ) SELECT 'nuffin' AS Expr1;"

    Set rs = db.OpenRecordset("SELECT @@IDENTITY AS LastID;")
    ShowIdentity = rs!LastID
    rs.Close

    Set rs = Nothing
    Set db = Nothing
End Function

 

[Zigzag]

Thanks for that, (I will try it out later) is there any methodology behind this being more reliable, robust or just a better way.

Garry

 

[RuralGuy]

I just trust in Allen's years of experience.

 

[Zigzag]

Thank you once again.

After i have tried it i will post an update.

Garry

 

[RuralGuy]

I appreciate that and you are welcome.

 

[gemma-the-husky]

i occasionally have issues like that - after adding an item, i read the last autonumber added, but then i tend to reread some data using that autonumber ot double check

so can you explain exactly what is allen brownes code does, in

Set rs = db.OpenRecordset("SELECT @@IDENTITY AS LastID;")

and is it possible that in a multi-user system someone else could still (in theory) do a further insert in between our insert, and this subsequent read

 

[RuralGuy]

Allen is simply availing himself of a native SQL feature that Jet supports. As for the Multi-User issue, I'm sure you will get *your* AutoNumber regardless of intervening inserts.