[AccessD] Disconnected recordset as form source?

Hi Roz

You can do it this way; no tampering with the table, no special queries:

Add  to the detail section of a table style form an unbound checkbox, chkSelect, and a (invisible) button, btnSelect.
Bind the checkbox to: =KeyGet(CStr([Id])) 
where Id is your unique key of the table.

Add this simple code to the form:

<code>
Option Compare Database
Option Explicit

  Public colSelect As Collection

Private Sub Form_Open(Cancel As Integer)

  Set colSelect = New Collection
  
End Sub

Private Sub KeySet( _
  ByVal strKey As String, _
  ByVal booSelect As Boolean)

  On Error Resume Next
  
  colSelect.Remove strKey
  colSelect.Add booSelect, strKey
  
End Sub

Private Function KeyGet( _
  ByVal strKey As String) _
  As Boolean

  Dim booKey  As Boolean
  
  On Error Resume Next
  booKey = colSelect(strKey)
  
  KeyGet = booKey
  
End Function

Private Sub btnSelect_Click()

  Dim strKey As String
  Dim booKey As Boolean
  
  strKey = CStr(Nz([Id]))
  booKey = KeyGet(strKey)
  
  Call KeySet(strKey, Not booKey)
  
  Me!chkSelect.Requery

End Sub
</code>

Now, clicking or keying the button will toggle the checkbox.
Pull RecordsetClone and the value from KeyGet to select the marked records.

/gustav


>>> roz.clarke at barclays.com 25-03-2012 12:52 >>>
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