form with many to many relations

[manudega]

Hallo, I have a serious problem and I hope that you can help me.

I have a database with 2 tables that have a many to many relationship: the Journalist table and the Article table. These two tables are connected with a junction table, that is called Pulishing Year, that contains the two primary keys of the other 2 tables and an own primary key, the Publication Date.
I would like to do a form that contains all the journalists with a subform that contains all the articles that a journalist writes or have written.
The problem is that when I would like to enter a new article in the subform, access tells me that the field cannot be updated, but then it works and I can enter a new article. But it is wrong. I don't understand why access tells me this error. Then I would like that all the articles in the subform can't be modified or deleted, but I can enter new articles. Is this possilble?
I have to do this work for university, so its important and I hope that someone can help me, also only for one question.
How should I manage this problem of form and subform of many to many relations? and how can I lock the inserted articles, so that I can't modify or delete the data, but I can enter new articles?

THank you!!!!!!

[nico5038]

The simples solution is to use a main form and two "nested" subforms, all based on the corresponding table:
1) Journalist
2) sub -> Pulishing Year
3) sub -> sub -> Article

Access will propose a "link key" you can accept.

This will give the possibility to add in the "sub-sub" form new articles without a problem.

Nic;o)

[manudega]

Thanks, but I have to ask you how I can create a subform in a subform. And how I can link them together? Is it possible for a many to many relationship with a junction table?
Thank you

[NeoPa]

@Manudega
There are a number of ways. The way you choose will depend on exactly what you want (which you haven't explained).

1. If you only want to see new records, then set the Data Entry property of your form to Yes.
2. If you want to be able to browse other records without updating them, then you could add some code in the OnOpen & OnCurrent event procedures that set the form (or the various controls if you prefer) to locked or not depending on Me.NewRecord value.

PS. I assume when you say Pulishing Year you really mean [Publishing Year].

[nico5038]

To create a subform in another subform, just activate a control in the subform (to make sure it's having the focus) and use the Toolbar to add a subform.

When the table has been selected for the subform Access will propose the linkage fields and these can be accepted to have Access filter the sub (and sub-sub) form for showing only the records that are belonging to the "master" table.
I normally use the relationships window to join the tables and to "guide" Access to propose the proper linkage fields. Just check that window and try to add your tables and their relationships.

A many to many (N:M) relation table is the way to make sure that one article can be created by multiple Journalists. But when using the sub / sub-sub construction you'll always add an article to one Journalist. Making it a multiple Journalist article will need an additional action on the sub form (or a separate form) to add a record for that additional Journalist.

Nic;o)

[manudega]

Ok thanks now it works and the error doesn't anymore appear.
But now I have a last problem. I would like that the journalist form remains unchanged, in other words that the data could be changed, deleted or added, but I want my subform: that I can't modify or delete the already entered articles, but I should be able to enter new data...
How can I do this? When I modifiy Allowadditions and so on on the property sheet of the form, also the main form changes, but I want these options only for the subform.
Can you help me please????? :-)

[nico5038]

The easy way is to open the subform from the forms section "straight away" and te the properties.
The other way is to select the subform on the main form and make sure that the small square top left is clicked and holds a small black square to indicate that the form itself has been selected. The properties window will display the properties of that form.

Nic;o)

[NeoPa]

See my post #4 and make sure to apply this to the form that is contained within your subform control (Nico has provided more specific instructions on how to do this). From your description I expect you require the second option.

[manudega]

OK thanks, excellent work.
Now my database works as I wanted.
Thank you and happy new year!!! Bye

[NeoPa]

Very pleased we could be of help.

Happy New Year to you and Welcome to Bytes!.