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!!

 

[Pat Hartman]

Try changing the query to:

SELECT Distinct [PupilsQueryReportWithout order].Category
FROM [PupilsQueryReportWithout order] LEFT JOIN PupilsQueryReport ON [PupilsQueryReportWithout order].Category = PupilsQueryReport.Category
Where [PupilsQueryReportWithout].Category Is Null;

I'm just guessing as to how the query needs to be changed. You don't need the group by since you are not using any aggregate functions and you need a Where clause rather than a having. The query as it was written was asking for only categories that were null which does not make sense given that is the only column being returned.

The diference between Where and Having is WHEN they are applied. Where is applied PRIOR to any aggregation and Having is applied AFTER any aggregation.

This probably won't fix the error you are getting. That is caused when you run a query that takes parameters (this one doesn't but one of its sub queries must) without first setting the parameters.

Here is a code example of how parameters are passed:

Dim dbs As DAO.Database 
Dim rst As DAO.Recordset 
Dim qd As DAO.QueryDef
Set dbs = CurrentDb
Set qd = dbs.QueryDefs!qryQCMainSizeCheck
qd.Parameters![YourParm1] = "SomeValue"
qd.Parameters![YourParm2] = "SomeValue"
Set rst = qd.OpenRecordset