Query on current recordset and filter

Thirith

Board Regular
Joined
Jun 9, 2009
Messages
115
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...
 

Some videos you may like

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

strive4peace

Well-known Member
Joined
Jul 6, 2009
Messages
678
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:

Code:
   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

Board Regular
Joined
Jun 9, 2009
Messages
115
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,988
Messages
5,627,996
Members
416,286
Latest member
ko15

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top