List subform items on main form

[Sammie]

I need to create a text box on a main form that lists the items in a subform.
I have created a command button which sends data in the main form to an
email, and it's working great. I need to list some items on the subform in
my email.

My subform contains fields InvoiceID (autonumber) and InvoiceNO (text), and
is linked to the main form on InvoiceID. How can I create a text box on the
main form which will list the InvoiceNo (s) of the related records?

Subform fields
InvoiceID InvoiceNo
1 9141
2 9142
3 9143

Text box on main form should read
9141, 9142, 9143

 

[Beetle]

You can loop through a recordset and build a concatenated list
of Invoice numbers, then apply that list to your text box.
Something like;

***********************************
Dim rs As DAO.Recordset
Dim strSQL As String, strInvoices As String
Dim i As Integer

strSQL = "Select InvoiceNo From tblSomeTable" _
& " Where InvoiceID = " & Me.InvoiceID & ";"

Set rs = CurrentDb.OpenRecordset(strSQL)

With rs
'get an accurate record count
.MoveLast
.MoveFirst

For i = 1 To .RecordCount
strInvoices = strInvoices & !InvoiceNo & ", "
.MoveNext
Next i
End With

'trim off the trailing comma and space
strInvoices = Left(strInvoices, Len(strInvoices) - 2)

Me!YourTextBox = strInvoices

Set rs = Nothing
************************************

You could put this code behind your existing command button

 

[Sammie]

Perfect! Thank you.