Display Subform records on main form as text

[AshB]

Hi all,

I hope this makes sense.

I have a form with a subform in it. The Subform relates to a junction table.

For each record on the main form, there may be more than one record on the subform. The Subform only has one field on it (a combobox).

What I want to be able to do is have a text box on the main form that shows a concatenated list of all of the records on the subform.

For example, if I have 2 subform records, 'Yellow' and 'Blue', I want a textbox on the main form record to show 'Yellow, Blue'.

Does anyone have any ideas? I have tried so many things I have found online (over about 5 hours, including a module called ConcatRelated) and absolutely nothing works!

 

[madefemere]

Hello,

A function like this ? :

[FONT=Arial][SIZE=2]Public Function Concat_Child(IdParent As Integer) As String
Dim rst As DAO.Recordset
Dim ourtxt As String
Set rst = CurrentDb.OpenRecordset("T_Child", dbOpenDynaset)
ourtxt = ""
With rst
    .FindFirst "[IdInit_FK]=" & IdParent
    While Not .NoMatch
    ourtxt = ourtxt & ![CHildName] & ";"
    .FindNext "[IdInit_FK]=" & IdParent
    Wend
End With
ourtxt = Left(ourtxt, Len(ourtxt) - 1)
Concat_Child = ourtxt
End Function[/SIZE][/FONT]

Good continuation

 

[burrina]

You might find this link useful.
http://allenbrowne.com/func-concat.html

HTH

 

[AshB]

Thanks, MadeFemere. I tried your code, but I cannot make it work. Same with the link from Burrina (which I actually referred to in my OP as not working. It either throws up a #name or #error message, depending on whether I place the textbox in a form or a report.

 

[burrina]

Have you tried debugging your code line by line and if so what is the exact error you get?
Checked your names so that they match your code?
Can you post your code? We are in the blind here!

 

[AshB]

I used the code you suggested, and then tried to change to match the names.

I have 3 tables with the following fields:

tbl_event

EventID​

EventName​

tbl_org

OrgID
OrgName​

tbl_event2org

JoinID
EventID
OrgID​

I have set up the relationships, and each table has a form. The form for 'tbl_event2org' (frm_event2org) acts as a subform within the form for 'tbl_event' (frm_event).

I would like to have a text box on each record on frm_event which shows a concatenated list of the OrgNames that have been selected using the frm_event2org subform for that record.

Public Function Concat_Child(EventID As Integer) As String
Dim rst As DAO.Recordset
Dim ourtxt As String
Set rst = CurrentDb.OpenRecordset("tbl_event2org", dbOpenDynaset)
ourtxt = ""
With rst
    .FindFirst "[OrgID]=" & OrgID
    While Not .NoMatch
    ourtxt = ourtxt & ![tbl_event2org] & ";"
    .FindNext "[OrgID]=" & OrgID
    Wend
End With
ourtxt = Left(ourtxt, Len(ourtxt) - 1)
Concat_Child = ourtxt
End Function

Edit: I also have a couple of queries set up which pull the data together so that 'OrgName' and 'EventName' appear together, but these have a new row for each combination, eg
Event 1 Org1
Event 1 Org3
Event 2 Org 4
Event 3 Org 2
Event 3 Org 1