Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2010
    Posts
    88

    Unanswered: Working with the records that I can see in a form

    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

  2. #2
    Join Date
    Mar 2010
    Posts
    88
    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......

  3. #3
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    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.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  4. #4
    Join Date
    Mar 2010
    Posts
    88
    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

  5. #5
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    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.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •