Update Method Example

This example demonstrates the Update method in conjunction with Edit method.

Sub UpdateX()

    Dim dbsNorthwind As Database
    Dim rstEmployees As Recordset
    Dim strOldFirst As String
    Dim strOldLast As String
    Dim strMessage As String

    Set dbsNorthwind = OpenDatabase("Northwind.mdb")
    Set rstEmployees = _
        dbsNorthwind.OpenRecordset("Employees")

    With rstEmployees
        .Edit
        ' Store original data.
        strOldFirst = !FirstName
        strOldLast = !LastName
        ' Change data in edit buffer.
        !FirstName = "Linda"
        !LastName = "Kobara"

        ' Show contents of buffer and get user input.
        strMessage = "Edit in progress:" & vbCr & _
            "  Original data = " & strOldFirst & " " & _
            strOldLast & vbCr & "  Data in buffer = " & _
            !FirstName & " " & !LastName & vbCr & vbCr & _
            "Use Update to replace the original data with " & _
            "the buffered data in the Recordset?"

        If MsgBox(strMessage, vbYesNo) = vbYes Then
            .Update
        Else
            .CancelUpdate
        End If

        ' Show the resulting data.
        MsgBox "Data in recordset = " & !FirstName & " " & _
            !LastName

        ' Restore original data because this is a demonstration.
        If Not (strOldFirst = !FirstName And _
                strOldLast = !LastName) Then
            .Edit
            !FirstName = strOldFirst
            !LastName = strOldLast
            .Update
        End If

        .Close
    End With

    dbsNorthwind.Close

End Sub

This example demonstrates the Update method in conjunction with the AddNew method.

Sub UpdateX2()

    Dim dbsNorthwind As Database
    Dim rstEmployees As Recordset
    Dim strOldFirst As String
    Dim strOldLast As String
    Dim strMessage As String

    Set dbsNorthwind = OpenDatabase("Northwind.mdb")
    Set rstEmployees = _
        dbsNorthwind.OpenRecordset("Employees")

    With rstEmployees
        .AddNew
        !FirstName = "Bill"
        !LastName = "Sornsin"

        ' Show contents of buffer and get user input.
        strMessage = "AddNew in progress:" & vbCr & _
            "  Data in buffer = " & !FirstName & " " & _
            !LastName & vbCr & vbCr & _
            "Use Update to save buffer to recordset?"

        If MsgBox(strMessage, vbYesNoCancel) = vbYes Then
            .Update
            ' Go to the new record and show the resulting data.
            .Bookmark = .LastModified
            MsgBox "Data in recordset = " & !FirstName & _
                " " & !LastName
            ' Delete new data because this is a demonstration.
            .Delete
        Else
            .CancelUpdate
            MsgBox "No new record added."
        End If

        .Close
    End With

    dbsNorthwind.Close

End Sub