Hello There;
I have been working on this the last 3 days, and i can not seem to find out why this is not doing what I want it too. I hope someone can look this over and maybe help me understand why not. I am trying to update a subform Record IDs when the main form record ID gets updated. This is so all the records with the old ID will again match the updated record id on the main form.

This is the code i am working with right now. I am hopeful someone can direct me on the right path?

code

Private Sub AgencyName_AfterUpdate()
    Dim newRecordID As Integer
    Dim subformRS As Recordset
    Dim Response As Integer
    
    Response = MsgBox("You are about to change the Record ID to the new selection, do you wish to continue?", vbYesNo + vbQuestion, "Confirm Change")
    If Response = vbYes Then
        Me.AgencyCode = Me.AgencyName.Column(0)
        Me.FDRecordID = Me.AgencyCode & "-" & Me.RecYear & "-" & Me.SeriesNum 'this creates a new record ID with the new 2 digit code of agency and keeps same date
        GoTo NewRecords
    
    ElseIf Response = vbNo Then
        Undo
        btnClose.SetFocus
        Form_Current
    End If
    Exit Sub

NewRecords: ' here we want to take the new record ID generated and replace all the old corresponding subform record ids to the new one

            newRecordID = Forms!DeptEditFillRecordF!FDRecordID.Value
            
            Set subformRS = Forms!DeptEditFillRecordF!CylFillsListSF.Form.RecordsetClone
            subformRS.MoveFirst
            Do Until subformRS.EOF
                If subformRS!FDRecID = FDRecID Then
                    subformRS.Edit
                    subformRS!FDRecID = newRecordID
                    subformRS.Update
                End If
                subformRS.MoveNext
                Loop

                Forms!DeptEditFillRecordF!CylFillsListSF.Form.Requery
            
End Sub