Home

Record Maintenance

 

Updating a Value

 

Visually Editing a Value

Value edition consists of changing the value held in a field with another value. To do this, a user usually examines the records to locate the value first. To do this, the user can use the Replace dialog box. To open it, after displaying a table or a form:

  • On the Ribbon, the user can click the Find button Find and click the Replace tab
  • Press Ctrl + H

The Replace dialog box presents some similar options as the Find dialog box. These include the Find What, Look In and Match combo boxes, also the Find Next, the Cancel and the More buttons. The Replace With combo box allows you to specify the value that will replace the existing value. Here is an example:

After specifying the values, you can click Replace. If you want to replace more than one occurrence of the undesired string, you can click Find Next to locate them one by one and replace only those you want. If you want to replace all occurrences of a value, you can click Replace All.

Practical LearningPractical Learning: Introducing Record Maintenance

  1. Start Microsoft Access
  2. Open the Bethesda Car Rental2 database you were working on in Lesson 19
  3. In the Navigation Pane, right-click ExistingRentalOrder and click Design View
  4. Right-click the Submit Changes button and click Build Event...
  5. In the Choose Builder dialog box, double-click Code Builder
  6. Implement the event as follows:
     
    Private Sub cmdSubmitChanges_Click()
    On Error GoTo cmdSubmitChanges_Error
        If IsNull(txtReceiptNumber) Then
            MsgBox "There is no rental order to save"
            Exit Sub
        End If
        
        txtMileageEnd_LostFocus
        txtEndDate_LostFocus
        txtRateApplied_LostFocus
        txtTaxRate_LostFocus
       
        Exit Sub
    
    cmdSubmitChanges_Error:
        MsgBox "There was a problem when creating the new record"
    End Sub

Locating a Value in the Microsoft Access Libraries

Before editing a record, you must locate it to indicate to the record set what record is involved. Here is an example of locating a record:

Private Sub cmdUpdate_Click()
On Error GoTo cmdUpdate_Error

    Dim curDatabase As Object
    Dim rstEmployees As Object
    Dim fldEmployee As Object
    
    Set curDatabase = CurrentDb
    Set rstEmployees = curDatabase.OpenRecordset("Employees")
    
    With rstEmployees
        Do Until .EOF
            For Each fldEmployee In .Fields
                If fldEmployee.Name = "EmployeeID" Then
                    If fldEmployee.Value = CInt(txtEmployeeID) Then
                        ' The record to be edited has been located
                        
                        Exit For
                    End If
                End If
            Next
            .MoveNext
        Loop
    End With
   
    Exit Sub

cmdUpdate_Error:
    MsgBox "There was a problem when editing the record."
End Sub

Editing a Value in the Microsoft Access Libraries

To support the ability to update a record, the Recordset class is equipped with a method named Edit. Once you have located the record, call the Edit method. After calling the method, access the field or each field that needs to be edited and assign the desired value to it. After calling the Edit() method and assigning the value(s) to the appropriate field(s), you must call the Update() method of the Recordset class. This would be done as follows:

Private Sub cmdUpdate_Click()
On Error GoTo cmdUpdate_Error

    Dim curDatabase As Object
    Dim rstEmployees As Object
    Dim fldEmployee As Object
    
    Set curDatabase = CurrentDb
    Set rstEmployees = curDatabase.OpenRecordset("Employees")
    
    With rstEmployees
        Do Until .EOF
            For Each fldEmployee In .Fields
                If fldEmployee.Name = "EmployeeID" Then
                    If fldEmployee.Value = CInt(txtEmployeeID) Then
                        ' The record to be edited has been located
                        .Edit
                        .Fields("DateHired").Value = txtDateHired
                        .Fields("FirstName").Value = txtFirstName
                        .Fields("LastName").Value = txtLastName
                        .Fields("HourlySalary").Value = txtHourlySalary
                        .Update
                        Exit For
                    End If
                End If
            Next
            .MoveNext
        Loop
    End With
   
    Exit Sub

cmdUpdate_Error:
    MsgBox "There was a problem when editing the record."
End Sub

