Adding multiple subform fields 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. I can populated the first
record ok, but I can't seem to move to the next record. I think I need
to clone the record set and put in a loop somewhere, but what I've got
doesn't generate any information.

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] & " - "

Forms![RMA Entry]![Part Count subform].SetFocus
Dim rst As DAO.Recordset
Set rst = Forms![RMA Entry]![Part Count
subform].Form.RecordsetClone
With rst
Do Until .EOF
myItem.Subject = myItem.Subject & [CountOfPART NAME] & " x " &
[PART NAME] & " "
DoCmd.GoToRecord , , acNext
Loop
End With

 

[Allen Browne]

Use the MoveNext method of the Recordset to get to the next record.

To ensure the recordset pointer is at the top, you need to MoveFirst before
the loop starts. The MoveFirst will fail if there are no records, so try
something like this:

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)

 

[aspoede]

Thank you so much, I'm getting a lot further than I have before.
However, the one problem with that code you suggested is that the
..RecordCount will always be greater than 0, therefore it will always go
to the first record. 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.

Any ideas?

 

[aspoede]

I should have included an example of the result. Let's say there are 3
records in the Part Count Subform: 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

 

[Allen Browne]

Sorry: it's reading the value from the form instead of the recordset. Needs
the bang to indicate the field of the object specified in the With block.

Try:

strSubject = strSubject & ![CountOfPART NAME] & " x " & ![PART NAME] & " "