How do I update Link tables using forms?

[tcolan]

Hello,

I have 3 tables:
1. Requirements
2. Test Cases
3. Reqs Linked to Test Cases

I need to have table #3 as the table maintain the many to many relationship (in other words test cases can have multiple requirements and vice versa)

So here's my issue: I have a form in which I want to Associate the Requirements to the test cases. Since the link table only maintains 3 fields (Primary Key, Req ID, Test Case ID), I want to write to that table but be able to call the Requirement info from the Requirement table to display to the user in a series of drop downs. The issue is when I try to actually save a record I get this error:
"The changes you requested to th table were not successful because they would create duplicate values in the inhdex, primary key, or relationshsip. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again"

So it's almost as if I want to mask what the user sees and write to the Link table but instead the form is trying to write to the Requirements table and it is erroring out.

Any suggestions?

Thanks,
Tom

 

[willir]

Tom

You need to make sure the parent / master record is inserted first.

Before writing the related records, use a requery of the form / table. Although you have "updated" the record, the record including the primary key (to be used as the foreign key in the related key) is still not accessible to the system.

Make sure you are using the primary key from the parent record as the foreign key in the child record.

Richard