List subform items on main form



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




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

For i = 1 To .RecordCount
strInvoices = strInvoices & !InvoiceNo & ", "
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


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question