Modules: Return a concatenated list of sub-record values

(Q)    How can I extract all values of a field from a table which is the related to another table in a 1:M relationship?

(A)    The following function fConcatChild can be used in a query

SELECT Orders.*, fConcatChild("Order Details","OrderID","Quantity","Long",[OrderID]) AS SubFormValuesFROM Orders;

This example is based on Orders and Orders Details tables in Northwind database which are related in a 1:M relationship. The fConcatChild simply states Concatenate all values in field Quantity in table Order Details where linking field is OrderID of datatype Long, for each value of [OrderID] returned by the table Orders.

'************ Code Start **********
'This code was originally written by Dev Ashish
'It is not to be altered or distributed,
'except as part of an application.
'You are free to use it in any application,
'provided the copyright notice is left unchanged.
'
'Code Courtesy of
'Dev Ashish
'
Function fConcatChild(strChildTable As String, _
                    strIDName As String, _
                    strFldConcat As String, _
                    strIDType As String, _
                    varIDvalue As Variant) _
                    As String
'Returns a field from the Many table of a 1:M relationship
'in a semi-colon separated format.
'
'Usage Examples:
'   ?fConcatChild("Order Details", "OrderID", "Quantity", _
                "Long", 10255)
'Where  Order Details = Many side table
'       OrderID       = Primary Key of One side table
'       Quantity      = Field name to concatenate
'       Long          = DataType of Primary Key of One Side Table
'       10255         = Value on which return concatenated Quantity
'
' Set a reference to DAO

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim varConcat As Variant
Dim strCriteria As String, strSQL As String
    On Error GoTo Err_fConcatChild
    
    varConcat = Null
    Set db = CurrentDb
    strSQL = "Select [" & strFldConcat & "] From [" & strChildTable & "]"
    strSQL = strSQL & " Where "
    
    Select Case strIDType
        Case "String":
            strSQL = strSQL & "[" & strIDName & "] = '" & varIDvalue & "'"
        Case "Long", "Integer", "Double":    'AutoNumber is Type Long
            strSQL = strSQL & "[" & strIDName & "] = " & varIDvalue
        Case Else
            GoTo Err_fConcatChild
    End Select
    
    Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)

    'Are we sure that 'sub' records exist
    With rs
        If .RecordCount <> 0 Then
            'start concatenating records
            Do While Not rs.EOF
                varConcat = varConcat & rs(strFldConcat) & ";"
                .MoveNext
            Loop
        End If
    End With
        
    'That's it... you should have a concatenated string now
    'Just Trim the trailing ;
    fConcatChild = Left(varConcat, Len(varConcat) - 1)
        
Exit_fConcatChild:
    If Not rs Is Nothing Then
    	rs.Close
    	Set rs = Nothing
    End If
    Set db = Nothing
    Exit Function
    
Err_fConcatChild:
    Resume Exit_fConcatChild
End Function

'************ Code End **********