Editing a Record

There are a few steps you should follow to edit a record:

1. Once you get to the record, before editing the value, call the Edit() method of the Recordset object to indicate that you want to edit the record
2. To actually make the necessary change, you can use the Fields property of the Recordset to identify the column that holds the value. After identifying the column, assign the desired value to it
3. After editing the value, call the Update() method of the Recordset object.

This would be done as follows in the Microsoft Access Object Library:

Private Sub cmdMovePosition_Click()
   Dim dbVideoCollection As Object
   Dim rstVideos As Object

   Set dbVideoCollection = CurrentDb
   Set rstVideos = dbVideoCollection.OpenRecordset("Videos")

   rstVideos.Move 6

   rstVideos.Edit
   rstVideos("Director").Value = "Frank Marshall"
   rstVideos.Update
End Sub

Here is the DAO version of this code:

Private Sub cmdMovePosition_Click()
   Dim dbVideoCollection As DAO.Database
   Dim rstVideos As DAO.Recordset

   Set dbVideoCollection = CurrentDb
   Set rstVideos = dbVideoCollection.OpenRecordset("Videos1")

   rstVideos.Move 6

   rstVideos.Edit
   rstVideos("Director").Value = "Frank Marshall"
   rstVideos.Update
End Sub

In some cases, jumping to a record to edit it could produce unpredictable results. For example, if one or more records are deleted below record number 7, the video titled Congo would not be at that position anymore and if you use the above code to edit the record, you may modify the wrong record. An alternative is to navigate among records looking for a particular value. In the previous lesson, we saw different ways of doing this. You can use record navigation to check the value of each column looking for the one that holds the value you want to change. Once a record matches a criterion you have set, edit it using the same above approach. This would be done as follows:

Private Sub cmdMovePosition_Click()
    Dim dbVideoCollection As Object
    Dim rstVideos As Object
    Dim fldEnumerator As Object
    Dim fldColumns As Object

    Set dbVideoCollection = CurrentDb
    Set rstVideos = dbVideoCollection.OpenRecordset("Videos")
    Set fldColumns = rstVideos.Fields

    ' Scan the records from beginning to each
    While Not rstVideos.EOF
        ' Check the current column
        For Each fldEnumerator In rstVideos.Fields
            ' If the column is named Title
            If fldEnumerator.Name = "Title" Then
                ' If the title of the current record is "Congo"
                If fldEnumerator.Value = "Congo" Then
                    ' then change its value
                    rstVideos.Edit
                    rstVideos("Director").Value = "Frank Marshall"
                    rstVideos.Update
                End If
            End If
        Next
        ' Move to the next record and continue the same approach
        rstVideos.MoveNext
    Wend
End Sub

1. In the Database window, click Forms. Right-click CustomerOrders and click Design View
2. In the Event tab of the Properties window, double-click On Load and click its ellipsis button
3. Implement the event as follows:
    

   ' When this form is opened, it checks for the dates of the items ' and applies the appropriate discount to each item, if necessary Private Sub Form_Load() On Error GoTo Err_cmdApplyPeriodicDiscounts_Click Dim curDatabase As Object Dim rstSaleItems As Object Dim tblSaleItems As Object Dim fldSaleItem As Object Dim dteSixMonthsAgo As Date Dim dteThreeMonthsAgo As Date ' Get a reference to the current database Set curDatabase = CurrentDb ' Get a reference to a table named SaleItems Set tblSaleItems = curDatabase.TableDefs("SaleItems") ' Create a Recordset object from the specified table Set rstSaleItems = tblSaleItems.OpenRecordset dteSixMonthsAgo = DateAdd("m", -6, Date) dteThreeMonthsAgo = DateAdd("m", -3, Date) With rstSaleItems ' Check each record While Not .EOF ' Once you get to a record, check each column For Each fldSaleItem In .Fields ' Once you find the DateEntered column If fldSaleItem.Name = "DateEntered" Then .Edit ' Find out what its corresponding date is ' 1. If the item was entered more than three months ago ' Then apply a 25% discount If fldSaleItem.Value < dteSixMonthsAgo Then .Fields("DiscountRate") = 0.5 .Fields("PriceAfterDiscount") = Nz(.Fields("MarkedPrice")) - _ CLng(Nz(.Fields("MarkedPrice")) * 0.5 * 100) / 100 ' 2. If the item was entered more than six months ago ' Then apply a 50% discount ElseIf fldSaleItem.Value <= dteThreeMonthsAgo Then .Fields("DiscountRate") = 0.25 .Fields("PriceAfterDiscount") = Nz(.Fields("MarkedPrice")) - _ CLng(Nz(.Fields("MarkedPrice")) * 0.25 * 100) / 100 ' 3. If the item was entered less than three months ago ' Then there is no discount, yet Else ' fldSaleItem.Value >= dteThreeMonthsAgo Then .Fields("DiscountRate") = 0# .Fields("PriceAfterDiscount") = .Fields("MarkedPrice") End If .Update End If Next .MoveNext Wend End With rstSaleItems.Close Set rstSaleItems = Nothing Exit_cmdApplyPeriodicDiscounts_Click: Exit Sub Err_cmdApplyPeriodicDiscounts_Click: MsgBox Err.Description Resume Exit_cmdApplyPeriodicDiscounts_Click End Sub

