Concentating contents from multiple records into one stored string

[Fred]

I'm very experienced at Access/DB's but figure me as a absolute absolute
complete novice on using code, to the point that I don't even know if this is
a code (vs. query etc.) question.

Let's say that I have a "People" table, with 20 records in it and one of the
fields in it is "EmailAddress". Every record always has a valid
EmailAddress.

I want to concentate all 20 of the emails into (separated by commas) a text
string and store it somewhere (preferably a memo field, but a text document
would also be OK) which can then do a dummy level copy (onto the clipboard)
of the string and paste into the "to" line when writing an email (in a
software [e.g. Thunderbird] that wants comma delimiters.)

Could you tell me how to do this, keeping my first sentence in mind?

Thank you very much in advance for any help that you can give me!!

Sincerely,

Fred



I suppose that my post will raise thoughts on "is there a better way to get
to that end result" but that's for later lest it interfere with this dummy
trying to learn the above.

 

[Paolo]

Hi Fred,
if I've understood well your question create a button and add this code to
the on click event

Dim rec_mail As DAO.Recordset
Dim str_mail As String * 32000

Set rec_mail = CurrentDb.OpenRecordset("select * from people",
dbopendynaset)
tmp_mail = ""
Do While Not rec_mail.EOF
tmp_mail = tmp_mail & rec_mail!emailaddress & ","
rec_mail.MoveNext
Loop
str_mail = Left(Left(tmp_mail, Len(tmp_mail) - 1) & Space(32000), 32000)
file_num = FreeFile
Open "c:\email.txt" For Random As #file_num Len = 32000
Put #file_num, , str_mail
Close #file_num
rec_mail.close

HTH Paolo

 

[Fred]

Dear Paolo

Thank you so much.

I did that and tried to run it (Access 2003) and it errored out saying that
there was a syntax error in:

Set rec_mail = CurrentDb.OpenRecordset("select * from people", dbopendynaset)


Do you know what's wrong / how to fix?

Again, thank you very much.

Sincerely,

Fred

 

[Paolo]

Hi Fred,
Sorry for the late answer but I was out for the weekend.
As Dirk Goldgar said, the line
Set rec_mail = CurrentDb.OpenRecordset("select * from people", dbopendynaset)
must be all on the same line! Otherwise use the line continuation character.
On my post the line was split in two lines by the system 'cause was too
long. It was unintentional and I didn't notice it until now.
Regards Paolo

 

[Fred]

Hello Paolo,

Thank you very much. That worked great!


Fred