[AccessD] Current recordset and such

How about using a pass through query that sends the built sql string to
be executed? 
Jim Hale

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Arthur Fuller
Sent: Monday, July 09, 2007 9:08 AM
To: Access Developers discussion and problem solving
Subject: [AccessD] Current recordset and such

Hi all,

I am writing an Access app designed to replace a commercial app that the
client now despises. I am trying to emulate all the cool features that
she
likes. Among them are a bunch of filter buttons on the form header (such
as
Age, Status, Balance, Skill Level and so on). All this works nicely. The
datasheet form begins by showing all persons (in this case
horse-riders).
Then filters may be applied to the existing recordset. This is done with
code that writes something into Me.Filter and sets FilterOn to True.

There is also an option group whose options include Mark All, Find
Marked
and Unmark all. At the moment the first and third options use a stored
procedure, but it addresses the whole table, not the filtered set.
That's
what I need to address. Perhaps a stored procedure is not the correct
way,
since a WHERE clause is tough to pass in.

So perhaps an alternative is to use the RecordSet object, but I'm not
quite
sure how.

Let's walk through a scenario.

1. Form displays all Riders.
2. User filters the list to include only Riders who are 12 years old.
(List
drops from 1000 to about 300.)
3. User clicks "Mark All". At the moment, because I'm firing a sproc
against
the table, all 1000 get marked (there is a column called Marked) rather
than
the 300 currently selected.

I know what I need to do, I'm just shaky on how to manipulate the
current
recordset. What I need to do is something like this:

Dim rs as ADODB.RecordSet
bla bla bla
Update rs SET Marked = True
SET rs = Nothing
Me.Requery

It's the bla bla bla part that I'm unsure how to code.

Next question:

At the moment, exerting any of the filter options erases any previous
filter. There is a button that clears all filters, but ideally I would
like
the filter mechanism to be additive. As described above, all I do
currently
is supply a string to the Filter property (such as "Age = 12" or "Status
=
'Active'). I'm thinking that it would be much more powerful if I AND
these
filter strings. So that if the current filter is "Age = 12" and then I
select the Status filter and set it to "Active", the resulting filter
will
be "Age = 12 and Status = 'Active'. OR is going to be a problem,
however,
but I can live without it.

So, is this simply a case of appending the new filter spec to the old
one
with an AND in betwixt? I think so, but I thought I'd run it past you
experts before coding it.

Next question:

Does there exist somewhere a list of the commands that appear on the
Access
menu? I have more than one reason for wanting such a list, but in the
immediate context I want to create a button that does "Filter by Form".

TIA,
Arthur
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

***********************************************************************
The information transmitted is intended solely for the individual or
entity to which it is addressed and may contain confidential and/or
privileged material. Any review, retransmission, dissemination or
other use of or taking action in reliance upon this information by
persons or entities other than the intended recipient is prohibited.
If you have received this email in error please contact the sender and
delete the material from any computer. As a recipient of this email,
you are responsible for screening its contents and the contents of any
attachments for the presence of viruses. No liability is accepted for
any damages caused by any virus transmitted by this email.