Today I wrote up a quick proof of concept for a task to propagate changes to other records that matched a key of a cost reference number and a phase number.

In this case, I wrote a before update event routine to check to see if there were other records that met the criteria, ask the user if they wanted to make the update to all the records, and then cancel the event if not, but to update the other records if so.

This was a proof of concept because I wasn’t sure how the subform was going to handle making updates to a form RecordsetClone or if it would even let me do it. But this worked quite nicely and Access auto-updated the subform even though I was accessing a SQL server recordset. I was quite happy with the results. Here’s my code:

Private Sub Description_BeforeUpdate(Cancel As Integer)
    If thisController.Is_Line_Valid Then
        ' If the line is valid, we need to check if this cost reference number is already
        ' on other lines
        Dim rsClone As DAO.Recordset, strCrit As String
        strCrit = "Cost_Reference_Number='" & Me.Cost_Reference_Number & "'" & _
            " AND Phase_Number=" & Me.Phase_Number & _
            " AND ID <> " & Me.ID
        Set rsClone = Me.RecordsetClone
        rsClone.FindFirst strCrit
        If Not rsClone.NoMatch Then
            ' There are existing records with the same phase and cost reference.'
            ' Since the description in this case is changing, we should ask if they want
            ' to change it on all other matching references
            If MsgBox("This will update the description on records with matching " & _
                   "Cost Reference Numbers on the same phase.", vbOKCancel) = vbOK Then
                'Ok, update the other descriptions
                Do While Not rsClone.NoMatch
                    rsClone.Edit
                    rsClone!Description = Me.Description
                    rsClone.Update
                    rsClone.FindNext strCrit
                Loop
            Else
                Cancel = True
                Me.Description.Undo
            End If
        End If
        Set rsClone = Nothing
    End If
End Sub

Some potential gotchas to watch out for though. I think if you were filtering the form this might remove records from the recordsetclone as I believe filters and ordering can be applied directly to the recordset.

However, if your subform always showed all the associated records you might want to change, this could work very well!