Primary key field update in form doesn't transfer subform data.

[Degs29]

I have a form/subform (one-to-many) combination with the following fields (and other irrelevant ones):



Form:
-JobNbr

Subform:
-JobNbr (invisible)
-PartNbr
-Description
-Quantity

They are linked through JobNbr. My problem is that if you wish to change JobNbr in the form, the subform data is still stored using the old JobNbr value. How do I ensure all data in the subform is carried over to the corrected JobNbr?

[Dal Jeanis]

So, you have is a subform that is linked to a query against the original JobNbr, and you want to change all the sub records over?

The typical linkage is that when you change the JobNbr in the Form, you are usually telling the subform to go look for the subrecords for the NEW JobNbr, not telling them to all CHANGE to the new JobNbr.

So, if you want to alter the key on the records in the subform, you'll need a control on the form that explicitly causes an update query to make the change. I'd have a second control for the new JobNbr, label a command button "Move records to new JobNbr", and have code that ran a query to update from the old to the new, then reset the value of the JobNbr field and blanked out the NewJobNbr field and requeried the subform.

[Degs29]

So, you have is a subform that is linked to a query against the original JobNbr, and you want to change all the sub records over?

The typical linkage is that when you change the JobNbr in the Form, you are usually telling the subform to go look for the subrecords for the NEW JobNbr, not telling them to all CHANGE to the new JobNbr.

So, if you want to alter the key on the records in the subform, you'll need a control on the form that explicitly causes an update query to make the change. I'd have a second control for the new JobNbr, label a command button "Move records to new JobNbr", and have code that ran a query to update from the old to the new, then reset the value of the JobNbr field and blanked out the NewJobNbr field and requeried the subform.

Ahh, that's the only way, huh? Thanks.

[Dal Jeanis]

I wouldn't say it's the only way, but it's a simple way that (1) will work, (2) won't confuse your user, and (3) won't confuse anybody who has to support the code later.