Results 1 to 6 of 6
  1. #1
    5 Star Lounger
    Join Date
    Jan 2002
    Location
    Midlothian, Virginia, USA
    Posts
    636
    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. Subscribe to our Windows Secrets Newsletter - It's Free!

    Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    9,959
    Thanks
    126
    Thanked 1,100 Times in 1,012 Posts
    What error message are you getting?

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

  5. #4
    5 Star Lounger
    Join Date
    Jan 2002
    Location
    Midlothian, Virginia, USA
    Posts
    636
    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

  6. #5
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,325
    Thanks
    1
    Thanked 12 Times in 12 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.

  7. #6
    5 Star Lounger
    Join Date
    Jan 2002
    Location
    Midlothian, Virginia, USA
    Posts
    636
    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
  •