Thank you for your reply!!
The report should look like this:
John Doe Address CSZ
Product1; Product2; Product3
The database is similar to Nwind with Customers, Orders, Order Details, & Products tables.
While I was waiting for a reply, I found the following post on The Access Web but I keep getting a syntax error when I plug it into my query. (Yes, I'm a newbie, but I did remember to plug in my own object names...) Maybe I'm not referencing them correctly. I dunno. Maybe your code will be better. PLEASE HELP!!!
Thank you!
(
---Posted by Dev Ashish---
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 **********
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
'
Dim db As Database
Dim rs As 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:
Set rs = Nothing: Set db = Nothing
Exit Function
Err_fConcatChild:
Resume Exit_fConcatChild
End Function
'************ Code End **********
