Access VBA Macro Programming

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

RecSet.Edit

RecSet![Company] = "Northwind"

RecSet.Update

End If

RecSet.MoveNext

Loop

If MsgBox("Save all changes?", vbQuestion + vbYesNo) = vbYes Then

WrkSp.CommitTrans

Else

WrkSp.Rollback

End If

RecSet.Close

WrkSp.close

Set RecSet = Nothing

Set WrkSp = Nothing

End Sub

In this example, there is a newWorkspaceobject called WrkSp. This is set to the current

workspace. Before your code is run to edit the recordset, aBeginTransstatement is used.

This defines the beginning of the transaction and ensures that nothing can happen to the data

until aCommitTransstatement is issued.

When the code has iterated through the recordset, a message box is displayed to ask to

save all changes. If the user selects Yes, aCommitTransstatement is made, which saves all

the changes to the table. If the user chooses No, then aRollbackstatement is made that

erases all the changes.

DFirst

This methodDFirstlets you extract a field value in the first record found in a table. An

example is:

MsgBox DFirst("MyField", "MyTable")

This will return the value of MyField for the first record in the table MyTable. You can use

an optional criterion as follows:

MsgBox DFirst("MyField", "MyTable", "MyValue> 6 ")

DLast

This methodDLastallows you to extract a field value in the last record found in a table. An

example is:

MsgBox DLast("MyField", "MyTable")

196 Microsoft Access 2010 VBA Macro Programming