Re: Adding multiple subform fields to a mail item in Access

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<aspoede@xxxxxxxxx> wrote in message
news:1132707657.419301.150800@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
>
> 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


.

Relevant Pages

  • Adding multiple subform fields to a mail item in Access
    ... populated with fields from a subform. ... number of records in that subform changes and I need it to populate the ... to clone the record set and put in a loop somewhere, ... Dim rst As DAO.Recordset ...
    (microsoft.public.access.forms)
  • Re: Recordsets
    ... If I just use MoveFirst and then loop through I ... only have one record, but if I MoveLast, check the record ... MoveLast to populate the set then ...
    (microsoft.public.access.gettingstarted)
  • RE: EOF getting "You cant go to the specified record"
    ... Movefirst ... This disables the buttons when first or last record is reached thus no more ... > Dim rst As ADODB.recordset ... > Exit Sub ...
    (microsoft.public.access.formscoding)
  • Re: Loop through recordset of Crosstab query
    ... Dim con As New ADODB.Connection ... Dim rst As New ADODB.Recordset ... rownum = rownum + 1 ... I suspect it is because you have a Forward Only cursor, and you are using a MoveFirst, which would be a move back. ...
    (comp.databases.ms-access)
  • Looping through Table records.
    ... You need to either use ADO or DAO to loop through the ... Dim rst As New ADODB.Recordset ... >decided I want to do this in Visual Basic code. ...
    (microsoft.public.access.formscoding)