Adding multiple subform records to a mail item in Access

[aspoede]

Hi,

I am trying to send an email from an Access form with the Subject line
populated with fields from a subform. The tricky part is that the
number of records in that subform changes and I need it to populate the
subject line with as many records listed. For some reason now it is
populating the correct
number of records, but the first record is duplicated that number of
times instead of each individual record.

My code:

Private Sub RMAsend_Click()

Set myOlApp = CreateObject("Outlook.Application")
Set myItem = myOlApp.CreateItem(olMailItem)

myItem.SentOnBehalfOfName = "Technical Support"
myItem.To = "RMA"
myItem.Subject = "RMA " & [RMA NUMBER] & " - " & Me![RMA DATA
subform1].Form![company] & " - "

Dim rst As DAO.Recordset
Dim strSubject As String
Set rst = Forms![RMA Entry]![Part Count
subform].Form.RecordsetClone
With rst
If .RecordCount > 0 Then
.MoveFirst
Do Until .EOF
strSubject = strSubject & [CountOfPART NAME] & " x " &
[PART
NAME] & " "
.MoveNext
Loop
End If
End With
myItem.Subject = Trim(strSubject)



EXAMPLE: What's happening now is the subject
line is populating as:

3 x Part Name A - 3 x Part Name A - 3 x Part Name A

Where it should be:

3 x Part Name A - 2 x Part Name B - 5 x Part Name C

So it seems to be moving through the records the correct number of
times but is only populating the first record that number of times. I
should also point out that Part Count subform is based on a query and
not a table. Does that matter?

Thanks

 

[MacDermott]

[CountOfPART NAME] refers to the value in the control in the selected
record; this doesn't change as you loop through the recordset.
You could try using rst![CountOfPART NAME] instead; that would refer to the
field in the current row of the recordset.