[AccessD] Disconnected recordset as form source?

Hello all!

I'm doing a very rare, for me, bit of work on a database, and I'm trying
to make a continuous form give me extended multiselect functionality, so
that the users can use filter by form etc., then choose a bunch of
records to perform an action on. They need to be able to select
discontiguous records. I'm finding it a bit challenging...

The back end will eventually be on SQL Server but I'm doing a demo
version with an Access 2000 back end.

I can see that I could do it by copying the working data into a new,
local, table, adding a 'selected' column, and using this, bound to my
continuous form, to identify the records for manipulation. However to
synchronise with other user updates (there will be a couple hundred
people writing updates) that table's going to get deleted and re-created
every two minutes which is going to cause a bit of bloat (it's got about
100k rows in it).

I had thought that I could achieve the same thing with disconnected
recordsets. On paper it works elegantly; pull the PK & other key fields
+ the 'selected' field through at the start of the session, bind a
search form to the recordset and drop the connection, have the users
search for the records they want to work on, let them make their
selection of rows to go ahead and edit, and only then do I pop them into
a bound form based on their pick of records. No changes in the
disconnected rst ever need to be written back to the table.

This is the code opening the rst, binding the form and dropping the cnn:

Dim cnn As New ADODB.Connection
Dim rstExceptions As New ADODB.Recordset


Private Sub Form_Open(Cancel As Integer)

Set cnn = CurrentProject.Connection

rstExceptions.CursorLocation = adUseClient
rstExceptions.Open "SELECT * from tblUnmatched_Combined", cnn,
adOpenKeyset, adLockBatchOptimistic

Set Me.Recordset = rstExceptions

Set rstExceptions.ActiveConnection = Nothing
cnn.Close

End Sub

BUT OF COURSE it doesn't quite work; I'm getting the error message "This
record has been changed but the updated data will not be displayed
because it doesn't satisfy the criteria of the underlying recordsource."

It's random; you can update 3 rows happily and then one fails, come back
to that one a few seconds later and it succeeds. So it's *not* the data.
The 'help' says this can be a problem you get with SQL Server
recordsets, but this ain't a SQL Server recordset so...

Anyone tried something like this and willing to chew it over with me?

It's good to be back.

Roz
This e-mail and any attachments are confidential and intended
solely for the addressee and may also be privileged or exempt from
disclosure under applicable law. If you are not the addressee, or
have received this e-mail in error, please notify the sender
immediately, delete it from your system and do not copy, disclose
or otherwise act upon any part of this e-mail or its attachments.

Internet communications are not guaranteed to be secure or
virus-free.
The Barclays Group does not accept responsibility for any loss
arising from unauthorised access to, or interference with, any
Internet communications by any third party, or from the
transmission of any viruses. Replies to this e-mail may be
monitored by the Barclays Group for operational or business
reasons.

Any opinion or other information in this e-mail or its attachments
that does not relate to the business of the Barclays Group is
personal to the sender and is not given or endorsed by the Barclays
Group.

Barclays Bank PLC. Registered in England and Wales (registered no.
1026167).
Registered Office: 1 Churchill Place, London, E14 5HP, United
Kingdom.

Barclays Bank PLC is authorised and regulated by the Financial
Services Authority.