How to programmatically implement a RecordExit event in Access 2002 (304139)

SUMMARY

This article shows you how to programmatically implement a RecordExit event in a form in Access 2002.

Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements.

MORE INFORMATION

The RecordExit event is not available in Access 2002; however, by using the ADO event model, you can simulate this event. To do so, follow these steps:

Insert a class module into the Northwind sample database. Save the class module as CRecordExit.
Add a reference to the Microsoft ActiveX Data Objects 2.5 library.

Insert the following code:

Option Explicit

Private frm As Access.Form
Private WithEvents rs As ADODB.Recordset

Public Event fRecordExit(Cancel As Integer)

Public Property Get Form() As Access.Form
    Set Form = frm
End Property

Public Property Set Form(frmIn As Access.Form)
    Set frm = frmIn
    If Not frm.Recordset Is Nothing Then
        Set rs = frm.Recordset
    End If
End Property

Private Sub rs_WillMove(ByVal adReason As ADODB.EventReasonEnum, _
  adStatus As ADODB.EventStatusEnum, ByVal pRecordset As ADODB.Recordset)
    Dim intCancel As Integer
    If adReason = adRsnMove Then
        RaiseEvent fRecordExit(intCancel)
        If intCancel Then adStatus = adStatusCancel
    End If
End Sub

Open the Customers form in Design view, click Code on the View menu, and then insert the following code into the form module:

Option Explicit

Private WithEvents fRecExit As CRecordExit

Private Sub Form_Load()
    Dim rs As ADODB.Recordset
    Set rs = New ADODB.Recordset

    With rs
        .ActiveConnection = CurrentProject.Connection
        .Source = "SELECT * FROM Customers"
        .CursorType = adOpenStatic
        .LockType = adLockOptimistic
        .CursorLocation = adUseClient
        .Open
    End With

    Set Me.Recordset = rs
    Set fRecExit = New CRecordExit
    Set fRecExit.Form = Me
End Sub

Private Sub fRecExit_fRecordExit(Cancel As Integer)
    Cancel = MsgBox("Do you wish to leave this record?", vbYesNo) = vbNo
End Sub

To test the code, open the Customers form in Form view and move through the records.

The way the code is designed, the fRecordExit event will fire as you leave the current record but before the Current event fires for the next record.

In a situation where a record is dirtied, the sequence of events is as follows:

Form BeforeUpdate-->Form AfterUpdate-->fRecordExit-->Form Current

NOTE: The form temporarily moves to the next record and then moves back. If the user clicks No in the message box, the code sets the Cancel argument of the fRecordExit event to True, which returns the user to the original record. This is a limitation in the ADO event model.