OriginalValue and UnderlyingValue Properties Example

This example demonstrates the OriginalValue and UnderlyingValue properties by displaying a message if a record's underlying data has changed during a Recordset batch update.

Public Sub OriginalValueX()

   Dim cnn1 As ADODB.Connection
   Dim rstTitles As ADODB.Recordset
   Dim fldType As ADODB.Field
   Dim strCnn As String

   ' Open connection.
   Set cnn1 = New ADODB.Connection
   strCnn = "Provider=sqloledb;" & _
      "Data Source=srv;Initial Catalog=pubs;User Id=sa;Password=; "
   cnn1.Open strCnn

   ' Open recordset for batch update.
   Set rstTitles = New ADODB.Recordset
   Set rstTitles.ActiveConnection = cnn1
   rstTitles.CursorType = adOpenKeyset
   rstTitles.LockType = adLockBatchOptimistic
   rstTitles.Open "titles"

   ' Set field object variable for Type field.
   Set fldType = rstTitles!Type

   ' Change the type of psychology titles.
   Do Until rstTitles.EOF
      If Trim(fldType) = "psychology" Then
         fldType = "self_help"
      End If
      rstTitles.MoveNext
   Loop

   ' Similate a change by another user by updating 
   ' data using a command string.
   cnn1.Execute "UPDATE titles SET type = 'sociology' " & _
      "WHERE type = 'psychology'"

   'Check for changes.
   rstTitles.MoveFirst
   Do Until rstTitles.EOF
      If fldType.OriginalValue <> _ 
         fldType.UnderlyingValue Then

         MsgBox "Data has changed!" & vbCr & vbCr & _
            "  Title ID: " & rstTitles!title_id & vbCr & _
            "  Current value: " & fldType & vbCr & _
            "  Original value: " & _
            fldType.OriginalValue & vbCr & _
            "  Underlying value: " & _
            fldType.UnderlyingValue & vbCr
      End If
      rstTitles.MoveNext
   Loop

   ' Cancel the update because this is a demonstration.
   rstTitles.CancelBatch
   rstTitles.Close

   ' Restore original values.
   cnn1.Execute "UPDATE titles SET type = 'psychology' " & _
      "WHERE type = 'sociology'"
   
   cnn1.Close
   
End Sub