Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

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

Discussion in 'Information Technology' started by Brad Kimbrell, Jul 28, 2006.

  1. Brad Kimbrell

    Brad Kimbrell
    Expand Collapse
    Guest

    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.
     
  2. Loading...


  3. arthurjr07@gmail.com

    arthurjr07@gmail.com
    Expand Collapse
    Guest

    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
     
  4. Allen Browne

    Allen Browne
    Expand Collapse
    Guest

    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

    --
    Allen Browne - Microsoft MVP. Perth, Western Australia.
    Tips for Access users - http://allenbrowne.com/tips.html
    Reply to group, rather than allenbrowne at mvps dot org.

    "Brad Kimbrell" <bkimbrell@qx.net> wrote in message
    news:1148881775.211516.46500@i40g2000cwc.googlegroups.com...
    >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.
     
  5. Brad Kimbrell

    Brad Kimbrell
    Expand Collapse
    Guest

    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.
     

Share This Page