How to get last inserted id

[Acke]

Basically, that is the question.

After data entry, and saving procedure I need to get the ID for that entry.

Private Sub dgmPotvrdi_Click()
Dim db As Database
Dim rs As Recordset

    Set db = CurrentDb()
    Set rs = db.OpenRecordset("klijenti", dbOpenDynaset)
        rs.AddNew
        rs!NazivFirme = Me!klijent
        rs!Ime = Me!Ime
        rs!Prezime = Me!Prezime
        rs!AlternativniKontakt = Me!AlternativniKontakt
        rs!Grad = Me!Grad
        rs!PostanskiBroj = Me!Pbroj
        rs!Adresa = Me!Adresa
        rs!Telefoni = Me!Telefoni
        rs!Fax = Me!Fax
        rs![e-mail] = Me!email
        rs.Update
       
        
rs.Close
db.Close

I need the ID that these data are stored under. I need it for the next step in the same procedure.

How is it possible to get that ID? That is the last ID inserted in that table.

 

[pbaldy]

Create a variable of Long data type. Then:

...
rs.Update
rs.Bookmark = rs.LastModified
VariableName = rs!IDFieldName

 

[bob fitz]

Hi Acke

Try this:

Dim db As Database
Dim rs As Recordset
[B][COLOR=black]Dim varID[/COLOR][/B]
    Set db = CurrentDb()
    Set rs = db.OpenRecordset("klijenti", dbOpenDynaset)
        rs.AddNew
        rs!NazivFirme = Me!klijent
        rs!Ime = Me!Ime
        rs!Prezime = Me!Prezime
        rs!AlternativniKontakt = Me!AlternativniKontakt
        rs!Grad = Me!Grad
        rs!PostanskiBroj = Me!Pbroj
        rs!Adresa = Me!Adresa
        rs!Telefoni = Me!Telefoni
        rs!Fax = Me!Fax
        rs![e-mail] = Me!email
        [B]varID = rs![NameOfIdField][/B]
        rs.Update
 
 
rs.Close
db.Close

varID will hold the ID value

 

[Mr. B]

Take a look at this Microsoft article for returning the @@identity of an auto-incrementing record ID for the last inserted row:
http://support.microsoft.com/kb/815629

 

[smig]

You can use DMAX() anytime

 

[MarkK]

DMax(), however, might query the data after another user has inserted a record and so it cannot guarantee that it returns the ID generated by your particular operation.
I like Mr Fitz's approach, or @@IDENTITY, but you can use it with DAO if you use the same database object you used to do the insert (noting that each call the CurrentDb() returns a distinct object)...

dim dbs as dao.database
dim rst as dao.recordset

set dbs = currentdb
set rst = dbs.openrecordset("YourTable")
with rst
  .addnew
  .fields(1) = "Test"
  .fields(2) = 12
  .update
  .close
end with

msgbox "Inserted record id on dbs is: " &  dbs.openrecordset("SELECT @@IDENTITY").fields(0)

... or use CurrentDb in a With block with an Insert Query...

with CurrentDb
  .execute _
    "INSERT INTO YourTable " & _
      "( Field1, Field2 ) " & _
    "VALUES " & _
      "( 'Test', 12 )"
  msgbox "Inserted record id on CurrentDb is: " & .openrecordset("SELECT @@IDENTITY").Fields(0)
end with

 

[smig]

Sure DMax() is only good for Auto Increment numbers

@@IDENTITY will also give the latest entered ID, either inserted by you or by someone else working on the same db.

I normaly use what mr. fitz suggested, but this will also work only for some kind of auto numbering, which in most cases will be Auto Increment.

 

[pbaldy]

It should be noted that Bob F's method does not work with linked SQL Server tables, which is why I use the method I posted, which works either way. @@Identity will also work, but DMax would be unreliable in a multiuser environment, presuming the goal is to get the ID associated with the record just inserted via this code, not simply the latest.

 

[smig]

presuming the goal is to get the ID associated with the record just inserted via this code, not simply the latest.

In this case I think your code is the best (This is what I do)

 

[cstickman]

This is very good and gave me an idea for an audit tracking based on unbound forms. The unbound forms was not my first option as I would have gone with bound forms, but it was what was left for me to deal with now. One day I may change them all to bound forms, but for now I do not have the time.

Using the same idea that Acke was asking about I was thinking about adding that to the before update on the form and then when the user clicked on the update button have it update the same record with the changes. Using the Variable ID recommended by pbaldy (rs.Update
rs.Bookmark = rs.LastModified
VariableName = rs!IDFieldName)

How would I write that as an update command referring to that variable ID?

 

[pbaldy]

I don't understand the question. Why would you need to update the record you just added?

 

[cstickman]

Hey Pbaldy, Thanks for responding. So the goal that I would like to achieve would be to track the changes made to the fields. So here is how the process works currently:
The user selects from a list box and that populates about 10 text / list boxes based on what they picked from the first list box. I want to be able to write to a new table the values of four text / list boxes that they can change. I want to track the original value and then the changed value. So for example say they load Task 10 and the month it is due is in June. So I want the table to write the task number that the month was June and then the user changed the month to July. I will then track the user and date/time it was changed. I can find similar audit tracking, but they are all for bound forms and not unbound. If you have an easier way or a website that has something that I am looking for that would be wonderful. So my thinking was to write an initial record to the table what the current values and then update the same record with the changes. That is why I figured I would record what the ID is so I can change it from an INSERT to an UPDATE statement. Thanks

 

[pbaldy]

The downside to unbound forms is having to do everything yourself. I've got an app with an unbound reservation form and audit trail code. What I do is when I populate the form, I also populate a local table with the record. After the update I compare the values in there with the new values, and populate an audit table with any changed fields.

 

[cstickman]

I am figuring that out the hard way with unbound forms. Anyway you could share the code or the app for me to review and adapt it for my needs? Thanks

 

[NauticalGent]

Ya know folks, I read this thread about a month ago and filed it away thinking I may or may not need it. Then it came to pass that I can in fact use this in my application.

Wasn't in love with opening an RS and editing it while users we online so looked for alternatives. Thought I hit the jackpot with the After Insert Data Macro...until I started messing around with them and quiclkly learned that MS may be on to something useful in the future but as of now, they are about as useless as teats on a boar...

 

[The_Doc_Man]

C'mon, Gent. Teats on a boar are great as comparisons for useless things, so I guess they aren't useless after all... but then, if they aren't useless, maybe we should stop comparing them to useless things... but then, they would become useless again. Darn, my head is out of it this afternoon!

 

[NauticalGent]

C'mon, Gent. Teats on a boar are great as comparisons for useless things, so I guess they aren't useless after all... but then, if they aren't useless, maybe we should stop comparing them to useless things... but then, they would become useless again. Darn, my head is out of it this afternoon!

Drugs are bad...m'kay?

 

[The_Doc_Man]

I'm high on life and sometimes just get a little giddy from the altitude.