The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship.
This is the error message I receive when I am halfway through keying in data into my subform. My subform's source is a Query
that joins two Tables
on a one-to-one parent-child relationship. I assume the following happens when I key in data:
- I have an After Insert data macro that creates a row in the child table.
- The data macro fires and a row is created in the child table with the corresponding foreign key.
- Microsoft Access is not aware that I have created that row in the child table.
- When I key in data that belongs to the child table in the subform, it automatically tries to add in a new row into the child table. This fails because a row already exists with the same foreign key.
Is there any way I can bypass this behaviour? Can I bridge the link between the parent and child table the moment the data macro is fired? I can't forgo the After Insert data macro because it contains some biz logic that helps identify which child table to insert into, and it's important that the row is created in the child table as soon as data is filled in the subform and not only when the child table's fields are touched in the subform.
Many thanks!
EDIT: I tried working around this by running Me.Requery
in the After Insert macro of the subform. Seems to work fine for a single record, but with a batch copy-paste the error Operation not supported in transactions. occurs.
EDIT 2: I even found this forum thread that says that it's kinda impossible to Requery
after a transaction. I'm kinda sure there should be a way around this because it seems kinda user-unfriendly...