We help IT Professionals succeed at work.

How populate a field with records from a query separated by commas

SteveL13
SteveL13 asked
on
I have a form that has a sub-form.  The sub-forms record source is a query and is displayed as a datasheet.  But what I'm trying to do is populate a field on the main form with data from the query in the subform.  But I need to populate the information as, for example, "Steve, Bill, David".  In other words, the results of the query separated with a comma and a space.

So if the query looks like this:

Names Example
Populate the field on the main form with "Steve, Bill, David"
Comment
Watch Question

View Solution Only

Scott McDaniel (EE MVE )Infotrakker Software
Most Valuable Expert 2012
Top Expert 2014

Commented:

You'd have to concatenate those values together if you want them to appear as a comma separated value in your main form.


What's the reason for populating data in the record's header, using data in the record detail section? Sounds like your data is properly stored, and you're trying to "flatten" it.

SteveL13

Author

Commented:
I’m not trying to populate the header. just populate a field in the detail section of the form.
Scott McDaniel (EE MVE )Infotrakker Software
Most Valuable Expert 2012
Top Expert 2014

Commented:

You wrote:


But what I'm trying to do is populate a field on the main form with data from the query in the subform.


That seems to indicate that you are taking data from the subform (your "detail" records) and using that data to populate something in the "main" form (your "header" record). 


If that's not correct, please provide examples of what you have not (i.e. the data structures, or pics of the form(s)), and what you want to do (again - pics or an excel example).



SteveL13

Author

Commented:
Actually forget the sub form. I only have it on the main form temporarily to show what should concatenate in the field on the main form.
SteveL13

Author

Commented:
I just want to concatenate the results of a query and display the concatenation in the field.
SteveL13

Author

Commented:
as demonstrated in the picture I posted.
aikimark
Top Expert 2014

Commented:
CIO
Most Valuable Expert 2015
Distinguished Expert 2018
Commented:

No need for queries - use the superfast RecordsetClone.

You can run a small piece of code in the OnCurrent event of the main form:


Private Sub Form_Current()


    Const Separator As String = ", "
   
    Dim Records     As DAO.Recordset
    Dim Names       As String
   
    Set Records = Me!subChild.Form.RecordsetClone
   
    If Records.RecordCount > 0 Then
        Records.MoveFirst
    End If
   
    While Not Records.EOF
        If Names <> "" Then
            Names = Names & Separator
        End If
        Names = Names & Records.Fields("FirstName").Value
        Records.MoveNext
        ' Debug.Print Names
    Wend
    Records.Close
   
    Me!NameList = Names
 
End Sub

subChild is the name of your SubformControl (not the subform)

FirstName is the name of the field of the recordsource of the subform.

NameList is the name of your textbox in the main form to display the list of names.