How do I string all records in a field together

[Guest]

I would like to set up a database with PTA information--student, teacher,
current grade, parent name, phone number, email, etc. I would then like to
be able to pull all querried for email addresses (based on grade, teacher,
etc.) and concactenate them all with comma delimiters. I will then paste the
result into my email program. Unfortunately, we can't use Outlook as our
email program or this would be a mute point. We use a webased email program
that won't accept copying the column of email addresses and pasting it in the
BCC or TO field. It only takes the first one. If I can combine them all
into one string with commas separating each one, that would work perfectly.

 

[Dirk Goldgar]

I would like to set up a database with PTA information--student,
teacher, current grade, parent name, phone number, email, etc. I
would then like to be able to pull all querried for email addresses
(based on grade, teacher, etc.) and concactenate them all with comma
delimiters. I will then paste the result into my email program.
Unfortunately, we can't use Outlook as our email program or this
would be a mute point. We use a webased email program that won't
accept copying the column of email addresses and pasting it in the
BCC or TO field. It only takes the first one. If I can combine them
all into one string with commas separating each one, that would work
perfectly.

 

[Dirk Goldgar]

I would like to set up a database with PTA information--student,
teacher, current grade, parent name, phone number, email, etc. I
would then like to be able to pull all querried for email addresses
(based on grade, teacher, etc.) and concactenate them all with comma
delimiters. I will then paste the result into my email program.
Unfortunately, we can't use Outlook as our email program or this
would be a mute point. We use a webased email program that won't
accept copying the column of email addresses and pasting it in the
BCC or TO field. It only takes the first one. If I can combine them
all into one string with commas separating each one, that would work
perfectly.

Oops, hit Send way too soon.

You'll need to loop through a recordset that you've opened on the query.
Something like this:

'----- start of example code -----
Function ConcatenateList(pSQL As String) As String

' pSQL is a SQL SELECT statement that returns the
' set of records we want. Only the first field in each
' record will be used.

' ERROR-HANDLING LEFT TO THE USER

Dim strList As String

Set rs = CurrentDb.OpenRecordset(pSQL, dbOpenSnapshot)
With rs
Do Until .EOF
strList = strList & ", " & .Fields(0).Value
.MoveNext
Loop
.Close
End With
Set rs = Nothing

If Len(strList) > 0 Then
strList = Mid(strList, 3)
End If

ConcatenateList = strList

End Function
'----- end of example code -----

So you could fill a text box with the result of this function:

Me!txtAddresses = _
ConcatenateList( _
"SELECT Email FROM MyTable " & _
"WHERE PersonType='Student' AND Grade = 9")

 

[Guest]

I'm having a lapse in common sense. I know what you are talking about but
where do I put this code?

I am assuming the textbox code would go on a report that would open up. Can
I put all of this on the report?

Again, I am just not thinking straight.

 

[Pieter Wijnen]

Either that, or in a general module

Pieter

I'm having a lapse in common sense. I know what you are talking about but
where do I put this code?

I am assuming the textbox code would go on a report that would open up.
Can
I put all of this on the report?

Again, I am just not thinking straight.