Adding data via a subform

[stemar]

I have, amongst other tables, Contractors with details such as name, address, etc. Jobs, a list of different types of jobs and a third called Contractors and Job Types, linking each contractor's name and the type of jobs they do.

In a form for Contactors I have a sub-form with a list of the types of jobs they do, drawn from Contractor and Jobs. If I try to add a type of job to the subform, when I close the form, I get an error "You cannot add or change a record because a related record is required in table 'Contractors'"

What I want to be able to do is add a job type to the subform and have an entry appear in the Contractors and Job Types table linking the new job type to the contractor on the form.

Can anyone help please?

 

[Joe4]

I believe if you "link" these two tables in Relationships, and select the "Cascade Update Related Fields" option, it should work. Just make sure that in your subform, you are entering all the necessary Primary Key fields, so Access can create a new, unique record.

 

[SydneyGeek]

You've got a Many-to-Many relationship going here... Don't know for sure, ignore if I'm wrong but are you relating Contractors directly to Job Types?
It should be Contractors --- Jobs --- Job Types, with the outer tables both linked One-to-Many to the join table. The form should then be based on Jobs, using combos to pick Contractors and Job Types.

If this is unclear, or if I have totally missed the point , paste back for help.

HTH
Denis