Results 1 to 10 of 10
  1. #1
    Remster is offline Competent Performer
    Windows 10 Access 2010 64bit
    Join Date
    Sep 2010
    Posts
    317

    When to clone a recordset

    I have a form with a continuous subform in its header. The subform is bound to the same table as the main form and is used to navigate to records in the main form by means of the following code in its (the subform's) On Current event:

    Dim rs As Object
    Set rs = Me.Parent.Recordset.Clone
    rs.FindFirst "[Field1] = '" & Me!Field1.Value & "'"
    If Not rs.EOF Then Me.Parent.Bookmark = rs.Bookmark

    The attached database illustrates what I mean.



    Now, this is just some code that I picked up from somewhere and I don't really understand what happens when a recordset is cloned. So my question is this: Would it be more efficient to declare 'rs' as a public variable in separate module, and to set it to Me.Parent.Recordset.Clone only in the main form's On Load and After Insert events? My concern is that cloning a large recordset once the database is full of data will be a slow business, so that it would be best to do so as infrequently as possible.
    Attached Files Attached Files

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    i have never needed to clone.

  3. #3
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    I have used RecordsetClone code you posted. However, I don't understand why you reference the field name as parameter. Should be an UNBOUND control for user input of search criteria.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  4. #4
    Remster is offline Competent Performer
    Windows 10 Access 2010 64bit
    Join Date
    Sep 2010
    Posts
    317
    Quote Originally Posted by June7 View Post
    I don't understand why you reference the field name as parameter. Should be an UNBOUND control for user input of search criteria.
    The subform works as it's supposed to. A list box can be used in the same way but is less pretty. Have you looked at the sample database I attached? My only concern is whether it makes sense to clone the recordset repeatedly.

  5. #5
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    The rs variable is not even needed.

    With Me.RecordsetClone
    .FindFirst "Field1='" Me.Field1 & "'"
    If Not .NoMatch Then Me.Parent.Bookmark = .Bookmark
    End With

    You appear to be emulating a split form.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    Remster is offline Competent Performer
    Windows 10 Access 2010 64bit
    Join Date
    Sep 2010
    Posts
    317
    Quote Originally Posted by June7 View Post
    You appear to be emulating a split form.
    No, the subform really is just for navigation. Additions aren't allowed. As I've said, I could just as well have used a list box.

    I'm entirely happy with the principle. My question is still about whether it will become time-consuming to clone the recordset every time the user moves record, as opposed to every time a new record is added.

    Good point about the redundant variable, as things stand.

  7. #7
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    The form dataset and RecordsetClone dataset have the same bookmarks. If a recordset object variable is opened to a RecordsetClone dataset then changes are made to form dataset outside the recordset variable, the recordset is no longer a copy of the form's dataset and bookmaks are no longer synchronized. I am not sure if the bookmarks are regenerated when navigating form.

    So, as long as no edits are done to data, might be able to declare variable global then keep RecordsetClone variable open for repeated reference. I've never tried. You can.


    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    moke123's Avatar
    moke123 is online now Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    My only concern is whether it makes sense to clone the recordset repeatedly.
    You could also use a filter

    Code:
    Private Sub Fruit_Click()
    
    Dim strFilter As String
    
    strFilter = "[Fruit] = '" & Me!Fruit & "'"
    
    Parent.Filter = strFilter
    Parent.FilterOn = True
    
    End Sub

  9. #9
    Remster is offline Competent Performer
    Windows 10 Access 2010 64bit
    Join Date
    Sep 2010
    Posts
    317
    Quote Originally Posted by June7 View Post
    If a recordset object variable is opened to a RecordsetClone dataset then changes are made to form dataset outside the recordset variable, the recordset is no longer a copy of the form's dataset and bookmaks are no longer synchronized.
    That's why I've talked about placing "Set rs = Me.Parent.Recordset.Clone" in the main form's After Insert event as well as its On Load event.

    Quote Originally Posted by June7 View Post
    I've never tried. You can.
    It definitely works. The question is whether it's worth it, specifically whether opening a recordset object variable to a RecordsetClone (if that's the correct expression) is a slow process when the recordset is large.

  10. #10
    Remster is offline Competent Performer
    Windows 10 Access 2010 64bit
    Join Date
    Sep 2010
    Posts
    317
    Quote Originally Posted by moke123 View Post
    You could also use a filter
    Thanks, I'll look into this.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Updating a RecordSet Clone
    By GraeagleBill in forum Programming
    Replies: 2
    Last Post: 01-13-2018, 03:43 PM
  2. recordset clone and search
    By Sweetu in forum Forms
    Replies: 5
    Last Post: 12-09-2016, 04:33 AM
  3. recordset clone method
    By vientito in forum Programming
    Replies: 1
    Last Post: 10-30-2014, 11:33 PM
  4. RecordSet Clone Problem with code
    By rlsublime in forum Programming
    Replies: 8
    Last Post: 06-21-2012, 11:56 AM
  5. multiple combo boxes recordset clone
    By trigirl67 in forum Forms
    Replies: 1
    Last Post: 01-30-2012, 02:32 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums