How To Navigate an ADO Recordset without Saving Changes to Current Record (228451)

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.

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

MORE INFORMATION

Create a new standard EXE project. Form1 is created by default.
Add a textbox (Text1) and an ADO Data Control (ADODC1) to the default form.
Set the following ADODC properties:
- ConnectionString: DSN=MyDSN;UID=MyUID;PWD=MyPWD
- RecordSource: SELECT MyField FROM MyTable
Set the following textbox properties:
- DataSource: ADODC1
- DataField: MyField
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.
Add the following code to the event ADODC1_WillChangeRecord():
```
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.
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 receive the following error message:
Errors occurred.
In ADO 2.7 , the error message appears as follows:
Multiple-step operation generated errors. Check each status value.
and the record just edited will remain the current record.
Add the following code to the event ADODC1_MoveComplete():
```
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.
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 error message:
Errors occurred.
In order to prevent the "Errors occurred" message from being displayed, add the following code to the ADODC1_Error() event:
```
If ErrorNumber = 3617 Then
    fCancelDisplay = True
End If
					
```