how to get current record id after update

[pb21]

I have some code that creates a recordset from some tempory tables and then creates a new record in the live tables accordingly.

The next bit i need is to grab the primary key code as it is the foreign key in the next table i want to update against that product.

I dont seem to be able to grab the primary key, it returns a number but not the correct one.

here is what I have:

Dim RstCourseBk As DAO.Recordset
Set RstCourseBk = db.OpenRecordset("CourseBk")


While RstStudentDataToLive.EOF <> True

With RstStudentDataToLive
'get course detail from subform


'now add course data to live
With RstCourseBk
.AddNew
.Fields("STUDENT-DSN") = StudentID 'add student id
.Fields("COURSE-DSName") = RstStudentDataToLive.Fields(1).Value ' add product code
.Fields("Date") = Format(Now(), "dd/mm/yyyy")

.Fields("From") = RstStudentDataToLive.Fields(2).Value

.Fields("To") = RstStudentDataToLive.Fields(3).Value
.Fields("Description") = RstStudentDataToLive.Fields(4).Value
.Fields("PricePK") = RstStudentDataToLive.Fields(5).Value
.Update


End With 'rstcoursebk
'now we need the courseid of the record i just created
'lets delete that last record now its been added to the live system.
*********************************************

I NEED SOMETHING HERE TO GRAB THE PK OF THE LAST RECORD I JUST ADDED.

.FIELDS("pkFIELDNAME").VALUE DOESNT WORK

.Delete
.MoveNext
End With

Wend




regards

Peter

 

[modest]

You could use dlookup(), but generally when you do an .addnew the pointer will return to the first record in the recordset. So either do .findfirst to point to the record you just added, or do a .movelast to go to the end of the list (which should be the record you added).


Also, youre closing your "with" block before you retrieve the PK field... Im surprised youre not getting any errors from that.

Example:

With RstCourseBk
    .MoveLast
    debug.print .Fields("pkFIELDNAME").VALUE
End With

 

[RuralGuy]

Modified from a response by MVP Ken Snell

.Update
.Bookmark = .LastModified
lngAuto = .Fields("pkFIELDNAME").Value

 

[Bat17]

.FIELDS("pkFIELDNAME").VALUE DOESNT WORK
should be giving you the value from RstStudentDataToLive

Is this what you are after or are you creating a Key in CourseBk that you are trying to recover?

Peter

 

[modest]

Modified from a response by MVP Ken Snell

.Update
.Bookmark = .LastModified
lngAuto = .Fields("pkFIELDNAME").Value

Peter, the use of the bookmark here is a good solution. But you should also know that you can store the field before calling the .Update; If you are not worried about rollbacks, then the following might be the best solution for you (it requires the littlest amount of Proc/Mem usage and the least typing)

For Example:

With RstCourseBk
    .AddNew
        .Fields("STUDENT-DSN") = StudentID 'add student id
        .Fields("COURSE-DSName") = RstStudentDataToLive.Fields(1).Value ' add product code
        .Fields("Date") = Format(Now(), "dd/mm/yyyy")
        .Fields("From") = RstStudentDataToLive.Fields(2).Value
        .Fields("To") = RstStudentDataToLive.Fields(3).Value
        .Fields("Description") = RstStudentDataToLive.Fields(4).Value
        .Fields("PricePK") = RstStudentDataToLive.Fields(5).Value
        [COLOR="Red"]strPrimaryKey = .Fields("pkFIELDNAME") [/COLOR]
    .Update
End With 'rstcoursebk