Extract query results into txtblock.

[Pete Printer]

Hi,
I have a staff form with a linked subform that lists training course details as
Course Date Passed
First Aid 01/01/2015
Manual Handing 02/02/2016.
ect.

I would like to use the
docmd send object to send Staff an email for their review of training
I can send a query or report as a pdf but as most remote staff only use a phone i have been asked to get the detail into the email body text.
Dlookup only returns the first row.
I understand that i need to loop through the query to get the text out so that i can include it in the body text but would welcome your help and advice with the best way to achieve this.
Thanks for looking at this.
Pete

 

[plog]

Recordsets (https://msdn.microsoft.com/en-us/library/office/ff197799.aspx?f=255&MSPPError=-2147217396) are what you need. You load a query into a recordset and then loop through the recordset record by record and construct the html needed to diisplay the data in an email.

You might google around, there probably is some code for this on the net somewhere.

 

[Pete Printer]

plog
Thanks for the starter and prompt reply i will have a look.
Regards
Pete

 

[MarkK]

A very common pattern for opening a recordset from a query, and looping thru all the rows, looks like . .

dim rst as dao.recordset

set rst = currentdb.openrecordset("QueryName")
with rst
   do while not .eof
      debug.print !Field1, !Field2, !Field3
      .movenext
   loop
   .close
end with

 

[Pete Printer]

MarkK,
Thanks for this helpful reply,
i will have a go at this and let you know how i get on
Best Regards
Nick

 

[Pete Printer]

MarkK

Tried this
Function TrainingCheck()
Dim rst As DAO.Recordset

Set rst = CurrentDb.OpenRecordset("qryStaffTraining")

With rst
Do While Not .EOF
Debug.Print !tblTrCourseName, !tblJoinDatePassed,
.MoveNext
Loop
.Close
End With

End Function

This fails with the set statement Errorr 3061 Too few Paramenters 1,

Any ideas please. the qry runs fine
Regards
Pete

 

[Cronk]

The query is a select query?

Also advisable is to ensure the recordset is at the beginning ie
.movefirst
before starting the loop.

 

[CJ_London]

or the query has a parameter to a form - form open or not queries opened in recordsets cannot reference a form

if this is the case, remove form references in the query then change your openrecordset to something like

"SELECT * FROM qryStaffTraining WHERE somefield=" & me.formcontrol

this assumes the referenced form is the one running the code if not then

....WHERE somefield=" & forms!myform!formcontrol

 

[MarkK]

This fails with the set statement Errorr 3061 Too few Paramenters 1

What line causes the error? If this . . .

Set rst = CurrentDb.OpenRecordset("qryStaffTraining")

. . . then it is a problem with the query, not the code.
If this . . .

Debug.Print [COLOR="Blue"]!tblTrCourseName, !tblJoinDatePassed[/COLOR],

. . . then the field names in blue are not present in the query.

 

[Pete Printer]

Thanks for the help you have all given to me.
I have put the pieces together and understand how and why it works.
This is a truly great recourse and i appreciate that you each took the time to help with this.

Best Regards

Pete