The following uses a function and the before update event of a form.
Sometimes it can be useful to have some kind of order field in the child records to indicate the order or version numbers of items. Although an incrementing Primary Key can be used child records may be in the thousands and if related to the parent you may want a simple almost ranking within the group. Which may be more meaningful when viewed filtered according to the parent.
A particular case may be where you are storing documents which have some kind of version.
Public Function GetChildCount(OrderNo as Integer) As Integer Dim intCount as Integer intCount = DCount("FKID","[ParentTable]","[FKID]=" & OrderNo) GetChildCount = IntCount + 1 End Function
This counts the number of records with the same FKID in the table called ParentTable with a FKID equal to OrderNo
Then within the before update event of the Sub_Form
Private Sub Form_BeforeUpdate(Cancel As Integer) If Me.NewRecord Then Me.Order = GetChildCount([SiteID]) End If End Sub
The If statement just ensures that when you edit a record the order is not updated to the count of the child records if a count already exists in the field Order.