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