Practical LearningPractical Learning: Editing a Value

  1. Change the code of the Click event of the cmdSubmitChanges as follows:
     
    Private Sub ResetOrder()
    On Error GoTo ResetOrder_Error
    
        Me.txtReceiptNumber = ""
        Me.txtEmployeeNumber = ""
        Me.txtEmployeeName = ""
        Me.txtDriversLicenseNumber = ""
        Me.txtCustomerName = ""
        Me.txtAddress = ""
        Me.txtCity = ""
        Me.txtState = ""
        Me.txtZIPCode = ""
        Me.txtTagNumber = ""
        Me.txtMake = ""
        Me.txtModel = ""
        Me.txtCarYear = ""
        Me.cbxConditions = "Excellent"
        Me.cbxTankLevels = "Empty"
        Me.txtMileageStart = ""
        Me.txtMileageEnd = ""
        Me.txtTotalMileage = ""
        Me.txtStartDate = Date
        Me.txtEndDate = Date
        Me.txtTotalDays = "0"
        Me.txtRateApplied = "0.00"
        Me.txtTaxRate = "0.00"
        Me.cbxOrderStatus = ""
        Me.txtNotes = ""
       
        Exit Sub
    
    ResetOrder_Error:
        MsgBox "There was a problem when reseting the record."
    End Sub
    
    Private Sub cmdSubmitChanges_Click()
    On Error GoTo cmdSubmitChanges_Error
    
        Dim curDatabase As Object
        Dim rstRentalOrders As Object
        Dim fldRentalOrder As Object
        
        If IsNull(txtReceiptNumber) Then
            MsgBox "There is no rental order to save"
            Exit Sub
        End If
        
        txtMileageEnd_LostFocus
        txtEndDate_LostFocus
        txtRateApplied_LostFocus
        txtTaxRate_LostFocus
        
        Set curDatabase = CurrentDb
        Set rstRentalOrders = curDatabase.OpenRecordset("RentalOrders")
        
        With rstRentalOrders
            Do Until .EOF
                For Each fldRentalOrder In .Fields
                    If fldRentalOrder.Name = "RentalOrderID" Then
                        If fldRentalOrder.Value = CLng(txtReceiptNumber) Then
                            .Edit
                            .Fields("EmployeeNumber").Value = txtEmployeeNumber
                            .Fields("DrvLicNumber").Value = txtDriversLicenseNumber
                            .Fields("TagNumber").Value = txtTagNumber
                            .Fields("CarCondition").Value = cbxConditions
                            .Fields("TankLevel").Value = cbxTankLevels
                            .Fields("MileageStart").Value = txtMileageStart
                            .Fields("MileageEnd").Value = txtMileageEnd
                            .Fields("TotalMileage").Value = txtTotalMileage
                            .Fields("StartDate").Value = txtStartDate
                            .Fields("EndDate").Value = txtEndDate
                            .Fields("TotalDays").Value = txtTotalDays
                            .Fields("RateApplied").Value = txtRateApplied
                            .Fields("TaxRate").Value = txtTaxRate
                            .Fields("OrderStatus").Value = cbxOrderStatus
                            .Fields("Notes").Value = txtNotes
                            .Update
                            
                            Exit For
                        End If
                    End If
                Next
                .MoveNext
            Loop
        End With
        
        Set rstRentalOrders = Nothing
        Set curDatabase = Nothing
        
        MsgBox "The rental order has been updated."
        ResetOrder
       
        Exit Sub
    
    cmdSubmitChanges_Error:
        MsgBox "There was a problem when submitting the new record."
    End Sub
  2. In the Object combo box, select cmdClose
  3. Implement the event as follows:
     
    Private Sub cmdClose_Click()
        DoCmd.Close
    End Sub
  4. Return to Microsoft Access and switch the form to Form View
  5. Enter a rental order in the top text box and click the Open Rental Order button
  6. Update the record with new values
     
  7. Click Submit Changes
  8. Close the Existing Rental Order form
  9. When asked whether you want to save the form, click Yes
  10. Open the New Rental Order form and create a new rental order
  11. Close the New Rental Order form
  12. Open the Existing Rental Order form and open an existing rental
  13. Switch the form to Design View and return to Microsoft Visual Basic

Editing a Value in ADO

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 an example:

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

Editing a Value With SQL

To edit a value using SQL, you start with the UPDATE keyword and follow this formula:

UPDATE TableName
SET ColumnName = NewValue

