VBA function to Pivot and Concatenate Child records

In situations where a Parent record has a limited number of children (0 to 10 works well) and you would like to list those children next to the parent somewhat like you would with a Pivoted table. Pivoting the table would however result in a massive table which is extremely wide. Pivoting and concatenating the fields can keep the resulting list within a manageable width. Take for example the following table.

ParentTable

So we have table with four header records and the children all relate to those parents. For each parent obtain a list of children

Firstly place the following function in a module

Public Function Conc(Fieldx, Identity, Value, Source) As Variant
  Dim cnn As ADODB.Connection
  Dim rs As ADODB.Recordset
  Dim SQL As String
  Dim vFld As Variant

  Set cnn = CurrentProject.Connection
  Set rs = New ADODB.Recordset
  vFld = Null

  SQL = "SELECT [" & Fieldx & "] as Fld" & _
        " FROM [" & Source & "]" & _
        " WHERE [" & Identity & "]=" & Value

  ' open recordset.
  rs.Open SQL, cnn, adOpenForwardOnly, adLockReadOnly

  ' concatenate the field.
  Do While Not rs.EOF
    If Not IsNull(rs!Fld) Then
      vFld = vFld & ", " & rs!Fld
    End If
    rs.MoveNext
  Loop
  ' remove leading comma and space.
  vFld = Mid(vFld, 3)

  Set cnn = Nothing
  Set rs = Nothing

  ' return concatenated string.
  Conc = vFld
End Function

Now set up a Query and call the function in an expression

QueryChildrenDesign

This results in the appropriate list

GroupChildrenbyParents