Combined field from all subform data

[treva26]

I have a subform that has a [Description] and [Drawing Number] column.
I want to get a field in the main form to contain a long string of all the [Description]s and [Drawing Number]s in the entire subform combined.
How can I do that?

For example it would put the following in a single memo field:
description1 - d32423 - descrip2 - dr4422 - description four - d1121b


The subforms Child/Master field is [Full Quote Num]

 

[RuralGuy]

See if this link helps some.

 

[treva26]

Cool thanks.

I had a bit of trouble getting that version to work on my database but it helped me find another one that worked perfectly.

Here is my final code in case anyone is interested:

(This is in a form that contains a subform of data that needs to be concatenated and put into a field on the main form. Although it didnt seem to work in the OnClose event, I assume because of the last 'Me' part.)

Private Sub SubmitECLbutton_Click()

'create SearchMemo field - combine all data from subform to use in searching
Dim strSearchMemo As String, strSQL As String
'   Use a query to retrieve all relevant records
    strSQL = "SELECT [Full Quote Num], DrawingNum FROM Items WHERE Items.[Full Quote Num]='" & Me.[Full Quote Num] & "';"
'   Open a recordset based on the query
    With CurrentDb.OpenRecordset(strSQL)
'       Check any records are returned
        If Not (.BOF And .EOF) Then
'           Loop through all returned records concatenating as you go along
            Do Until .EOF
                strSearchMemo = strSearchMemo & .Fields("DrawingNum") & " - "
'               Move to the next record
                .MoveNext
            Loop
'           Chop off training " - "
            strSearchMemo = Left(strSearchMemo, Len(strSearchMemo) - 3)
        End If
'       Close recordset
        .Close
    End With
'   Do what you need to with the concatenated result
Me.[SearchMemo] = strSearchMemo

End Sub

 

[treva26]

instead of the "Me" line I now have:

SQL = "UPDATE Quotes SET SearchMemo = '" & strSearchMemo & "'WHERE QuoteDBID = " & strMemoID(i) & ""
DoCmd.RunSQL (SQL)
strSearchMemo = ""

So Im just using SQL to put the new value into the table.

I found that I had to set the string to nothing before looping again otherwise any that should be blank just repeat the last value.