Using Visual Basic forApplications to Enter Data in a Form

As you might have suspected 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 about 17 different events; a form recognizes more than 50.

Tip  

You can see the list of events recognized by an object by looking at the Event tab on the object s Properties dialog box.

While you use a form, objects are signaling events, or firing events , almost constantly. However, unless you attach a macro or Microsoft Visual Basic for Applications (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 the running of a macro or a VBAprocedure that performs 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, you should take a glimpse at what they re all about while you have a form open .

For example, while looking at customer records in the GardenCo database, 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 will usually generate 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 Garden Company s database, you can let VBA do it instead.

In this exercise, you will write a few lines of VBA code, and attach the code to the After Update event in the LastName text box in the Customers form. When you change the content of the text box and attempt to move somewhere else in the form, the Before Update event is fired. In response to that event, Access updates the record in the source table, and then the After Update event is fired . This is the event you are going to work with. This is by no means an in-depth treatment of VBA, but this exercise will give you a taste of VBA s power.

USE the GardenCo database and the AftUpdate text file in the practice file folder for this topic. This practice file is located in the  My Documents\Microsoft Press\Office 2003 SBS\Forms\Events folder and can also be accessed by clicking Start/All Programs/Microsoft Press/Microsoft Office System 2003 Step by Step .

OPEN the GardenCo database and acknowledge the safety warning, if necessary.

1. With Forms selected on the Objects bar, click Customers in the list of forms, and click the Design button.

2. Click the LastName text box to select it, and if necessary, press [F4] to openthe Properties dialog box.

3. Click the Event tab to see the options.

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

4. Click After Update in the list, and then click the button.

   

   The Choose Builder dialog box appears, offering you the options of buildingan expression, a macro, or VBA code.

5. Click Code Builder , and then click OK to open the VBA Editor.

   

   The Project Explorer pane lists any objects you have created to which you can attach code; in this case, only the Customers form (Form_Customers) is listed. As you create more forms and reports , they will appear here.

   The Code window displays a placeholder for the procedure that Access will useto handle the After Update event for the LastName text 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.

6. Launch a text editor, such as Microsoft Notepad, navigate to the My Documents\Microsoft Press\Office System 2003 SBS\Forms\Events folder, open the AftUpdate practice file, and copy the following lines of text to the Clipboard. Then [ALT]+[TAB]back to the Code window and paste the text between the Private Sub LastName_AfterUpdate() and End Sub statements:

    '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  

   When a line of text is preceded by an apostrophe, the text isa comment that explains the purpose of the next line of code. In the VBA Editor, comments are displayed in green.

7. Save the file, click the View Microsoft Access button to return to the Access window, and then close the Properties dialog box.

8. Switch to Form view and size the window as necessary. Then on the Navigation bar, click the New Record button to create a new record.

9. Press the [TAB] key to move the insertion point to the text box for the FirstName field, type John , press [TAB] to move to the text box for the LastName field, type Coake , and then press [TAB] again.

   If you entered the VBA code correctly, COAJO appears in the CustomerID text box.

10. Change the first and last name to something else and notice that the CustomerID text box doesn t change even if the names from which it was derived do change.

11. Press the [ESC] 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 jumpto it when either event occurs. You won t do either in these exercises, but if you are interested, you can look at the code in the database file for the next exercise to see the second solution.

12. Press [ESC] to clear your entries, and then close the Customers form.

13. Press [ALT]+[TAB] to switch to the VBA Editor, which is still open, and close the editor.

CLOSE the GardenCo database.