OriginalValue and UnderlyingValue Properties Example (VB)

OriginalValue and UnderlyingValue Properties Example (VB)

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