Access VBA Macro Programming

Notice the statements to get the field data. An exclamation mark (!) is used instead of a
dot (.) because the field names are not actually part of the Recordset object model and this
must be distinguished.
Also, because Last Name and First Name contain spaces, the field names need to be
enclosed in square brackets ([ ]), otherwise an error will occur.
This is a simple means to iterate through a recordset and show the data in each field. However,
using VBA you are more than likely going to want to manipulate the data or add a new record.
To change the data, a similar methodology can be used, but we need to use theEditand
Updatemethods:

Sub EditData()
Dim RecSet As Recordset
Set RecSet = CurrentDb.OpenRecordset("Employees")

Do Until RecSet.EOF

If RecSet![First Name] = "Nancy" Then

RecSet.Edit

RecSet![Company] = "Northwind"

RecSet.Update

End If

RecSet.MoveNext

Loop
RecSet.Close
Set RecSet = Nothing
End Sub

This uses the sameRecordsetobject as before, but there is now anIfstatement that
checks whether the First Name is Nancy. If this is true, then the recordset is put into Edit
mode and the Company field for that record is changed to Northwind. The record is then
updated in the database.
If you run this code, you will see that the Company field in the Employees table for any
employee with the first name of Nancy is changed to Northwind.
Any changes you make to data must adhere to the rules of the table. You cannot place text
into a numeric field and you cannot put a null value into a required field. Also, the table must
not already be open in design mode.
You can also add new records to the table:

Sub AddData()
Dim RecSet As Recordset
Set RecSet = CurrentDb.OpenRecordset("Employees")

Chapter 14: The Access Object Models 187