[AccessD] Disconnected recordset as form source?

Not sure what the overall objective is or required functionality from the
continuous form but a list box would make the selection part trivial.

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of
roz.clarke at barclays.com
Sent: Saturday, March 24, 2012 11:18 AM
To: accessd at databaseadvisors.com
Subject: [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.


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