In our formula, the TableName factor must be the name of a table in the selected or current database. The ColumnName must be a valid column in the TableName table. The NewValue factor of our formula is the new value that will replace the old one. If you use this formula, all records of the ColumnName would be given the NewValue. In some cases, you may want to change only one or more particular value. To do this, you must specify a condition that would be used to identify a record. The formula to use is:

UPDATE TableName
SET ColumnName = NewValue
WHERE Condition

The Condition factor specifies how the value will be located.

From the above table, imagine that you want to replace Peter Sagal with Peter Segal. The TableName is Videos. The column that holds the value is named Director. The NewValue is Peter Segal. The Condition can be that the shelf number is CM-8842. Based on this, to edit the value, you can use the following code:

UPDATE 	Videos
SET 	Director = "Peter Segal"
WHERE 	ShelfNumber = "CM-8842"

This can be done in code as follows:

Private Sub cmdMaintenance_Click()
    Dim strDataUpdate As String
    
    strDataUpdate = "UPDATE Videos " & _
                    "SET Director = 'Peter Segal' " & _
                    "WHERE ShelfNumber = 'CM-8842';"
    
    DoCmd.RunSQL strDataUpdate
End Sub

In the same way, in the list of videos, notice that the videos titled "Indecent Proposal" and "Fatal Attraction" have a name of director that looks the same. After checking the videos, you find out that they were actually directed by the same person. So you decide to prgrammatically edit it. You can write the SQL statement as follows:

UPDATE 	Videos
SET 	Director = "Adrian Lynn"
WHERE 	ShelfNumber = "DM-7426"

Before executing the statement, you check the table again but find out that, either by mistake or something else, another video is using the same shelf number. This means that if you execute the statement, any video that uses the same shelf number would have its director replaced, including those videos that don't have the same error. The solution to this is to make sure that you always have a way to distinguish each video from another. This is why the primary key is important. We saw in previous lessons that, when you use the AutoNumber data type on a column, Microsoft Access would take on the responsibility to assign each record a unique identifier. This value is furthermore required if the column is a primary key. Based on this, you can safely use the value of this record when doing data maintenance, such as updating a value. Therefore, the above change can be performed by using the value of the VideoID corresponding to the video whose director you want to update:

UPDATE 	Videos
SET 	Director = "Adrian Lynn"
WHERE 	VideoID = 5

This would be done programmatically as follows:

Private Sub cmdMaintenance_Click()
    Dim strDataUpdate As String
    
    strDataUpdate = "UPDATE Videos " & _
                    "SET Director = 'Adrian Lynn' " & _
                    "WHERE VideoID = 5;"
    
    DoCmd.RunSQL strDataUpdate
End Sub

This time, because we know that only one video has a VideoID equal to 5, only its record will be changed.

 
 

 

 
 

Deleting Records

 

Visually Deleting a Record

Record maintenance consists of copying a record, deleting a record, or importing records from a table in another database.

Deleting a record consists of removing it from a table (or a form). To visually do this, after displaying a table in the Datasheet View, you can right-click the box on the left side of the record and click Delete Record:

Deleting a Record

You can also click Cut. The difference is that the Cut option would copy the record to the clipboard so you can paste it anew.

Deleting a Record in the Microsoft Access Libraries

To give you the ability to remove a record, the Recordset class is equipped with a method named Delete. Of course, before performing this operation, first locate the record you want to delete. Once you have found the record, call the Delete() method. Here is an example:

Private Sub cmdDeleteRecord_Click()
On Error GoTo cmdDeleteRecord_Error

    Dim curDatabase As Object
    Dim rstEmployees As Object
    Dim fldEmployee As Object
    
    Set curDatabase = CurrentDb
    Set rstEmployees = curDatabase.OpenRecordset("Employees")
    
    With rstEmployees
        Do Until .EOF
            For Each fldEmployee In .Fields
                If fldEmployee.Name = "EmployeeID" Then
                    If fldEmployee.Value = CInt(txtEmployeeID) Then
                        ' The record to be deleted has been found
                        .Delete
                        Exit For
                    End If
                End If
            Next
            .MoveNext
        Loop
    End With
   
    Exit Sub

cmdDeleteRecord_Error:
    MsgBox "There was a problem when deleting the record."
End Sub

