Adding multiple records to plain-text email (1 Viewer)

[RossWindows]

Hello everyone. It's been a while! I've managed to hold my own due to everything I learned right here on the forum, but alas I've run into a road-block.
I have this code to generate an email with plain-text
It takes a bunch of fields from a single-form (we'll call it "Deliveries") and sticks them into the subject and body.
I also have a subform (we'll call it "items") on that main form. It is a continuous form.
So the basic relationship here is "Many Items to 1 Delievery"
Now, How do I write this code, so it cycles through each item that exists on the subform and appends them to the email?

So it might look something like this:
Customer: Blah Blah
Market #: 123
Item: 1234-5678
Qty: 1
Price: 10.00
Item: 6897-1234
Qty: 5
Price: 299.00
Item: 123-456
Qty: 2
Price: 159.00
Item: 2401-0001
Qty: 13
Price: 59.00

Here is what I have so far:

[SIZE=2]Dim strRequest, strTo, strCC, strBCC, strSubject, strBody As String[/SIZE]
[SIZE=2]strRequest = "Incident " & Me![tblIncidents.ID].Value[/SIZE]
[SIZE=2]strTo = "claims@mycompany.com"[/SIZE]
[SIZE=2]strBCC = "me@mycompany.com"[/SIZE]
[SIZE=2]strSubject = "Customer Name: " & txtLastName & ", " & txtFirstName[/SIZE]
[SIZE=2]strBody = _[/SIZE]
[SIZE=2]"Customer Name: " & txtLastName & ", " & txtFirstName & Chr(13) & _[/SIZE]
[SIZE=2]"Order Number: not enabled" & Chr(13) & _[/SIZE]
[SIZE=2]"Carrier Reference #: " & Me!Child1_HDLClaim.Form!CarrierRef & Chr(13) & _[/SIZE]
[SIZE=2]"Market #: " & Me!Child1_HDLClaim.Form!MarketNumber & Chr(13) & _[/SIZE]
[SIZE=2]"Service Provider: " & Me!Child1_HDLClaim.Form!ProviderName & " " & _[/SIZE]
[SIZE=2]Me!Child1_HDLClaim.Form!ProviderOther & Chr(13) & _[/SIZE]
[SIZE=2]"Type of Damage: " & Me!Child1_HDLClaim.Form!DamageType & Chr(13)[/SIZE]
[SIZE=2]Dim strInternalDamage, strExternalDamage As String[/SIZE]
[SIZE=2]If Me!Child1_HDLClaim.Form!InternalDamage = True Then strInternalDamage = "Yes"[/SIZE]
[SIZE=2]If Me!Child1_HDLClaim.Form!ExternalDamage = True Then strExternalDamage = "Yes"[/SIZE]
[SIZE=2]If Me!Child1_HDLClaim.Form!DamageType = "Property Damage" Then[/SIZE]
[SIZE=2]strBody = strBody & _[/SIZE]
[SIZE=2]"Internal Damage? " & strInternalDamage & Chr(13) & _[/SIZE]
[SIZE=2]"External Damage? " & strExternalDamage & Chr(13) & _[/SIZE]
[SIZE=2]"Date of Damage: " & Me!Child1_HDLClaim.Form!DamageDate & Chr(13) & _[/SIZE]
[SIZE=2]"Details: " & txtReason[/SIZE]
[SIZE=2]ElseIf Me!Child1_HDLClaim.Form!DamageType = "Lost/Damaged Freight" Then[/SIZE]
[SIZE=2]' Cycle through each item and add it to the email[/SIZE]
[SIZE=2]'[/SIZE]
[SIZE=2]'[/SIZE]
[SIZE=2]'[/SIZE]
[SIZE=2]End If[/SIZE]
[SIZE=2]DoCmd.SendObject acSendNoObject, , , strTo, , strBCC, strSubject, strBody, False[/SIZE]

 

[darbid]

So the basic relationship here is "Many Items to 1 Delievery"
Now, How do I write this code, so it cycles through each item that exists on the subform and appends them to the email?

There are probably many ways of doing this but I assume you have a unique relationship (field) between the Delivery table and Items table.

If this is the case I would open a recordset Where Delivery.ID = Item.ID and then loop through the fields of the item table adding them to your Email Body test string.

 

[RossWindows]

That should work. I figured I would have to open a recordset, but as far as cycling through each record, I'm a little lost. I've tossed around ideas in my head like "For Each" or "Do Until rs.EOF" along with "rs.MoveNext" but I'm a little foggy beyond that. I like to figure things out without too much outside help, so I'll keep trying and post back if I really get stumped. Thanks for your help.