Show related records in a string

[cbrsix]

I have a table with accounts and their owners then I have a Joint Owners table where there can be more than 1 joint owner. A 1 to many relationship exits with the AccountNumber. I have a report that shows the owner on one line and I want to have the joint owners added in either of the following formats.

John Doe (owner)
Jane Doe (joint owner), Jeff Doe (joint owner)



or

John Doe (owner)
Jane Doe (joint owner)
Jeff Doe (joint owner)

I saw this post from Allen Browne but not sure how to add this to my report already and to get it in the second option above (my preference if it can be done).

http://allenbrowne.com/func-concat.html

[ItsMe]

If you are creating a report there should not be a need to write VBA to list out both the owner and all relative joint owners in the detail of the report. Granted it is not in one single textbox. But, would be all in one area of the detail. All you need is a query for tblOwners and tblJointOwners. Building your report from this query using the wizard should step you through the process.

If you view your query in SQL view you can see the recordset. If you were to build a form you could place this SQL behind the form and call on it. Below is an example of an SQL you could place in a form's control to display your info. You could put this in a click event to change the form's recordset to view (filter) only the curent record. This is assuming you have a query named qryOwners.

dim strSQL as String
dim varAccountNumber as Variant
varAccountNumber = Me.AccountNumber.Value

strSQL = "SELECT [qryOwners].[AccountNumber], [qryOwners].[owner], [qryOwners].[JointOwner]" _
& "FROM [qryOwners]" _
& "WHERE (qryOwners.AccountNumber)=" & '"varAccountNumber"' & " _
& "ORDER BY [AccountNumber];"

me.Recordsource = strSQL

[cbrsix]

This is not for a form. This is for a report.

I went the subreport way to show the Joint Owners for each account. My problem now is that if an account doesn't have any Joint Owners, the report does not function properly. It comes back with the fields as #Type! when they should still be filled with the single account owners information and the subreport showing the joint owners should just not show anything.

[cbrsix]

Nevermind. I forgot to check the join properties of the relationship.