4.3.4 Working with Recordsets: Editing Records

In Access VBA, you can use Recordsets to interact with and modify the data in your tables. After opening a Recordset in an editable mode, such as Dynaset or Table mode, you can edit the values of existing records. This section will explain how to edit records using VBA, including the process, syntax, and examples for updating field values in a Recordset.

1. Basic Steps to Edit Records with Recordsets

To edit an existing record, you need to follow these general steps:

1. Open a Recordset: Open the Recordset in Edit mode (Dynaset or Table mode).
2. Locate the Record: Use methods like FindFirst or loop through the records to find the record you wish to edit.
3. Edit the Record's Fields: Once the desired record is located, assign new values to the fields you wish to update.
4. Call the Update Method: After editing the fields, call the Update method to save the changes to the database.
5. Close the Recordset: Close the Recordset when you are done.

2. Using the Edit Method

Unlike the AddNew method for adding new records, the Edit method is automatically triggered when you move to a record for editing. Once you move to the desired record, you can simply change the field values directly.

Key Syntax:

Recordset.Edit
Recordset!FieldName = NewValue
Recordset.Update

• Edit: Puts the Recordset into edit mode for the current record.
• FieldName: The name of the field/column in the table (or query) you want to edit.
• NewValue: The new value you want to assign to the field.
• Update: Saves the changes made to the record.

3. Example of Editing a Record

In this example, we will edit an existing record in the Employees table. We will change the Salary for an employee whose EmployeeName is "John Doe".

Dim db As Database
Dim rs As Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("Employees", dbOpenDynaset) ' Open the table in Dynaset mode

' Find the record for "John Doe"
rs.FindFirst "EmployeeName = 'John Doe'"

If Not rs.NoMatch Then
    ' Start editing the record
    rs.Edit

    ' Change the salary
    rs!Salary = 60000  ' Update the salary to 60000

    ' Save the changes to the record
    rs.Update
    MsgBox "Record updated successfully"
Else
    MsgBox "Record not found"
End If

' Close the Recordset
rs.Close
Set rs = Nothing
Set db = Nothing

Explanation:

1. We open the Employees table in Dynaset mode, which allows us to edit records.
2. We use FindFirst to locate the record where EmployeeName = 'John Doe'.
3. If the record is found (Not rs.NoMatch), we use the Edit method to begin editing that record.
4. We change the value of the Salary field to 60000.
5. We call Update to save the changes made to the record.
6. After processing, we close the Recordset and clean up the objects.

4. Editing Multiple Records in a Loop

If you need to edit multiple records in a loop, you can iterate through the records and make changes as necessary. The process is similar, but instead of editing a single record, you will use a loop to apply updates to multiple records.

Example of Editing Multiple Records:

Dim db As Database
Dim rs As Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("Employees", dbOpenDynaset) ' Open table in Dynaset mode

' Loop through the records
Do While Not rs.EOF
    ' If the employee is in the "IT" department and their salary is below 50000, update salary
    If rs!Department = "IT" And rs!Salary < 50000 Then
        rs.Edit
        rs!Salary = rs!Salary + 5000  ' Increase salary by 5000
        rs.Update
    End If
    rs.MoveNext
Loop

' Close the Recordset
rs.Close
Set rs = Nothing
Set db = Nothing

Explanation:

1. We open the Employees table and loop through each record.
2. Inside the loop, if the Department is "IT" and the Salary is below 50000, we edit that record.
3. The Salary is increased by 5000, and the record is updated using Update.
4. After processing all records, we close the Recordset.

5. Error Handling for Editing Records

Error handling is important to prevent issues when editing records, such as missing fields or issues with the database. Adding error handling ensures the process runs smoothly, even if something goes wrong.

Example with Error Handling:

Dim db As Database
Dim rs As Recordset

On Error GoTo ErrorHandler ' Error handling setup

Set db = CurrentDb
Set rs = db.OpenRecordset("Employees", dbOpenDynaset)

' Find the record for "John Doe"
rs.FindFirst "EmployeeName = 'John Doe'"

If Not rs.NoMatch Then
    rs.Edit
    rs!Salary = 65000  ' Update the salary
    rs.Update
    MsgBox "Record updated successfully"
Else
    MsgBox "Record not found"
End If

' Close the Recordset
rs.Close
Set rs = Nothing
Set db = Nothing

Exit Sub

ErrorHandler:
    MsgBox "Error: " & Err.Description
    rs.Close
    Set rs = Nothing
    Set db = Nothing
End Sub

Explanation:

1. The On Error GoTo ErrorHandler directs the program to the ErrorHandler block if an error occurs.
2. If the record is found, it is edited and updated.
3. If an error occurs (e.g., due to missing fields), the error message will display the error description.
4. The Recordset is closed and cleaned up even if an error occurs.

6. Working with NULL Values

Sometimes, you may need to update fields with NULL values. To set a field to NULL, you can use the IsNull function or assign Nothing to the field.

Example with NULL Values:

Dim db As Database
Dim rs As Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("Employees", dbOpenDynaset)

' Find the record for "John Doe"
rs.FindFirst "EmployeeName = 'John Doe'"

If Not rs.NoMatch Then
    rs.Edit
    rs!Salary = Null  ' Set Salary to NULL
    rs.Update
    MsgBox "Salary updated to NULL"
Else
    MsgBox "Record not found"
End If

' Close the Recordset
rs.Close
Set rs = Nothing
Set db = Nothing

Explanation:

• We use Null to set the Salary field to NULL.
• The changes are saved using Update.

Conclusion

Editing records with Recordsets in Access VBA is a straightforward process involving opening the Recordset in an editable mode, using the Edit method, and then saving changes with Update. Whether editing a single record or multiple records in a loop, VBA allows for flexible and efficient data manipulation. By adding proper error handling and ensuring safe operations with NULL values, you can manage your database records seamlessly.