ADO introduced events in version 2.0. They can greatly help you organize your code and respond to actions on your Recordset objects. Documenting all the different ways the events could fire, however, would be an overwhelming and nearly impossible task. Instead, we'll cover the basics of each event, as described in the following table. If you're looking for a list of events and parameter values for a specific scenario—"What if a user modifies a field in the grid and then clicks on another record?"—your best bet is to put code into each of the event handlers, create the desired scenario, verify the events that fire, and check the value of the parameters in those events.
Recordset Object Events | |
---|---|
Event Name | Description |
EndOfRecordset | Fires when you navigate beyond the last record of data in your Recordset |
FetchComplete | Fires when the ADO Cursor Engine retrieves the last of the data in your Recordset that uses asynchronous fetching |
FetchProgress | Fires after the ADO Cursor Engine retrieves a batch of records for your Recordset that uses asynchronous fetching |
FieldChangeComplete | Fires after you've modified the value for a field |
MoveComplete | Fires after the current position of the Recordset changes |
RecordChangeComplete | Fires after you modify a record |
RecordsetChangeComplete | Fires after the Recordset object has changed |
WillChangeField | Fires before the contents of a field change |
WillChangeRecord | Fires before the contents of a record change |
WillChangeRecordset | Fires before the Recordset object changes |
WillMove | Fires before the current position in the Recordset changes |
This event fires when you move beyond the last record in your Recordset. There's a property you can set on most Visual Basic navigation controls that enables the control to automatically add a record to the end of your Recordset when you reach EOF. With this event, you can provide similar functionality in your application. The EndOfRecordset event supports three parameters:
You can also use the EndOfRecordset event simply to determine when you've reached the end of your Recordset.
EventStatusEnum Values | ||
---|---|---|
Constant | Value | Description |
adStatusOK | 1 | The operation that caused the event was successful. |
adStatusErrorsOccurred | 2 | The operation that caused the event failed because of an error or errors. |
adStatusCantDeny | 3 | The event cannot request cancellation of the pending operation. |
adStatusCancel | 4 | Requests cancellation of the operation that caused the event to occur. |
adStatusUnwantedEvent | 5 | Prevents subsequent notifications of the event. |
If you do not want this event to continue to fire, you can set the adStatus parameter to adStatusUnwantedEvent.
This event fires only if you're using asynchronous fetching with a client-side Recordset. After the final FetchProgress event fires, FetchComplete will fire. The FetchComplete event has three parameters:
Setting adStatus to adStatusUnwantedEvent will not prevent the event from firing in the future. We'll talk more about ADO's asynchronous fetching feature and events at the end of this chapter.
Like the FetchComplete event, the FetchProgress event fires when you use asynchronous fetching with the ADO Cursor Engine. It has four parameters:
The Progress and MaxProgress parameters are designed to help you determine what percentage of the fetching has completed thus far. To date, I've retrieved only a value of 1 from the MaxProgress parameter and 0 for Progress until the final time the event fires.
Two dynamic properties on client-side Recordsets can help determine how often this event fires: Initial Fetch Size and Background Fetch Size. They default to 50 and 15, respectively. These properties are available as soon as you set the Recordset's CursorLocation property to adUseClient. Once ADO has retrieved the number of records specified in the Initial Fetch Size property, the ExecuteComplete event on the Connection object will fire. From that point on, after ADO retrieves the number of records specified in the Background Fetch Size property, it will fire the FetchProgress event. The FetchProgress event will also be fired if ADO reaches the end of the Recordset without retrieving the number of records specified by Background Fetch Size. After FetchProgress fires for the final time, FetchComplete will fire.
Setting adStatus to adStatusUnwantedEvent will prevent the event from firing again.
Whenever you change the Value property of Field objects in your Recordset, the WillChangeField and FieldChangeComplete events will fire. These events use the following syntax:
WillChangeField cFields, Fields, adStatus, pRecordset FieldChangeComplete cFields, Fields, pError, adStatus, pRecordset |
The following is a list of the combined parameters for both WillChangeField and FieldChangeComplete:
If you want to determine which fields have been or will be modified, you can use code as follows in the event handler for either event:
Dim lngCounter As Long For lngCounter = 0 To cFields - 1 Debug.Print Fields(lngCounter).Name Next lngCounter |
If an error occurs, you can use the pError parameter in the FieldChangeComplete event handler to determine the cause.
You can set the adStatus parameter to adStatusUnwantedEvent to prevent either event from occurring in the future. You can set this parameter to adStatusCancel if you want to prevent the Field from being updated. In the FieldChangeComplete event, this parameter will be set to adStatusErrorsOccurred if an error occurs.
As far as I can tell, there is no way to use the WillChangeField event to examine the change that will be made to the Field.
You can use the WillChangeRecord and RecordChangeComplete events to keep track of changes to the records in your Recordset. These events will fire as a result of calls to the Update, Delete, CancelUpdate, AddNew, UpdateBatch, CancelBatch, or Resync methods on the Recordset. The events will also fire the first time you modify the value of a field. When the event fires, the only records visible through the filter are those affected by the action that caused these events to fire. You cannot change the Filter property in either of these events. The WillChangeRecord and RecordChangeComplete events use the following syntax:
WillChangeRecord adReason, cRecords, adStatus, pRecordset RecordChangeComplete adReason, cRecords, pError, adStatus, pRecordset |
The following is a list of the combined parameters for both these events:
The adReason parameter can help you understand why the event fired. Changing the current record in different ways will cause the adReason parameter to contain different values, as detailed in the following table.
EventReasonEnum Values Available to WillChangeRecord and RecordChangeComplete | ||
---|---|---|
Constant | Value | Description |
adRsnFirstChange | 11 | The record was modified for the first time. Subsequent modifications to the record prior to submitting those changes to the database will not cause the events to fire. |
adRsnAddNew | 1 | The record was added by using AddNew. Subsequent modifications to the record prior to submitting those changes to the database will not cause the events to fire. |
adRsnDelete | 2 | The record was deleted. |
adRsnUpdate | 3 | The pending changes in the record were submitted to the database by Update (for LockTypes of adLockOptimistic or adLockPessimistic), or by UpdateBatch (for a LockType of adLockBatchOptimistic). |
adRsnUndoUpdate | 4 | A pending modification was canceled by CancelUpdate or CancelBatch. |
adRsnUndoAddNew | 5 | A pending insertion was canceled by CancelUpdate or CancelBatch. |
adRsnUndoDelete | 6 | A pending deletion was canceled by CancelBatch. |
adRsnResynch | 8 | The record was changed by calling the Resync method. |
Setting the adStatus parameter to adStatusUnwantedEvent will prevent the event from firing in the future. If you want to cancel the operation that caused the event, you can set the adStatus parameter to adStatusCancel in the WillChangeRecord event handler so long as the parameter was not equal to adStatusCantDeny when the event fired. Setting the adStatus parameter to adStatusCancel will cause the function that fired the event to return an error.
These events can fire as a result of changing the Filter property or by calling Requery, Close, or the different navigation methods on the Recordset. They use the following syntax:
WillChangeRecordset adReason, adStatus, pRecordset RecordsetChangeComplete adReason, pError, adStatus, pRecordset |
The following is a list of the combined parameters for these events:
The adReason parameter can help you understand why the event fired. Changing the current record in different ways will cause the adReason parameter to contain different values. (See the following table.) For example, if your Recordset does not support bookmarks (server-side forward-only and dynamic cursors), these events will fire when ADO refreshes the cache. This can happen the first time you examine the data in the Recordset object; by navigating outside the currently cached records by calling the Move, MoveNext, or similar methods; or by setting the Filter property. If you call the Requery method on a Recordset that doesn't support bookmarks, these events will fire with an adReason of adRsnMove.
EventReasonEnum Values Available to WillChangeRecordset and RecordsetChangeComplete | ||
---|---|---|
Constant | Value | Description |
adRsnRequery | 7 | An operation requeried the Recordset. |
adRsnSynch | 8 | The query has been resynchronized with the database. |
adRsnClose | 9 | Calling the Open or Requery method (whether your Recordset supports bookmarks or not) will cause the RecordsetChangeComplete event (but not the WillChangeRecordset event) to fire with this value for the adReason parameter. |
adRsnMove | 10 | Requery has been called on a Recordset that doesn't support bookmarks. |
Setting the adStatus parameter to adStatusUnwantedEvent will prevent the event from firing in the future. If you want to cancel the operation that caused the event, you can set the adStatus parameter to adStatusCancel in the WillChangeRecordset event so long as the parameter was not equal to adStatusCantDeny when the event fired. Setting adStatus to adStatusCancel will cause the function that fired the event to return an error.
The WillMove and MoveComplete events fire when you navigate through your Recordset. These events use the following syntax:
WillMove adReason, adStatus, pRecordset MoveComplete adReason, pError, adStatus, pRecordset |
They support the following parameters:
The adReason parameter can help you understand why the event fired. Changing the current record in different ways will cause the adReason parameter to contain different values, as explained in the following table.
EventReasonEnum Values Available to WillMove and MoveComplete | ||
---|---|---|
Constant | Value | Description |
adRsnRequery | 7 | The Requery method was called. Currently, calling Requery will cause the WillMove and MoveComplete events to fire twice, first with an adReason of adRsnRequery and then with an adReason of adRsnMove. |
adRsnMove | 10 | Calling the Move or Requery method, or moving to another record by setting the AbsolutePage, AbsolutePosition, Bookmark, Filter, or Index property, will cause these events to fire with this value in the adReason parameter. |
adRsnMoveFirst | 12 | The MoveFirst method was called. |
adRsnMoveNext | 13 | The MoveNext method was called. |
adRsnMovePrevious | 14 | The MovePrevious method was called. |
adRsnMoveLast | 15 | The MoveLast method was called. |
Setting the adStatus parameter to adStatusUnwantedEvent will prevent the event from firing in the future. If you want to cancel the operation that caused the event, you can set the adStatus parameter to adStatusCancel in the WillMove event as long as the parameter is not equal to adStatusCantDeny when the event is fired. Setting adStatus to adStatusCancel will cause the function that fired the event to return an error.