4.4 Writing the VBA code
In the “Click” event handler of the “Update SendCard” button, specify the following code:
1Dim myRecordSet as RecordSet
2Set myRecordSet = CurrentDB.
OpenRecordSet(”Contacts”, dbOpenDynaset)
3myRecordSet.
MoveFirst 4Do While Not myRecordSet.
EOF 5 myRecordSet.
Edit 6 If (
MsgBox(”Send_card_to_” & _
7 myRecordSet![First
Name] & ”_” & _
8 myRecordSet![Last
Name] & ”?”, _
9 vbYesNo) = vbYes)
then 10 myRecordSet![SendCard] = 1
11 Else 12 myRecordSet![SendCard] = 0
13 End If
14 myRecordSet.
Update 15 myRecordSet.
MoveNext 16Loop
The following is the overall explanation of the code:
- lines 1 to 2: define the RecordSet variable and initialize it.
- line 3: move the window to the first record.
- lines 4 and 16: defines the beginning and ending of the loop. The condition to stay in the loop is
“Not myRecordSet.EOF”. This means that as long as we have not reached the end of the RecordSet, perform
the statements between Do While and Loop.
- line 5: this tells VBA that we are about to perform some editing. This method (Edit) prepare the necessary
buffer to do so.
- lines 6 to 13: this is the conditional statement that determines whether we want to send a card to a particular
contact list item. We’ll explain this one later.
- line 14: this line looks at the revised RecordSet buffer and updates the associated table.
A lot of mystery is in the conditional statement itself, so it deserves some further explanations:
- The ampersand (&) symbol is a string concatination operator. It makes a longer string by connecting the strings
to its left and right.
- The underscore (_) symbol lets you break up a long line into shorter lines. It means “continue on the following
line”.
- myRecordSet![First Name] and myRecordSet![Last Name] refer to the “First Name” and “Last Name” fields
of the record being examined by the RecordSet “myRecordSet”.
- vbYesNo specifies that a dialog box presents two buttons, one labeled “Yes”, the other labeled “No”.
- MsgBox("xyz", vbYesNo) displays a message box with “xyz” as the text inside. The message box also presents
“Yes” and “No” as buttons. When the user clicks either button, “MsgBox” returns a value and the VBA
program continues execution.
- MsgBox("xyz", vbYesNo) = vbYes checks to see whether the “Yes” button was clicked.
- myRecordSet![SendCard] = 1 updates the “SendCard” field of the record in the “myRecordSet” RecordSet.
Note that this does not update the table. This statement only updates the buffer that was set up on line 5.
The actual change happens on 14.