Duplicating MS Access main form record while keeping the parent-child link in the subform
I have a Monthly Reports entry form (data source: MonthlyReports table) with a Project Info subform (data source: Projects table) linked on ProjectID field. ProjectID is a PK in the Projects table and a FK in the MonthlyReports table.
I am trying to use the Duplicate Record button to duplicate the record in the main Monthly Reports form, edit the duplicated record and save it as a new record/monthly report for the same project. I would like to keep the Project Info (or ProjectID link) in the new record after duplicating. When I duplicate the record, it creates a new Monthly Report record with a unique number, but it does not keep the ProjectID foreign key, thus creating an unrelated record without the ProjectID foreign key. How can I modify the macro or add a VB code on click of the Duplicate Record button to keep the Parent-Child field link?
Short answer, If I understand what you're asking, is no.
When you have a relationship between a master and child table, it is a one (Master record) to many (child records) relationship. A child cannot have more than one parent. Even if you haven't defined this relationship in the database, when you link a subform to a master form by a field, that one-to-many is (properly) assumed.
What you would need to do is have the macro, subsequent to creating a new master record, create a new child record then copy the fields of the existing child record to the new record. Finally, you will need to set the value of the new child record field [ProjectID] to be the value of the newly created master record. Now you have the complete duplicate.