Results 1 to 6 of 6
  1. #1
    5 Star Lounger
    Join Date
    Jan 2002
    Location
    Midlothian, Virginia, USA
    Posts
    663
    Thanks
    0
    Thanked 2 Times in 1 Post

    How to Read Record ID from New Record

    Access 2010... I am writing a VBA routine to load some permanent tables with data from a temporary table that contains data from a spreadsheet. As my routine steps through the records in the input recordset, after it creates one record in one table it needs to retrieve that record's ID so it can write it into the new records in a table that will be linked to it. The code I tried to use looks like this:
    Code:
                    If NewSub Then
                        rstO1!SubElementName = SubName
                        NewSub = False
                    End If
                    rstO1.Update
                    ParentID = rstO1!ComElemID
    I thought that I would be able to retrieve the new output ID after doing the .Update but it fails on the next statement. How am I supposed to do this?

    Thanks,
    Bill

  2. #2
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    10,357
    Thanks
    130
    Thanked 1,162 Times in 1,069 Posts
    What error message are you getting?

  3. #3
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,537
    Thanks
    0
    Thanked 23 Times in 23 Posts
    Do it before the Update command.

  4. #4
    5 Star Lounger
    Join Date
    Jan 2002
    Location
    Midlothian, Virginia, USA
    Posts
    663
    Thanks
    0
    Thanked 2 Times in 1 Post
    Quote Originally Posted by ruirib View Post
    What error message are you getting?
    I don't remember the exact words but it said that the source field did not exist or that there was no record or something like that.

    Quote Originally Posted by patt View Post
    Do it before the Update command.
    That fixed the problem. I had in my mind that I had to set it up that way when I had a similar situation in a previous project but I obviously remembered it wrong.

    Thanks,
    Bill

  5. #5
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,328
    Thanks
    1
    Thanked 13 Times in 13 Posts
    FYI, doing it before the Update command only works if you are using an Access backend. Will not work with a SQL backend.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  6. #6
    5 Star Lounger
    Join Date
    Jan 2002
    Location
    Midlothian, Virginia, USA
    Posts
    663
    Thanks
    0
    Thanked 2 Times in 1 Post
    Thanks for the info Mark. Bill

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •