Hi folks ... have a new problem that I'm having difficulty in describing to myself, hence hard to search for solutions! Let's see if this makes sense:

Short version: I want to take an action on a set of data that has been filtered in a form.

Longer version:

I have a big table of people - each record (person) has many fields in it.

I have a query based on that table, with just a few of the fields, and which is a subset of all of the people. I have a form which looks at the data in this query, as a continuous form. No problem so far - I can see all the data I want, and make changes to it as required.

I can use the built-in filtering tool to make a further filter on the records I'm looking at. Also fine.

Now, however, I want to perform an action based on the set of records that I am now looking at, but don't know how to get at the right set of records.

For example, I might want to run a report (which uses records from my main persons table) - but only for the list of people that I am now looking at in my filtered form.

Or, I might want to send an email to all of the people that I am now looking at (I have VBA code to take care of sending an email to a particular person).

Is there, for example, a recordset that I can get at which corresponds to the list of records that I am looking at in my form? Or is there a better way of achieving what I want?

All suggestions gratefully received!

TIA

Well, there's nothing like writing out your problem to help you think about it, is there!

As a result of writing the above, I realised that the answer to my second problem (sending an email to all visible people) is just to do a quick

rst = me.recordsetclone

and then loop through the recordset, sending emails as I go.

However, I still don't see how that helps me with my first problem, of running a report just for the records I'm looking at. I should make it clear that the report uses many fields from the underlying table which don't appear in the query/form/recordset I've now got.

More clarity of thinking required here......

Rather than use the built-in filtering tools, build your own into the form header. You can have a series of combo boxes or text boxes in which you select or enter values, and a command button to filter the form based on those values. Once you have the records that you need on display, you could then have another command button to build a SQL statement based on your selections. That statement could then be used as the source for a report, or a mail merge, or whatever.

Hi weejas

Thanks for the response. Hmm, that sounds like a lot of work, especially when you remember all the nice options that are available in the built-in filtering tools. Just think of dates - next week, last month, etc etc.

I'm trying to think of a way that I can effectively join an existing query (the one that drives the report) with a recordset (recordsetclone from the form, after filtering applied). It feels like there ought to be a way!!

Ceejaydbf

Well, my experience with Access is currently limited to 2000 (don't ask!), so the later versions might be more flexible. However, in 2k you can't base a report on a recordset - you need a SELECT statement. So if you build your own filtering controls, you can then use their contents to build a SQL statement.