How to Respond to Auto-commit Feature of VB Data Control

MORE INFORMATION

The Action argument passes to the Sub procedure the action taking place on the Data Control. If you use a Select Case statement on the Action parameter, you can choose the events to which you want to respond.

The Validate event occurs before the next record becomes active. The action that causes the Validate to be fired can be cancelled by setting the Action parameter to vbDataActionCancel. The saving of the data can be cancelled by setting the Save parameter in the Validate event to False. And you can choose not to save bound control values individually by changing the DataChanged property (which all bindable controls have) to False for the individual controls.

If you want to roll back all the changes of an editing session, you can place BeginTrans and Commitrans (or Rollback) statements around your Data Control code. However, the following example code gives you better control.

Step-by-Step Example

1. Start a new project in Visual Basic. Form1 is created by default.
2. Add a Data Control (Data1) and two Text Boxes (Text1 and Text2) to Form1.
3. Set the following properties of the controls:

      Control     Property          Value
      ------------------------------------------
      Data1       DatabaseName      Biblio.mdb
      Data1       RecordSource      PUBLISHERS
      Text1       DataSource        Data1
      Text1       DataField         Name
      Text2       DataSource        Data1
      Text2       DataField         City
   						

4. Add the following code to the Data1_Validate event:

      Sub Data1_Validate (Action As Integer, Save As Integer)
         Dim RC As Integer
         Debug.Print "Action "; Action; " generated the Validate event."
         If Save Then
            Select Case Action
               Case vbDataActionFind, _
                  vbDataActionDelete, _
                  vbDataActionAddNew, _
                  vbDataActionCancel, _
                  vbDataActionClose
                  'No action
               Case vbDataActionMoveNext, _
                  vbDataActionMovePrevious, _
                  vbDataActionMoveFirst, _
                  vbDataActionMoveLast, _
                  vbDataActionUnload
                  RC = MsgBox("Save changes to current record?", vbYesNo + _
                     vbQuestion)
                  If RC = vbNo Then
                     Save = False
                     Debug.Print "The data will NOT be saved."
                  Else
                     If Not IsValid() Then
                        ' Write your own IsValid Function to return T/F value
                        ' and do any additional validation that you want.
                        Action = vbDataActionCancel
                     End If
                  End If
               Case Else
                  ' This case should never be executed unless a new
                  ' (unhandled) value is added to the Action parameter.
            End Select
         End If
      End Sub
   
      Function IsValid() As Boolean
         IsValid = True
         ' Write your own validation routine here.
      End Function
   						

5. Start the program by choosing Start from the Run menu or by pressing the F5 key.
6. Make changes to the Text boxes.
7. Move off the current record by using the Data control buttons.

The current behavior is that you are prompted to save or not save the data. You can easily modify this to do any internal validation checking that you need as long as within the Validation event you do not use any of the methods described at the beginning of this article to generate another change in the current record.