Access VBA write multiple values to single text field on form

Question

I'm attempting to concatenate multiple values into a single text field on a form and needs help on assigning those values to the text field.

Setup: I have an access form that a user can select a drop down and choose an ID number. Selecting the ID and then clicking a button will run a query that can return one or multiple rows of results. The rows returned in the results will have duplicate data except for one field (e.g. description). I'm attempting to loop over the results and add the description to a text field on the form so that shows all the field values from each row, thus listing all the descriptions.

Example of data returned: Columns:

ID | Issue Date | Description | CBD | ECD

Results return could be 1 or more rows: example below

17-0001 | 11/30/2017 | ABC | 12/5/2017 | 12/10/2017
17-0001 | 11/30/2017 | XYZ | 12/5/2017 | 12/10/2017
17-0001 | 11/30/2017 | LMN | 12/5/2017 | 12/10/2017
17-0001 | 11/30/2017 | QAZ | 12/5/2017 | 12/10/2017

In the text box I'm trying to add the values to I want it add the description to a single text box in order.

Text box contains: Subject: ID

Body Text: ABC XYZ LMN QAZ

Here is Code I have so far: It works but I just not sure how to get the description to concatenate together.

Private Sub createAnnouncement_Click()
Dim qdf As DAO.QueryDef
Dim rst As DAO.Recordset
Dim db As Database
Dim issID As String

Set db = CurrentDb
Set qdf = db.QueryDefs("nso_announce_qry")
qdf.Parameters(0) = Forms![NSO Announcements]!annID

Set rst = qdf.OpenRecordset

annSubject = rst.Fields("Issuance Title")

Do While (Not rst.EOF)
   MsgBox (rst.Fields("Issuance Detail Description").Value)
   rst.MoveNext
Loop
End Sub

should there be a annBody variable outside the Do while that you add in each value as it loops through?

This is a common topic. Did you do a search on 'concatenate related records'? Review allenbrowne.com/func-concat.html — June7
I've used a variety of Google searches trying to find what I needed but that exact search I have not. Reading it over now. — jjones312

mnieto mnieto · Accepted Answer · 2017-12-05T12:38:38

You can "acumulate" the contents of the Description field for each record:

Dim concatenated as String

Do While (Not rst.EOF)
   concatenated = concatenated + rst.Fields("Issuance Detail Description") + " "
   rst.MoveNext
Loop
concatenated = Left(concatenated, Len(concatenated) - 1)

Access VBA write multiple values to single text field on form

1 Answers