4. Return to Microsoft Access
5. Save the form and switch it to Form View
6. Create a few sample customers orders as follows:
    
7. Close the form

The approach to editing a record using ADO follows the same algorithms as reviewed for the Microsoft Access Object Library or DAO except that, with ADO, you don't call the Edit() method. Here is the ADO version of the version code:

Private Sub cmdMovePosition_Click()
   Dim rstVideos As ADODB.Recordset

   Set rstVideos = New ADODB.Recordset
   rstVideos.Open "Videos", CurrentProject.Connection, _
                  adOpenForwardOnly, adLockOptimistic, adCmdTable

   rstVideos.Move 6

   rstVideos("Director").Value = "Frank Marshall"
   rstVideos.Update
   
   rstVideos.Close
   Set rstVideos = Nothing
End Sub

Here is the ADO version of the second technique:

Private Sub cmdMovePosition_Click()
    Dim rstVideos As ADODB.Recordset
    Dim fldEnumerator As ADODB.Field

    Set rstVideos = New ADODB.Recordset
    rstVideos.Open "Videos", CurrentProject.Connection, _
                  adOpenForwardOnly, adLockOptimistic, adCmdTable

    ' Scan the records from beginning to each
    While Not rstVideos.EOF
        ' Check the current column
        For Each fldEnumerator In rstVideos.Fields
            ' If the column is named Title
            If fldEnumerator.Name = "Title" Then
                ' If the title of the current record is "Congo"
                If fldEnumerator.Value = "Congo" Then
                    ' then change its value
                    rstVideos("Director").Value = "Frank Marshall"
                    rstVideos.Update
                End If
            End If
        Next
        ' Move to the next record and continue the same approach
        rstVideos.MoveNext
    Wend
   
    rstVideos.Close
    Set rstVideos = Nothing
End Sub

1. In the Forms section of the Database window, right click NewInventoryItem and click Save As...
2. Type InventoryItemItem as the name of the new forms and click OK
3. Add a Check Box to the form and set its Name to chkUpdateSales
4. Change the caption of the accompanying label to Update Sales Item Also
5. Change the value of the Submit button as follows:
   Caption: Submit Change
   Name: cmdSubmitChange
6. Save the form
    
   
7. Save the form
8. Right-click the Submit Change button and click Build Event
9. Implement the event as follows:
    

   Private Sub cmdSubmitChange_Click() On Error GoTo Err_cmdSubmitItem_Click Dim rstInventoryItems As ADODB.Recordset Dim rstSaleItems As ADODB.Recordset Dim fldItem As ADODB.Field Set rstInventoryItems = New ADODB.Recordset rstInventoryItems.Open "InventoryItems", CurrentProject.Connection, _ adOpenStatic, adLockOptimistic With rstInventoryItems While Not .EOF For Each fldItem In .Fields If fldItem.Name = "ItemNumber" Then If fldItem.Value = txtItemNumber Then .Fields("ItemName") = txtItemName .Fields("ItemCategoryID") = cboItemCategoryID .Fields("ItemSize") = txtItemSize .Fields("OriginalPrice") = txtOriginalPrice .Fields("UnitsInStock") = txtUnitsInStock .Update End If End If Next .MoveNext Wend End With Set rstSaleItems = New ADODB.Recordset rstSaleItems.Open "SaleItems", CurrentProject.Connection, _ adOpenStatic, adLockOptimistic If Me.chkUpdateSales.Value = True Then With rstSaleItems While Not .EOF For Each fldItem In .Fields If fldItem.Name = "ItemNumber" Then If fldItem.Value = txtItemNumber Then .Fields("ItemName") = txtItemName .Fields("ItemCategoryID") = cboItemCategoryID .Fields("ItemSize") = txtItemSize .Fields("MarkedPrice") = txtOriginalPrice .Fields("UnitsInStock") = txtUnitsInStock .Update End If End If Next .MoveNext Wend End With End If cmdReset_Click rstInventoryItems.Close rstSaleItems.Close Set rstInventoryItems = Nothing Set rstSaleItems = Nothing Me.txtItemNumber.SetFocus Exit_cmdSubmitItem_Click: Exit Sub Err_cmdSubmitItem_Click: MsgBox Err.Description Resume Exit_cmdSubmitItem_Click End Sub

10. Close Microsoft Visual Basic
11. Save the form and switch it to Form View