@allanb
I see you have not had a contribution on this for a week.
Some advice do not use NAME as a word for naming fields. It is a reserved word and can cause problems. I notice you mention a query, my assumption is that your data is normalised properly and that your query gathers its source data from separate tables.
To help you, below is a function that expects a numeric
'learnerID' data type of the datatype LONG INTEGER to be passed to it. (If yours is text then it will have to be reflected in the SQL string where the LearnerID is mention)
To get this working open a New Module and paste the function directly into it and save it
The function now becomes available to your query in that you can add it as any other column for instance in the query grid just type in a new column the fieldname as
All Comments: CommentsAll([LearnerID])
and it will return the concatenation of ALL comments but it will display it for EACH row too. This is not so painful to look at if you are simply creating a form that displays a single question record. You can assign place a textbox on the form and set its controlsource to the additional column 'All Comments'
Below is the function
- Function CommentsAll(learnerId As Long) As String
-
'loop a column of comments depending on the NUMERIC value of learnerid passed
-
'to the function and list the comments per record.
-
'but concatenated so that is is a list format suitable of use in a textbox
-
'the learnerid is assumed nueric value and that it exists in each row to identify
-
'the row. If you dont want the vbNewline then replace with Space(1)
-
Dim db As DAO.database
-
Dim rs As DAO.Recordset
-
Dim str As String
-
Dim mysql As String
-
Set db = CurrentDb
-
mysql = "SELECT Comment FROM tblLearner WHERE (((tblLearner.LearnerID)=" & learnerId & "));"
-
Set rs = db.OpenRecordset(mysql, dbOpenDynaset)
-
If rs.RecordCount <> 0 Then
-
i = 1
-
Do While Not rs.EOF
-
str = str & UCase("This is the comment for question (" & i & ")") & vbNewLine
-
str = str & Nz(rs!Comment, "(No comment given)") & vbNewLine
-
rs.MoveNext
-
i = i + 1
-
Loop
-
End If
-
Debug.Print str
-
CommentsAll = str
-
rs.Close
-
db.Close
-
Set rs = Nothing
-
Set db = Nothing
-
End Function