concatenating a field from multiple subform records

[Paul Ponzelli]

I'm trying to build a concatenated string of email addresses from a selected
group of records in a subform. My purpose is to send an email to a group of
contacts whose check boxes have been selected by a user.

I'm currently using the SendObject method of the DoCmd object to send the
email to a single recipient, but I need to loop through the subform records
that are checked to build the concatenated string containing the addresses
of all contacts in the selected group.

Assuming that my objects are named as follows:

Main form: frmProjects
Subform control: ctlContactsSub
Subform: frmContactsSub
Email field in subform: txtEmail
Checkbox field in subform: chkInclude

Can someone Please tell me how to loop through those subform records so I
can concatenate the email fields in the checked records?

Thanks in advance,

Paul

 

[pietlinden]

I'm trying to build a concatenated string of email addresses from a selected
group of records in a subform. My purpose is to send an email to a group of
contacts whose check boxes have been selected by a user.

I'm currently using the SendObject method of the DoCmd object to send the
email to a single recipient, but I need to loop through the subform records
that are checked to build the concatenated string containing the addresses
of all contacts in the selected group.

Assuming that my objects are named as follows:

Main form: frmProjects
Subform control: ctlContactsSub
Subform: frmContactsSub
Email field in subform: txtEmail
Checkbox field in subform: chkInclude

Can someone Please tell me how to loop through those subform records so I
can concatenate the email fields in the checked records?

Thanks in advance,

Paul

create a recordsetclone of the subform's recordsource and then loop
through it... oh, and filter for the records that are checked.

I'd probably just create a function that opened a query that returned
the values I wanted, and appended them to a string variable.

'---Air code... not tested or anything responsible like that...

Function MyFunction() as string
dim rs as dao.recordset
dim strTemp as string
set rs=DbEngine(0)(0).OpenQueryDef("qryGetRecords")
do until rs.EOF
strTemp = rs.Fields("EMailAddress") & ","
loop

'---trim off extra trailing comma
strTemp=left$(strTemp,len$(strTemp)-1)
MyFunction = strTemp

rs.close
set rs=nothing
End Function

 

[Paul Ponzelli]

Thank you so much for explaining how to accomplish this. I followed your
suggestions, and with a few tweaks, I came up with this which works fine:

Function fnEmailList() As String
Dim rs As dao.Recordset
Dim strTemp As String

Me.Refresh

Set rs = Me.RecordsetClone
strTemp = ""
rs.MoveFirst
Do Until rs.EOF
If Len(rs.Fields("Email")) > 0 And rs.Fields("SendEmail") = -1 Then
strTemp = strTemp & rs.Fields("EMail") & ";"
End If
rs.MoveNext
Loop
fnEmailList = strTemp
rs.Close
Set rs = Nothing
End Function

I found after some testing that unless I used the Me.Refresh method, the
RecordsetClone didn't recognize the most recent changes to the SendEmail
checkboxes. But with that command inserted, it works great.

Thanks for explaining the approach to take.

Paul