Copy main record and its linked subform records.

[WalkieTalkie]

I want to create a duplicate main record, then duplicate its subform records, then change the ID that links the subform records to match the latest main record (the overall aim is to reduce data entry when there is only one field that is different).

So, the main form has these fields: PDMenuID, PDMenuDate, PDMode, PDTotalExchanges, PDTargetWeight.
The subform has the fields: PDExchangeID, PDMenuID, PDExchangeDate, PDStrength, PDVolume.
The subform is linkled to the main form through PDMenuID. Its a one-to-many relationship.

I have a command button with the following code:

Dim vPDMode As Variant, vTargetWeight As Variant, vPDTotalExchanges As Variant

vPDMode = Me.PDMode
vPDTargetWeight = Me.PDTargetWeight
vPDTotalExchanges = Me.PDTotalExchanges

DoCmd.GoToRecord , , acNewRec

Me.PDMenuDate = Date
Me.PDMode = vPDMode
Me.PDTargetWeight = vPDTargetWeight
Me.PDTotalExchanges = vPDTotalExchanges
Me.Refresh

End Sub

This works fine to copy the data from one record into a new one, with a new autonumber PDMenuID on the main form.

But in addition, I want to copy the data that is linked to this data in the subform, and give it a new PDMenuID so that it links to the newly created main record.

Does that make sense to anyone, and if so, can you offer any ideas as to how I can do this?

 

[Remou]

You can use an append query:

strSQL="INSERT INTO SomeTable (LinkIDName, Other, Field, Names) " _
& "SELECT (" & Me.LinkIDName & ", Other, Field, Names) " _
& "FROM SomeTable " _
& "WHERE LinkIDName=" & LinkIDOfRecordYouAreCopying 
CurrentDB.Execute strSQL, dbFailOnError

http://lessthandot.com

 

[WalkieTalkie]

Thanks Remou. What a derrr - why didn't I know about that little .Execute command?! So simple! I love this site, I learn so much.

Thanks again

Miranda