Also, it is important to put theMoveNextstatement in, otherwise your code will never
finish and will just stay on the first record. TheMoveNextstatement is easily forgotten when
concentrating on other aspects of the code.
You can use the VBA code to perform updates, although it is more efficient to do this
through an Update query:
Sub TestQuery()
Dim ReSet As Recordset
Set ReSet = CurrentDb.OpenRecordset("MyQuery")
Do Until ReSet.EOF
If ReSet![Ship Name] = "Karen Toh" Then
ReSet.Edit
ReSet![Ship Name] = "Unknown"
ReSet.Update
End IfReSet.MoveNext
Loop
End Sub
The code works the same as before except that we test to see if the Ship Name field shows
Karen Toh. Notice that the field Ship Name has a space in the name so it has to be enclosed
in square brackets ([]) to show VBA where it starts and finishes.
If the name is Karen Toh, then the record is put into Edit mode and the field is changed to
Unknown. The record is then updated.
Similarly, a new field can also be added in this way:
Sub TestQuery()
Dim ReSet As Recordset
Set ReSet = CurrentDb.OpenRecordset("MyQuery")
ReSet.AddNew
ReSet!Company = "Richard"
ReSet![Order Date] = "01-Aug-2006"
ReSet![Ship Name] = "Richard Shepherd"
ReSet.Update
End Sub
Chapter 12: SQL Queries 155