Query on current recordset and filter

[Thirith]

I could imagine that the following is actually quite simple, but I haven't found anything useful on it so far.

I'd like to add a button to the form we're using for data entry and editing records; this button should give the user a list of the e-mail addresses in all the records of the current record source and filter. (To give a bit more detail: I've got a filter-by-event box that sets the record source to those participants only who took part in the specified event, so technically it's not so much a filter as a case of RecordSource = xxx.)

It's easy enough for me to do this - I just change from Form View to Datasheet View and copy the e-mail column. However, this would already throw a fair number of the users who will be working with the database, so I want to make it as easy for them as possible. (In fact, if I could create a function that opens a new e-mail in Outlook with all the relevant e-mail addresses in the To: field, that'd be even better.)

Any tips on how I can do this in Access 2003? I'd think that there's some sort of expression along the lines of CurrentRecordSource...

 

[strive4peace]

there are a couple different ways to do this. Here is one:

You could put a textbox on your form to return the list to. Then, the user could click in there, press F2 to select everything, and then paste to an email header...

but since you want to generate an email message anyway, then you could collect the addresses into a variable and use code to construct an email message with the list. Are you using Outlook? If not, what is your email application?

you can use code like this to make the list:

   if me.recordset.recordcount = 0 then
     msgbox "you don't have any records displayed",,"Can't make email list"
     exit sub
   end if

   dim mEmailList as variant
   mEmailList  = Null

   with me.recordsetclone
      .movefirst
      do while not .eof
         if len(nz(!email_address_fieldname,"")) > 0 then
            mEmailList = (mEmailList + ",") & !email_address_fieldname
         end if
         .movenext
      loop
   end with

   if IsNull(mEmailList) then
      msgbox "No email addresses found",, "Email Addresses"
   else
      msgbox mEmailList,, "Email Addresses"
   end if

Once you specify your email application, we can give you code to construct the message

 

[Thirith]

Thanks, that's a great start! We're using Outlook; I'll try to write some code (using yours) that'll create a new mail using all the addresses.