[AccessD] Update the current recordset

First of all, using a field called "Marked" may cause multi-user issues.  It
definitely will in a bound form, because the records are live linked back to
the table.  If another user clears the marked flag for a record the current
user is looking at... 


John W. Colby
Colby Consulting
www.ColbyConsulting.com 
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Arthur Fuller
Sent: Tuesday, July 10, 2007 5:35 AM
To: Access Developers discussion and problem solving
Subject: [AccessD] Update the current recordset

I have  continuous form that is a subform. On the parent form's header are
some buttons and combo boxes that are used to filter the continuous subform.
This all works fine. There's an option group that contains "Mark All", "Find
Marked" and "Unmark All" options. When I first wrote it, "Mark All" applied
to all the records in the table but I want it to be smarter than that. To
wit, when you filter, say, by age, and select 20 as the age, then only a
small subset of the total rows are shown. I want "Mark all" to apply to the
subset not the whole table.

The Marking is done using a column in the table called Marked. The first
version invokes a stored procedure to mark and unmark all the records and it
is amazingly quick.

I thought that if I used the recordsetclone it would present only the
filtered subset, but apparently that doesn't work. Here's the code I wrote:

<code>
Sub MarkAllRecords()
    Dim rst As ADODB.Recordset
    With Me
        Set rst = .Riders_Browser_fsub.Form.RecordsetClone

        Do While Not rst.EOF
            rst.Fields("Marked") = -1
            rst.Update
            Debug.Print rst("RiderID"), rst("Marked")
            rst.MoveNext
        Loop
        Debug.Print rst.RecordCount & " rows changed."
        rst.Close
        Set rst = Nothing
    End With
End Sub
</code>

This marks all the records in the table, not the filtered subset. How do I
get a handle on just the subset? I suppose that I could use a SQL statement
that specifies the current filter, which I shall try now, but I'm wondering
why the code above doesn't work -- or rather, how to make it work.

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