Practical LearningPractical Learning: Deleting a Record

  1. In the Object combo box, select cmdDeleteRecord
  2. Implement the event as follows:
     
    Private Sub cmdDeleteRecord_Click()
    On Error GoTo cmdDeleteRecord_Error
    
        Dim curDatabase As Object
        Dim rstRentalOrders As Object
        Dim fldRentalOrder As Object
        Dim Answer As VbMsgBoxResult
        
        If IsNull(txtReceiptNumber) Then
            MsgBox "There is no rental order to delete"
            Exit Sub
        End If
        
        Set curDatabase = CurrentDb
        Set rstRentalOrders = curDatabase.OpenRecordset("RentalOrders")
        
        With rstRentalOrders
            Do Until .EOF
                For Each fldRentalOrder In .Fields
                    If fldRentalOrder.Name = "RentalOrderID" Then
                        If fldRentalOrder.Value = CLng(txtReceiptNumber) Then
              Answer = MsgBox("Are you sure you want to delete this record?", _
                            VbMsgBoxStyle.vbYesNo Or VbMsgBoxStyle.vbQuestion, _
                                            "Bethesda Car Rental")
                            If Answer = VbMsgBoxResult.vbYes Then
                                .Delete
                            End If
                            
                            Exit For
                        End If
                    End If
                Next
                .MoveNext
            Loop
        End With
        
        Set rstRentalOrders = Nothing
        Set curDatabase = Nothing
        
        MsgBox "The rental order has been deleted."
        ResetOrder
       
        Exit Sub
    
    cmdDeleteRecord_Error:
        MsgBox "There was a problem when deleting the record."
    End Sub
  3. In the Object combo box, select cmdClose
  4. Implement the event as follows:
     
    Private Sub cmdClose_Click()
        DoCmd.Close
    End Sub
  5. Return to Microsoft Access and switch the form to Form View
  6. Enter a rental order in the top text box and click the Open Rental Order button
  7. Click the Delete button 
  8. Click No
  9. Close the Existing Rental Order form
  10. When asked whether you want to save the form, click Yes

Deleting Records in SQL

To programmatically delete a record using SQL, you combine the DELETE operator in the following primary formula:

DELETE FROM TableName

When this statement is executed, all records from the TableName table would be removed. Here is an example:

Private Sub cmdClearCustomers_Click()
    DoCmd.RunSQL "DELETE FROM Customers"
End Sub

In this case, all customer records from a table named Customers in the current database would be deleted. An alternative to the above formula is:

DELETE *
FROM TableName

In this formula, you use the * operator as the column placeholder. You can replace it with one or more names of columns but it doesn't matter because the DELETE operator signifies that the whole record will be deleted, regardless of the column name.

The TableName must be a valid name of a table in the specified or the current database. Here is an example:

Private Sub cmdDeleteRecords_Click()
    DoCmd.RunSQL "DELETE * FROM Videos;"
End Sub

If you execute this type of statement, all records from the table would be deleted. We saw above that the user can specify what particular record to delete instead of all records. You also can specify what record to remove from a table. To do this, use the following formula of the DELETE operator:

DELETE *
FROM TableName
WHERE Condition

This time, the Condition factor allows you to set the condition that would be applied to locate the record. Here is an example of specifying a condition to delete a record:

Private Sub cmdDeleteRecords_Click()
    DoCmd.RunSQL "DELETE * " & _
                 "FROM Videos " & _
                 "Director = 'Adrian Lynn';"
End Sub

When this code runs, all videos directed by Adrian Lynn would be deleted from the table. Instead of deleting all records like this, you may want to remove only one particular video. To do this, you must set a condition that sets that record apart. Once again, the condition can be easily handled by the primary key.

Deleting Records in ADO

In ADO, to delete a record, first locate it. To do this, you can create a SQL statement to locate the record. Then call the Delete method of the ADO.Recordset class. Here is an example:

Private Sub cmdDeleteLast_Click()
    Dim rstVideos As ADODB.Recordset
    
    Set rstVideos = New ADODB.Recordset
    rstVideos.Open "SELECT * FROM Videos WHERE Title = 'Leap of Faith'", _
		   CurrentProject.Connection, _
                   adOpenDynamic, adLockPessimistic, adCmdText
                   
    rstVideos.Delete
    
    rstVideos.Close
    Set rstVideos = Nothing
End Sub

 

 
 
   
 

Previous Copyright © 2005-2011 FunctionX, Inc. Next