PRB: ADO Recordset Clone Events Positioned to Incorrect Record

ID: Q248678


The information in this article applies to:
  • Microsoft Visual Basic Professional and Enterprise Editions for Windows, versions 5.0, 6.0
  • ActiveX Data Objects (ADO), versions 2.1, 2.1 SP1, 2.1 SP2


SYMPTOMS

Manipulating an ADO Recordset raises events in its clones. However, the field value is not correct in the clone Recordset events.


CAUSE

Microsoft is currently researching this issue.


RESOLUTION

To resolve this issue take one of the following steps to work around the problem:

  • Base the events on the Recordset variable actually being manipulated.


  • Position the clone to the current recordset position.


  • 
    rsClone.BookMark = rs.BookMark 
  • If you do not need independent scrolling, you can set the two Recordsets equal to each other instead of one being a clone. For example, use:
    
    Set rs2 = rs1 
    instead of:
    
    Set rs2 = rs1.Clone 



STATUS

This behavior is by design.


MORE INFORMATION

A Clone Recordset is a pointer to the same data as another Recordset, except that a Clone Recordset has an independent Bookmark. Therefore, the current position in the main Recordset and in the Clone can be different. A MoveNext in one does not affect the position of the other.

When you edit a record in one Recordset, the other may or may not be positioned on it. Event procedures, such as WillChangeField or FieldChangeComplete merely pass a pointer to the Recordset and the Fields collection. The events do not reposition the record.

When data is changed in one Recordset, the events in the other provide a Recordset which does not point to the record being affected.

Steps to Reproduce Behavior

  1. In Visual Basic 5.0 or 6.0, open a new Standard EXE project.


  2. From the Project menu select the References menu to add a Reference to
    Microsoft ActiveX Data Objects 2.1


  3. Add a Command button (Command1) and the following code to the default form:


  4. 
    Option Explicit
    
    Dim WithEvents rs As ADODB.Recordset
    Dim WithEvents rsClone As ADODB.Recordset
    
    Private Sub Form_Activate()
      Set rs = New ADODB.Recordset
      rs.Fields.Append "Col", adInteger
      rs.Open
      rs.AddNew "Col", 1
      rs.AddNew "Col", 2
      rs.AddNew "Col", 3
      rs.AddNew "Col", 4
      rs.AddNew "Col", 5
      Set rsClone = rs.Clone
      rs.MoveLast
      Debug.Print "Initial rs Record:", rs!Col
      Debug.Print "Initial rsClone Record:", rsClone!Col
    End Sub
    
    Private Sub Command1_Click()
      ' rsClone.Bookmark = rs.Bookmark
      rs!Col = 1000
      rs.Update
      Debug.Print "rs Record after edit:", rs!Col
      Debug.Print "rsClone Record after edit:", rsClone!Col
      rsClone.Bookmark = rs.Bookmark
      Debug.Print "Clone sync'd with rs:", rsClone!Col
    End Sub
    
    Private Sub rs_WillChangeField(ByVal cFields As Long, ByVal Fields As Variant, adStatus As ADODB.EventStatusEnum, _
                                   ByVal pRecordset As ADODB.Recordset)
      Debug.Print "rs WillChangeField:", pRecordset!Col
    End Sub
    
    Private Sub rsClone_WillChangeField(ByVal cFields As Long, ByVal Fields As Variant, adStatus As ADODB.EventStatusEnum, _
                                        ByVal pRecordset As ADODB.Recordset)
      Debug.Print "rsClone WillChangeField:", pRecordset!Col
    End Sub
     
  5. Run the application and click the command button. The output should appear as follows:
    
    Initial rs Record:           5 
    Initial rsClone Record:      1 
    rs WillChangeField:          5 
    rsClone WillChangeField:     1 
    rs Record after edit:        1000 
    rsClone Record after edit:   1 
    Clone sync'd with rs:        1000  
    NOTE: The clone record position does not change. If it explicitly set to the record that was edited, it does show the changes.


  6. Uncomment the first line of the Command1_Click procedure and run the application again. This time, the clone is positioned on the same record as the main Recordset prior to the edit. The results are as follows:
    >
    Initial rs Record:           5 
    Initial rsClone Record:      1   <-- clone is repositioned after this point
    rs WillChangeField:          5 
    rsClone WillChangeField:     5 
    rs Record after edit:        1000 
    rsClone Record after edit:   1000 
    Clone sync'd with rs:        1000  


NOTE: The Fields argument of the cloned Recordset's event procedures also give the value of the fields in the cloned recordset's current position. Replacing the event Debug.Print statements with

  Debug.Print "rsClone WillChangeField:", Fields(0).Value 
produces identical results as the existing code.

© Microsoft Corporation 1999, All Rights Reserved.
Contributions by Malcolm Stewart and Terrell Andrews, Microsoft Corporation

Additional query words: kbdsupport kbgrpvbdb kbgrpmdac

Keywords : kbADO kbVBp500 kbVBp600 kbGrpVBDB kbGrpMDAC kbDSupport kbADO210sp2
Version : WINDOWS:2.1,2.1 SP1,2.1 SP2,5.0,6.0
Platform : WINDOWS
Issue type : kbprb


Last Reviewed: January 10, 2000
© 2000 Microsoft Corporation. All rights reserved. Terms of Use.