HOWTO: Navigate an ADO Recordset without Saving Changes to Current Record

ID: Q228451


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.0, 2.01, 2.1


SUMMARY

You are developing an application with Visual Basic and ADO that allows users to view and edit records in a database. The user navigates to a particular record, makes a change, and then tries to move to another record. In response, your application prompts the user: "Do you want to save the changes you made to the current record?" If the user answers "No," then you want to cancel the update and continue with the requested navigation.

ADO's default behavior is that any changes to a record are saved when moving to another record. If the update fails or is canceled, then the move operation is canceled. The below example illustrates how to implement the desired behavior.


MORE INFORMATION

  1. Create a new standard EXE project. Form1 is created by default.


  2. Add a textbox (Text1) and an ADO Data Control (ADODC1) to the default form.


  3. Set the following ADODC properties:


    • ConnectionString: DSN=MyDSN;UID=MyUID;PWD=MyPWD


    • RecordSource: SELECT MyField FROM MyTable


  4. Set the following textbox properties:


    • DataSource: ADODC1


    • DataField: MyField


  5. Run the project. Make a change to the data in the textbox. Using the buttons on the ADO Data Control, move to the next record and then back to the previous record. Notice that the changes have been saved. Now stop the project and continue with the next set of steps.


  6. Add the following code to the event ADODC1_WillChangeRecord():


  7. 
    If adReason = adRsnUpdate Then
        If MsgBox("Save changes?", vbYesNo) = vbNo Then
            adStatus = adStatusCancel
        End If
    End If 
    This code means:

    If ADO is about to save changes to the record, and if the user says "no" when prompted about saving the changes, then cancel the changes.

    The check for adRsnUpdate is of particular importance because the WillChangeRecord event executes during this scenario for other reasons, and prompting the user at those times would not yield the desired result.

  8. Run the project again, and again make a change to the data in the textbox and attempt to move to a different record. This time you will be prompted to save your changes. Click "Yes," and you will be moved to the new record. If you move back, you will see that your changes were saved. If you click "No" instead, you will receive an error stating:

    Errors occurred.
    and the record just edited will remain the current record.


  9. Add the following code to the event ADODC1_MoveComplete():


  10. 
    If adStatus = adStatusErrorsOccurred Then
        Adodc1.Recordset.CancelUpdate
        Adodc1.Recordset.MoveNext
    End If 
    The effect of this code is:

    If an error occurred during the record navigation, then undo the changes to the recordset, and move to the next record.

    The MoveComplete event's adReason parameter will indicate the cause of the move so that rather than always performing a MoveNext as the above example would, you can respond in a more specific way.

  11. This time when you run the project, edit the record, and initiate a move to a different record, if you answer "No" when prompted to save changes, the changes will not be saved, and you will move to the new record. You will, however, still see the message:


  12. Errors occurred.
  13. In order to prevent the "Errors occurred" message from being displayed, add the following code to the ADODC1_Error() event:


  14. 
    If ErrorNumber = 3617 Then
        fCancelDisplay = True
    End If 

Additional query words:

Keywords : kbADO kbADO200 kbADO201 kbADO210 kbDataBinding kbVBp500 kbVBp600 kbGrpVBDB kbGrpMDAC
Version : WINDOWS:2.0,2.01,2.1,5.0,6.0
Platform : WINDOWS
Issue type : kbhowto


Last Reviewed: June 15, 1999
© 2000 Microsoft Corporation. All rights reserved. Terms of Use.