Entering Data in a Form by Using VBA

As you might suspect by now, almost everything in Access, including the Access program itself, is an object. One of the characteristics of objects is that they can recognize and respond to events, which are essentially actions. Different objects recognize different events. The basic events, recognized by almost all objects, are Click, Double Click, Mouse Down, Mouse Move, and Mouse Up. Most objects recognize quite a few other events. A text control, for example, recognizes 17 different events; a form recognizes more than 50.

Tip 

The events recognized by an object are listed on the Event tab in the object’s Property Sheet pane.

While you use a form, objects are signaling events, or firing events, almost constantly. However, unless you attach a macro or VBA procedure to an event, the object is really just firing blanks. By default, Access doesn’t do anything obvious when it recognizes most events. So without interfering with the program’s normal behavior, you can use an event to specify what action should happen. You can even use an event to trigger a macro to run or a VBA procedure to perform a set of actions.

Sound complicated? Well, it’s true that events are not things most casual Access users tend to worry about. But because knowing how to handle events can greatly increase the efficiency of objects such as forms, it is helpful to have an idea of what they’re all about.

For example, while looking at customer records in one of the exercise databases, you might have noticed that the CustomerID is composed of the first three letters of the customer’s last name and the first two letters of his or her first name, all in capital letters. This technique usually generates a unique ID for a new customer. If you try to enter an ID that is already in use, Access won’t accept the new entry, and you’ll have to add a number or change the ID in some other way to make it unique. Performing trivial tasks, such as combining parts of two words and then converting the results to capital letters, is something a computer excels at. So rather than typing the ID for each new customer record that is added to the database, you can let VBA do it for you.

In this exercise, you will write a few lines of VBA code and attach the code to an event in a form. This is by no means an in-depth treatment of VBA, but this exercise will give you a taste of the power of VBA.

Use the 05_VBA database and the 05_AftUpdate text file. These practice files are located in the Chapter10 subfolder under SBS_Office2007.

Open the 05_VBA database. Then open the Customers form in Design view.

1. In the Customers form, click the LastName text box, and then if the Property Sheet pane isn’t already open, press to open it.

2. Click the Event tab.

This tab lists the events to which the LastName text box control can respond.

3. In the Property Sheet pane, click the ellipsis button to the right of the After

Update property.

The Choose Builder dialog box opens, offering you the options of building an expression, a macro, or VBA code.

4. Click Code Builder, and then click OK.

The VBA Editor opens.

The Project Explorer pane lists any objects in the database to which you can attach code; in this case, only the Customers form (Form_Customers) appears in the list. New forms and reports appear here automatically.

The Code window displays a placeholder for the procedure that Access will use to handle the After Update event for the LastName text box control. This procedure is named Private Sub LastName_AfterUpdate(), and at the moment it contains only the Sub and End Sub statements that mark the beginning and end of any procedure.

5. Navigate to the Documents\MSP\SBS_Office2007\Chapter10 folder, double-click the 05_AftUpdate text file to open it in your default text editor, and then copy the following lines of text to the Clipboard.

      'Create variables to hold first and last names      ' and customer ID      Dim fName As String      Dim lName As String      Dim cID As String      'Assign the text in the LastName text box to      ' the lName variable.      lName = Forms!customers!LastName.Text      'You must set the focus to a text box before      ' you can read its contents.      Forms!customers!FirstName.SetFocus      fName = Forms!customers!FirstName.Text      'Combine portions of the last and first names      ' to create the customer ID.      cID = UCase(Left(lName, 3) & Left(fName, 2))      'Don't store the ID unless it is 5 characters long      ' (which indicates both names filled in).      If Len(cID) = 5 Then           Forms!customers!CustomerID.SetFocus           'Don't change the ID if it has already been           ' entered; perhaps it was changed manually.           If Forms!customers!CustomerID.Text = "" Then                Forms!customers!CustomerID = cID           End If      End If      'Set the focus where it would have gone naturally.      Forms!customers!Address.SetFocus

Important 

A line of text beginning with an apostrophe is a comment that explains the purpose of the next line of code. In the VBA Editor, comments are displayed in green.

6. Switch back to the Code window, and then paste the copied text between the Private Sub LastName_AfterUpdate() and End Sub statements.

7. On the File menu, click Save 05_VBA to save your changes.

8. On the File menu, click Close and Return to Microsoft Access to return to the Access window. Then close the Property Sheet pane.

9. Switch to Form view. On the Navigation bar, click the New Record button. A blank Customers form appears.

10. In the new record, press the key to move the insertion point to the FirstName box, type Chris, press to move to the LastName box, type Sells, and then press again.

If you followed the above steps correctly, SELCH appears in the CustomerID box.

11. Change the first name to Dana and the last name to Birkby. Notice that the original CustomerID doesn’t change, even when the names from which it was derived do.

12. Press the key to remove your entry, and then try entering the last name first, followed by the first name.

Access does not create a Customer ID. The code does what it was written to do, but not necessarily what you want it to do, which is to create an ID regardless of the order in which the names are entered. There are several ways to fix this problem. You could write a similar procedure to handle the After Update event in the FirstName text box, or you could write one procedure to handle both events and then jump to it when either event occurs. You won’t do either in this exercise, but if you are interested, you can look at the code in the database file provided for the exercise in “Creating a Form by Using a Wizard” later in this chapter to see the second solution.

13. Press to clear your entries.

Close the 05_VBA database.