Error 'field cannot be updated' while working with (sub)form

[Sikorsky]

I have the following problem:
I'm creating a small database in MS Access 2000, I have created a many-to-many relationship between 2 tables (users & products) with a link table inbetween containing the primary keys from both the user and product table which together make up the primary key for the link table. the relations are working correct because when I enter the data directly into either one of the tables it creates the proper rows in the link table.

I created a form to edit the data in both tables (users as main form, with product as subform) when ever I type anything into the subform I get the following error 'field cannot be updated'.
It does create the record in the products table, but no record is created in the link table.

Does anyone have any ideas why this happens?

 

[Sikorsky]

Should there be any important information missing from my post or anything, please let me know.

 

[PeeJC]

Your problem is probably with the obscurities of how Access ties together relationships and indexes. If you have a table like:

Link Table
----------
Id AutoIncrement
Table1LinkId Integer
Table2LinkId Integer

providing a "one-to-many" link between two bits of data then if you have Table2LinkId set to "Yes (Duplicates OK)" then you'll get the error you have seen but if you change it to "Yes(No Duplicates)" and it will probably all work like magic!

You can note how this changes the Relationships diagram and should also provide nice "drill-down" when you view the link table data.

Hope this helps.

Cheers,

Paul