CODEWITHSUNDEEP
ms-accessduplicatesforeign-keys
Kevin R. M.
Kevin R. M.

Reputation: 25

Access - duplicate parent and sub records after changing foreign key

I have an Access 2016 db with tables like these:

Master
------
| ID = PK1 | ... field = value |

Sub1
----
| ID = 1 | MasterID = PK1 | ... field = value |

SubN
----
| ID = 1 | MasterID = PK1 | ... field = value |
| ID = 2 | MasterID = PK1 | ... field = value |

I need a method to run on Sub1 that will allow me to create a duplicate of records for PK1 in Sub1, (through) SubN after changing the value from PK1 to PK2 e.g. via input value of "PK2" to a pop-up. It's a given that these will be new records containing MasterID = PK2 i.e. none already exist. So the result would be:

Master
------
| ID = PK2 | ... field = value |

Sub1
----
| ID = 2 | MasterID = PK2 | ... field = value |

SubN
----
| ID = 3 | MasterID = PK2 | ... field = value |
| ID = 4 | MasterID = PK2 | ... field = value |

There is code offered by the new button wizard to duplicate a record, boiling down to:

DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdCopy
DoCmd.RunCommand acCmdRecordsGoToNew
DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdPaste

This results in all MasterID values of the duplicate equaling PK1... Is it possible to change the MasterID (FK) value while leveraging the code above?

Upvotes: 0

Views: 55

Answers (1)

Wolfgang Kais
Wolfgang Kais

Reputation: 4100

No, you can't use that code from the wizard and change the MasterID on the fly. Also, it copies only the current record.

You have N tables named Sub1 through SubN, so why not create N queries (qryDupSub1 through qryDupSubN) that will create duplicates of the records with MasterID = [OldMasterID] and save them with a MasterID of [NewMasterID]?

The queries could be defined like this (example for Sub1) (I use "... field" just as you did):

PARAMETERS OldMasterID long, NewMasterID long;
INSERT INTO Sub1 (MasterID, ... field)
SELECT [NewMasterID], ... field
FROM Sub1 WHERE MasterID = [OldMasterID];

You can create code that executes all N queries in a loop and take the values for the two parameters from controls on the Form (Textboxes txtOldMasterID and txtNewMasterID in my example):

Const N As Integer = 10 ' the number of queries
Dim i As Integer

With CurrentDb
    For i = 1 To N
        With .QueryDefs("qryDupSub" & i)
            !OldMasterID = Me.txtOldMasterID
            !NewMasterID = Me.txtNewMasterID
            .Execute
            .Close
        End With
    Next
End With

Upvotes: 1

Related Questions