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!!!!!!

 

[pbaldy]

Try setting the Allow Edits and Allow Deletions properties of the subform to No, leaving the Allow Additions Yes. I think that will do what you want.

 

[manudega]

No unfortunately I didn't find these options only for the subform. When I modify the options "AllowEdits" and "AllowDeletions" etc. in Property Sheet in Design View, also the rest of the main form will change, and that is not what I want. I would like that the main form remains unchanged, that I can add, delete and edit data, whereas in the subform nothing could be changed, but I can add new articles.

Please help me!!!!!

 

[dcb]

Is it then a "subform - form" or a table inside a "subform child control"?

 

[manudega]

A form with a subform. But I think that I have a problem with my many to many relation, or is it possible to create a form with subform when you have many to many relation with a junction table that connects the 2? And how do I have to build this form? should I do 2 forms separately and then connect them? But how can I do this and where should I include my junction table????

 

[manudega]

It is a form with a subform. But is it possible to create it with a many to many relationship that have a junction table? Maybe I should create 2 different forms and then connect them, but how can I do this with the junction table? how can I connect them and where I have to insert my junction table, otherwise the 2 tables dont understand that they are connected. Help meeee!!!!

 

[gemma-the-husky]

the problem is presumably that the journalist table is related to the publishing table, and not to the article table

so when you create a new article, you THEN have to populate the publishing table with the (new) article id, and the current journalist.


it just needs a bit of careful thought to manage the data - in this case you probably dont have a true parent-child relationship, so you will have to set the linking fields in code, as access cannot do it for you automatically


incidentally, in your system can an article have multiple authors, and can it be published in multiple publications. you may need to consider these and similar possibiliites

 

[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?????

 

[cckeel]

i am also having problems with forms for many-to-many relationship. I have a table "Videos" and another table "Actors." I then have a third table for the m-m relation that only has three fields, the RelationID, VideoID and ActorID. I cannot figure out how to (1) Show the Actor Names from the Actor Table with each Video. Secondly, I cannot figure out how to add a new video, have a listbox with Actors on the AddVideo form, and then populate the join table. Does anyone have an example of this that I can use???

 

[pbaldy]

How about

http://www.baldyweb.com/MultiselectAppend.htm

 

[cckeel]

I will try this, thank you for responding!. You have a wonderful website. Thank you for that as well.

 

[pbaldy]

Happy to help, and welcome to this site!