10.2. Properties

All forms have a set of properties that are specific to them, and each control (text box, command button, and so on) on the form has its own properties as well. There are many properties that share a common name between a form and the controls contained on it, but each object retains its own values. For example, a form and a text box both have a visible property, but the text box property may be set to False, while the form property is set to True. For review, the following table displays a list of example properties. A longer list can be found in Appendix E.

10.2.1. Event Properties

Event properties are the driving force behind the automation of a form. These properties are the reaction to a situation triggered by the user. Examples include clicking a command button, updating a text box, or entering a value that does not exist in a combo box. Anytime the user triggers one of these actions, the programmer must react appropriately to perform tasks such as opening another form (or report), sending an e-mail, and/or data validation. The possibilities of what can be performed are endless, but they all start with an event property.

The following table displays some of the frequently used event properties and what causes them to be executed. Some of these properties will be demonstrated in the code samples later in the chapter.

Just like the regular properties (that is, BackColor, Visible, or AllowEdits), Forms have their own set of event properties, and each control on that Form has its own as well. The items in the list above represent a cross section of event properties that exist for Forms and Controls. Not all controls have the same event properties as a Form, and depending on the type of control, it may have additional event properties to handle specific situations that only exist for that control. For example, a combo box (a control with which the user can select an existing value, or enter one not available) has a LimitToList property that restricts the user to only selecting a value that exists. If the user does not select or enter a valid value from this property, then the NotInList event property will trigger, and the programmer can add code to react to it.

Knowing what event properties are available and knowing when they will execute is the heart of VBA programming. Another tidbit to launch you forward is that some event properties can be cancelled, while others may not. Learning these three elements will take some time, but just knowing that they are there is a good start.

10.2.2. Associating Code to an Event Property

When you are ready to begin associating VBA code to an event property, you can start from either the Design View of the Form or the VBA Editor window itself. Both methods are useful, and when you utilize them will depend on your current location (form or editor) at the time that you need to start a new procedure.

From the Form Design window, click to highlight the desired control, then choose View/Properties from the main menu. (You can also press F4 or just double-click the control to open the Properties window.) Once opened, click the tab called Event, open the combo box associated to the desired event property, and choose [Event Procedure], as shown in Figure 10-1. Performing this step signifies to Access that you want to execute a VBA event procedure when the user triggers the event. Notice in Figure 10-1 that a macro can also be assigned to an event property. This is why there is a selection for event procedure. The reason it is in square brackets is that it will always be the first item in the combo box.

Note: To speed the selection of [Event Procedure], when you enter the combo box of an event property, press the Left Square Bracket key "[,"or just double-click the combo box. (Double-clicking a second time will select the next value in the list.)

Figure 10.1. Figure 10-1

After the selection of [Event Procedure], click the button that is just to the right of the combo box "opener." (The button has the Period of Ellipses [... ] on it.) This action will cause the VBA Editor window to open. When the event procedure for the event property is opened for the first time, a new sub procedure is created for it. If a sub procedure already exists for the form or control event property, then that existing sub procedure is opened.

From the Module window itself, use the combo boxes at the top of the Code window to select the desired control, and then the subsequent event property. The combo box on the left has a value for the form, and all controls on the form. The combo box on the right has all of the possible event procedures based on the selection from the left combo box.

Figure 10-2 shows the Control and Event Property combo boxes, as well as a sample event procedure structure that is created for you. Notice that the procedure is a Sub, which means that it cannot return a value. It is also dimensioned as Private, which means that other modules in the database cannot see or utilize it. The last thing to notice in the picture is that the sub procedure has the Cancel parameter. The use of the Cancel parameter is described later in this chapter.

Figure 10.2. Figure 10-2

Using a naming convention comes in handy when viewing the Control combo box in the VBA Editor. All controls are listed alphabetically, so voluntarily adding a prefix to each control name will sort each control type together (that is, txt, cbo, cmd, and so on).

10.2.3. You Talking to Me?

Programmers are typically self-obsessed beings, so on your journey to learn about Form Automation, it is important to understand that "It's all about Me!" No, Me is not about You, Me is about the currently active Form. If you want to know anything about the Form, you can request information from the Me object. Using Me will expose all of the events, properties, and controls associated to it. To utilize Me in your code, enter Me. (dot or period) and then select the desired value from the IntelliSense combo box, which automatically appears. See Figure 10-3.

Figure 10.3. Figure 10-3

An example of using Me is if you want to know whether the user edited anything about the current record, you can inquire with the following:

If Me.Dirty then

Or, if you would like to change the caption of the current form, you could use the following command to change the Caption property:

Me.Caption = "Employees Form - Edited"

Finally, there may be times when you need to examine a value about a form but have no knowledge ahead of time what the "about" may be. There are many collections contained within the Me object that you can expose, even with very little information about the collection contents.

For example, suppose you need to reference the value of a certain control, but you don't know the name of the control before it happens. The ability to refer to that control in code using the specific name (that is, txtFirstName) will not work, so you may need a generic procedure, and need the flexibility to send it a variety of information like the following:

Private Sub DoSomething(pstrControlName As String)
  If Me.Controls(pstrControlName).Value > 50 then
      ...'Do something
  Else
      ...'Do something else
  End If
End Function

This type of programming begins to scare the faint of heart, but it demonstrates not only a practical use of the Me object, but also the power and flexibility that VBA possesses. In the above example, the Me object contains a collection called Controls, which is the same as referencing a control name directly. To this collection, a parameter can be passed in the shape of either a literal value (that is, "txtFirstName", quotes required), a variable that contains the value (that is, pstrControlName), or an ordinal value (that is, zero (0) represents the first value in the collection).

The Me object is a very important and useful tool that can be used in any of the many event procedures that are written.