convert many records into one string

[Happy YN]

I need to pop up a message box which includes information from many records as a string
e.g The following subjects do not have a teacher assigned to them
Science
History
Maths


I don't know how to loop through all records of one field of a query and create a string from them
I would seperate them with a vbCrlf

I tried to create an array of the recordset but I am finding difficulties...

Is there a quick and simple way?
Thanks

 

[scottfarcus]

Use the query as a recordset and loop through the resulting recordset capturing the field each time.

Dim rs as DAO.Recordset
Dim i as Integer
Dim iRecordCount as Integer
Dim strString as String

Set rs = CurrentDb.OpenRecordset("qryName")
iRecordCount = rs.RecordCount

For i = 0 to iRecordCount
If strString = "" then
strString = rs!FieldName
Else
strString = strString & vbCrLf & rs!FieldName
End If
Next

MsgBox strString

Set rs = Nothing

 

[Happy YN]

Thanks for your reply which seems exactly what I wanted but I am getting errors opening the recordset
error 3061
too few parameters. expected 4

this is the sql of the qry. (I know its based on other queries but I am hoping we'll catch the fault here since the sql of the ones its based on is long and complicated!)

SELECT [PupilsQueryReportWithout order].Category
FROM [PupilsQueryReportWithout order] LEFT JOIN PupilsQueryReport ON [PupilsQueryReportWithout order].Category = PupilsQueryReport.Category
GROUP BY [PupilsQueryReportWithout order].Category, PupilsQueryReport.Category
HAVING (((PupilsQueryReport.Category) Is Null));

Thanks for any help!!