Write Record then need Primary Key - "No Current Record" Error?

  • Thread starter Thread starter Brad Kimbrell
  • Start date Start date
B

Brad Kimbrell

I am looking for the proper way to find the primary key (auto-numbered
field) after I perform the .Update method on the recordset.

Sample Code:

rsRecordset.Open(SQL stmt)
if rsRecordset.EOF Then .AddNew
rsRecordset.Fields("FieldName")=strData
rsRecordset.Update
strKey=rsRecordset.Fields("PrimaryKeyID")

The last statement give the error "No Current Record".

Do I have to execute the SQL statement again just to find the ID? I
need this ID in order to insert it into a child table that should be
linked to this parent table.

Thanks in advance for helping a beginner.
 
Try this..

Dim bookmark

rsRecordset.Open(SQL stmt)
if rsRecordset.EOF Then .AddNew
rsRecordset.Fields("FieldName")=strData
rsRecordset.Update


bookmark = rsRecordset.absolutePosition
rsRecordset.Requery
rsRecordset.absolutePosition = bookmark

strKey=rsRecordset.Fields("PrimaryKeyID")


HTH
 
The recordset has a LastModified bookmark you can use.

After the Update, add this line:
rsRecordset.Bookmark = rsRecordset.LastModifed

If this is a JET table (an Access table), and the field is an AutoNumber,
Access assigns it even before the update, so you can use this even before
the Update line:
strKey = rsRecordset!PrimaryKeyID
 
Amazing how simple! Move it up before the update and no problem. The
bookmark logic will come in handy as well. Thanks to both of you for
helping out.
 
Back
Top