how to combine multiple child records into One Record

[mancusoj]

I have two tables one with Parents information and another table with children information. I want to be able to combine the parents with all there children in one record for printing labels. I have a module that is suppose to combine the information. Its not working.
Function CombineChildRecords(strTblQryIn As String, _
strFieldNameIn As String, strLinkChildFieldNameIn As String, _
varPKVvalue As Variant, Optional strDelimiter) As Variant

Dim db As DAO.Database
Dim qd As DAO.QueryDef
Dim rs As DAO.Recordset
Dim strSQL As String
Dim varResult As Variant

Set db = CurrentDb
Set qd = db.CreateQueryDef("&quot

If IsMissing(strDelimiter) Then strDelimiter = "; "
strSQL = "SELECT [" & strFieldNameIn & "] FROM [" & strTblQryIn & "]"
qd.SQL = strSQL & " WHERE [" & strLinkChildFieldNameIn & "] = [ParamIn]"
qd.Parameters("ParamIn&quot.Value = varPKVvalue

Set rs = qd.OpenRecordset()

Do Until rs.EOF
varResult = varResult & rs.Fields(strFieldNameIn).Value & strDelimiter
rs.MoveNext
Loop

rs.Close

If Len(varResult) > 0 Then varResult = Left$(varResult, _
Len(varResult) - 2)

CombineChildRecords = varResult

Set rs = Nothing
Set qd = Nothing
Set db = Nothing
End Function
And a query that takes the data through the function to display it as one record.
SELECT Parents.ParentID, Parent.FName,
Parents.LName, CombineChildRecords("Children","Name","ParentID",[ParentID],",&quot AS ChildrenList
FROM Parents;
I keep getting compile errors.
Please help

 

[JimRich]

I would use a make table query. With both tables related on the proper fields you simply pick the fields you want in from both tables. The result will create a new table with the data that you selected. Practice several times until you get the data you want then change it to the make table query or simply use the query as the record source for your label form.



JimRich