Multi-Line Paste Failure Into SubForm Leaves Uncommitted Internal Transaction RRS feed

  • Question

  • I have created a very simple Access 2010 database.  It has 2 tables: tblParent and tblChild.  Each has a primary key on a single numeric column: called ParentId and ChildId respectively.  Each has an additional Index (No Duplicate) on text name columns: called Parent and Child respectively.  tblChild has a numeric column ParentId.  There is a relationship between tblParent and tblChild on ParentId.  There is a form frmParent based on tblParent with a subform frmChild based on tblChild.  frmChild has Link Master Fields = ParentId and Link Child Fields = ParentId.  It would be easier to attach the database but I don't appear to be able to do so?  Hopefully you will be able to follow me so far :)

    I now try the following :

    1. Open frmParent

    2. Copy TWO child rows to the clipboard for a ParentId (in my data = 33)

    3. Select new blank child row and paste.

    4.Click OK to clear the resulting paste errors due to uniqueness violation of Child text collumn.

    So far so good - or is it?

    5. Manually create (not paste) a new row with a unique child name and save.

    6.Close frmParent

    7. Reopen frmParent


    Looks like after the failed paste Access is stuck in an uncommitted internal transaction; any data changes thereafter are not committed.  Surely it should have rolled back the internal transaction?

    It works as you would expect for a single row paste - presumably because the internal transaction is easier to manage.

    Let me know if you want a copy of the database.

    Saturday, April 21, 2018 5:42 PM

All replies

  • Hi Derek,

    Just so I'm clear... When you click OK to the error message, Access cancels the paste operation, correct? I mean, you don't see any of your pasted records on the form, do you? If so, do you still see the pencil icon on the record selector?

    Just curious...

    Saturday, April 21, 2018 7:10 PM
  • Hello Derek lan Autin,

    >>2. Copy TWO child rows to the clipboard for a ParentId (in my data = 33)

    >>3. Select new blank child row and paste

    I failed to understand how did you do this? I could not do the operation on a form. How did you do this? Could you please detail it for me?

    Best Regards,


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, April 23, 2018 7:34 AM
  • Derek - How are you saving the manually created record? I have had users type in a new child record and then close the form and the record is not saved. I got around it by forcing a command which will save the new record in the child sub it it's Unload event:

    If me.Dirty Then Me.Dirty = False

    Bill Mosca

    Monday, April 23, 2018 9:34 PM