[AccessD] Current recordset and such

Hi Arthur

This is where DAO would come in as your speedy and extremely simple tool for your bound updatable form.
Skip the stored procedure as well as reloading/requering the form by running code like this:

  Dim rst As DAO.Recordset
  
  Set rst = Forms!frmYourForm.RecordsetClone
  Debug.Print rst.RecordCount
  ' Apply your filter.
  rst.Filter = "Include = True"
  Set rst = rst.OpenRecordset
  rst.MoveLast
  Debug.Print rst.RecordCount
  
  With rst
    .MoveFirst
    While Not .EOF
      .Edit
        !YourField.Value = <somevalue>
      .Update
      .MoveNext
    Wend
    .Close
  End With
  
  Set rst = Nothing

/gustav

>>> fuller.artful at gmail.com 09-07-2007 16:08 >>>
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
--