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

[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.

 

[arthurjr07]

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

 

[Allen Browne]

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

 

[Brad Kimbrell]

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.