[AccessD] Disconnected recordset as form source?

Thanks Gustav, I'm fairly well, life always has its ups and downs of
course! Perhaps on OT Friday everyone can catch me up on how they are!

I'll read through this carefully. Fast is good! - at first glance I'm
just not sure, if I do this, how to show the user which rows have been
selected? Would you have a separate form that you used to display the
subset?

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gustav Brock
Sent: 24 March 2012 23:06
To: accessd at databaseadvisors.com
Subject: Re: [AccessD] Disconnected recordset as form source?

Hi Roz

Good to see that you still are here! Hope all is well.

You could use a collection to hold the local info about the records - if
each one is marked or not.

I've used that in my row counter function. Even though much different,
it should give you the idea. The key of a collection item matches the
key of a record, and the value of the item could store if the record is
selected or not:

<code>
Public Function RowCounter( _
  ByVal strKey As String, _
  ByVal booReset As Boolean, _
  Optional ByVal strGroupKey As String) _
  As Long
  
' Builds consecutive RowIDs in select, append or create query ' with the
possibility of automatic reset.
' Optionally a grouping key can be passed to reset the row count ' for
every group key.
'
' Usage (typical select query):
'   SELECT RowCounter(CStr([ID]),False) AS RowID, *
'   FROM tblSomeTable
'   WHERE (RowCounter(CStr([ID]),False) <> RowCounter("",True));
'
' The Where statement resets the counter when the query is run ' and is
needed for browsing a select query.
'
' Usage (typical append query, manual reset):
' 1. Reset counter manually:
'   Call RowCounter(vbNullString, False)
' 2. Run query:
'   INSERT INTO tblTemp ( RowID )
'   SELECT RowCounter(CStr([ID]),False) AS RowID, *
'   FROM tblSomeTable;
'
' Usage (typical append query, automatic reset):
'   INSERT INTO tblTemp ( RowID )
'   SELECT RowCounter(CStr([ID]),False) AS RowID, *
'   FROM tblSomeTable
'   WHERE (RowCounter("",True)=0);
'
' 2002-04-13. Cactus Data ApS. CPH
' 2002-09-09. Str() sometimes fails. Replaced with CStr().
' 2005-10-21. Str(col.Count + 1) reduced to col.Count + 1.
' 2008-02-27. Optional group parameter added.

  Static col      As New Collection
  Static strGroup As String
  
  On Error GoTo Err_RowCounter
  
  If booReset = True Or strGroup <> strGroupKey Then
    Set col = Nothing
    strGroup = strGroupKey
  Else
    col.Add col.Count + 1, strKey
  End If
  
  RowCounter = col(strKey)
  
Exit_RowCounter:
  Exit Function
  
Err_RowCounter:
  Select Case Err
    Case 457
      ' Key is present.
      Resume Next
    Case Else
      ' Some other error.
      Resume Exit_RowCounter
  End Select

End Function
</code>

Also, this method runs very fast.

/gustav


>>> roz.clarke at barclays.com 24-03-12 19:18 >>>
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

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
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.