grabbing id after insert into

swarv · Jul 11, 2009

I have the following code:

Code:

     SQLText = "INSERT INTO BaseData ([lastname], [firstname], [Title], [Department], [EmpTerm], [username], [sec]) SELECT '" & lastname & "', '" & firstname & "', '" & title & "', '" & dept & "', '" & EmpTerm & "', '" & username & "', '" & sec & "'"
    DoCmd.RunSQL SQLText

This works fine and inserts into the basedata table in the database.

Once inserted the table gives the new record an ID number in the ID column. Now I would like to get that ID number from the record that just got inserted and use it in the next line of code.
Is this possible?

Thanks

Swarv

RuralGuy · Jul 11, 2009

From MVP Allen Browne:

Code:

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

swarv · Jul 11, 2009

Thanks for the reply,

im not sure how to put this into context though.
what does the bit ("SELECT @@IDENTITY AS LastID;") mean?
and the showidentity?

Thanks

Swarv

RuralGuy · Jul 11, 2009

Try putting this in your form and invoke it instead of what you have like:
MyNextID = ShowIdentity()

Code:

Function ShowIdentity() As Variant

   Dim db As DAO.Database
   Dim rs As DAO.Recordset
   Dim SQLText As String
   SQLText = "INSERT INTO BaseData " & _
             "([lastname], [firstname], [Title], [Department], " & _
             "[EmpTerm], [username], [sec]) " & _
             "SELECT '" & lastname & "', '" & firstname & "', '" & title & _
             "', '" & dept & "', '" & EmpTerm & "', '" & UserName & "', '" & sec & "'"

   Set db = DBEngine(0)(0)
   db.Execute SQLText, dbFailOnError
   Set rs = db.OpenRecordset("SELECT @@IDENTITY AS LastID;")
   ShowIdentity = rs!LastID
   rs.Close

   Set rs = Nothing
   Set db = Nothing

End Function

swarv · Jul 11, 2009

cheers, seems to work, many thanks.

RuralGuy · Jul 11, 2009

Great! Allen Browne knows his stuff.

gemma-the-husky · Jul 13, 2009

RG/Allan

out of interest, I had similar issues in this respect in some databases. I know the likelihood of another user inserting a record at exactly the same time is negligible, but even so, I took a belt and braces approach to be sure

ie

1. use the SQL insert into
2. use a dlookup to retreive the max value of the autonumber id field
3. RE_READ some real unique info (say an order number) based ON the dlookup to make sure the ID is correct!

so does the @@identity do this differently? Does it ensure it only rereads data relating to the correct record?

RuralGuy · Jul 13, 2009

As I understand, it knows what *you* did and does not get confused. On the what it does, you will need to communicate with Allen Browne on that one. I believe there is a similar command in SQL Server but I'm not sure.

grabbing id after insert into

swarv

Registered User.

RuralGuy

AWF VIP

swarv

Registered User.

RuralGuy

AWF VIP

swarv

Registered User.

RuralGuy

AWF VIP

gemma-the-husky

Super Moderator

RuralGuy

AWF VIP

Similar threads

Users who are viewing this thread