Welcome to SPN

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

Sign Up Now!
  1. Monthly (Recurring) Target: $300 :: Achieved: $95
      Become a Supporter    ::   Make a Contribution   

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.
     
Since you're here... we have a small favor to ask...

More people are visiting & reading SPN than ever but far fewer are paying to sustain it. Advertising revenues across the online media have fallen fast. So you can see why we need to ask for your help. Donating to SPN's is vote for free speech, for diversity of opinions, for the right of the people to stand up to religious bigotry. Without any affiliation to any organization, this constant struggle takes a lot of hard work to sustain as we entirely depend on the contributions of our esteemed writers/readers. We do it because we believe our perspective matters – because it might well be your perspective, too... Fund our efforts and together we can keep the world informed about the real Sikh Sikhi Sikhism. If everyone who writes or reads our content, who likes it, helps us to pay for it, our future would be much more secure. Every Contribution Matters, Contribute Generously!

    Become a Supporter      ::     Make a Contribution     



Share This Page