0

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:

  1. I have an After Insert data macro that creates a row in the child table.
  2. The data macro fires and a row is created in the child table with the corresponding foreign key.
  3. Microsoft Access is not aware that I have created that row in the child table.
  4. 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...

matt
  • 2,857
  • 7
  • 33
  • 58

1 Answers1

0

I generally recommend against using timers but this is a case where you could use one. On the AfterInsert set the form's TimerInterval at something like 500. Then put your Me.Requery code on the Timer event and also set the TimerInterval back to 0.

After AfterInsert event should set the TimerInterval back to 500 which will prevent the timer from firing until all records have been pasted in.

I see you are using Macros. I don't know if you can use the timer in Macros or not. I don't use macros at all and I recommend learning VBA instead of using them.

Private Sub Form_AfterInsert()
    Me.TimerInterval = 500
End Sub

Private Sub Form_Timer()
    Me.Requery
    Me.TimerInterval = 0
End Sub
HK1
  • 11,941
  • 14
  • 64
  • 99