Concatenating multiple child records into formatted string

[emailmeeric]

Overall goal: Concatenate and format a record's information and it's
related information into a memo field to be then sent as an SMTP text
email.

Access 2003 using DAO

First thanks for looking. All of you are an incredibly valuable
resource and I really appreciate your time.

I am trying to figure out how to concatenate multiple related records
in a carriage return format (VbCrLf) into one memo field.

For example you have a problem table and you concatenate it's fields
together i.e.

Dim txt1, txt2, txtBody as String

txt1 = "Title: " & Me!ProblemTitle
txt2 = "Description: " & Me!ProblemDescription
txtBody = txt1 & VbCrLf & txt2

Then you send the txtBody string along as an email.

How would I go about concatenating multiple related records in a one-
many relationship to this string txtBody?

For example say there was a solution table that contained multiple
solutions for this problem.

tblSolution
lngSolutionID
txtSolutionDescription

I was thinking of creating a query using DoCmd.RunSQL and then
counting it's records and then trying to capture each one but no
matter which way I try I cannot get it. I have always been horrible at
opening recordsets and recursing through the records to get
information. I just have never been able to get the correct syntax.

Anyone have any ideas that might help?

Thanks,
Eric

 

[Allen Browne]

See:
Concatenate values from related records
at:
http://allenbrowne.com/func-concat.html

There's a VBA function you can copy to create the concatenated string. It
includes an optional argument for the separator, so you can use Chr(13) &
Chr(10) for the last argument to get a carriage return and line feed between
the items.