Multi-Line Paste Failure Into SubForm Leaves Uncommitted Internal Transaction (1 Viewer)

derekaustin

New member
Local time
Today, 13:42
Joined
Apr 21, 2018
Messages
4
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. Please see attached accdb.

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 column.

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

Where is teh new row you just created.

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.

What have I missed?
 

Attachments

  • ParentChild.accdb
    492 KB · Views: 44

Pat Hartman

Super Moderator
Staff member
Local time
Today, 09:42
Joined
Feb 19, 2002
Messages
42,970
The form is working as I expect it to work. You can't use copy/paste when what you are copying has a unique index on it.
 

derekaustin

New member
Local time
Today, 13:42
Joined
Apr 21, 2018
Messages
4
Hi Pat - thanks for replying. The failure to paste a unique index into a new record is as expected. What surprised me was that after the paste failure I could go on to add records that were lost when I closed the form. Looks like the paste failure has left the form in the middle of an uncommitted internal transaction with no way of rollback or committing. In my simple example the paste failure is violation of a unique index, however, the paste failure could be for any number of reasons - validation on a pasted field, a business rule in the form before update event etc. The paste failure in itself is not the issue, it's the loss of new records after the paste failure that concerns me.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 09:42
Joined
Feb 19, 2002
Messages
42,970
The pasted records are not visible to me in the subform when I get the paste error so I can't reproduce this.
 

derekaustin

New member
Local time
Today, 13:42
Joined
Apr 21, 2018
Messages
4
The pasted records not being visible after the failed paste is not the problem - as you correctly pointed out that's to be expected when pasting a unique key. In this post attempting to paste unique keys is only a means to an end i.e. a simple way to generate a paste error.

After the paste error try steps 5 to 7 (see original post) and you will reproduce the issue i.e. records added manually (not pasted) after the paste failure are lost when the form is closed.

This seems a big deal to me. It looks like an issue with Access not rolling back a multi-line paste internal transaction after encountering an error?
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 09:42
Joined
Feb 19, 2002
Messages
42,970
I added one row by typing - paty6
Then I pasted two and got the error message.
Then I typed in a new row and at that time it said pat7
I pressed the refresh button and now shows #Name? with the error message about transactions.

So it looks like you did find a bug. The transaction the form uses is not cleared after the append failure.
 

Attachments

  • PasteError.JPG
    PasteError.JPG
    40.1 KB · Views: 124

derekaustin

New member
Local time
Today, 13:42
Joined
Apr 21, 2018
Messages
4
Thanks again Pat. Good to know I'm not going mad. What would you recommend I do next? The issue appears to exists in MS Access 2010, 2014 and 2016. MS should address this ASAP?
 

Users who are viewing this thread

Top Bottom