Microsoft Access 2010 Bible

Chapter 11: Mastering VBA Data Types and Procedures

439

l Statements: A VBA procedure can contain many statements. Usually, however, you’ll

want to keep your VBA procedures small to make debugging as painless as possible. Very

large subroutines can be difficult to work with, and you’ll avoid problems if you keep

them small. Instead of adding too many features and operations in a single procedure,

place operations in separate procedures and call those procedures when those operations

are needed.

At the conclusion of a subroutine, program flow returns to the code or action that originally called

the sub. The subroutine shown in Figure 11.9 runs in response to the form’s Load event, so con-

trol is returned to that event.

As an example of a useful VBA subroutine, the next several paragraphs describe building an event

procedure for a control on an Access form. This procedure retrieves several values from the cboB-

uyerID combo box columns and uses them in the form. The RowSource of the cboBuyerID

combo box contains six active columns, which are as follows:

VBA Column Number Value

(^0) Name: tblContacts.LastName & “, “ & : tblContacts.FirstName
1 Company (from tblContacts)
(^2) DiscountPercent (from tblTaxRates)
3 TaxRate (from tblTaxRates)
(^4) TaxLocation (from tblContacts)
5 ContactID (from tblContacts). This is the bound column of this combo box.
Note
Combo-box row sources start with column 0, so column 2 is the third column in the row source.
The objective of this exercise is to learn about procedures, but it also serves to teach you some
additional VBA commands. The code is added to the form as cboBuyerID AfterUpdate event.
To create an event procedure in a form, follow these steps:

1. Select the cboBuyerID control in frmSales Design view.


2. Press F4 to display the Property window for the control.


3. Click in the After Update event property in the Event tab of the property sheet and
   select [Event Procedure] from the event’s drop-down list.


4. Press the builder button (...) to open the VBA code editor.


5. Enter the following code into the cboBuyerIDAfterUpdate event procedure, as
   shown in Figure 11.10. The following code goes between Private Sub cboBuyerID
   AfterUpdate() and End Sub in the VBA code editor.