[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