Chapter 2: Customize an Application with Visual Basic
Estimated Time: 40 minutes
In this chapter you will learn how to:
When you take the city bus around town, trip planning is of the utmost importance. Where does the bus stop? How close does it get you to your destination? Like it or not, the bus follows a predetermined route. But wouldn't it be nice if you had complete control of the bus route? You could just sit near the bus driver and say, "Take a left at the next light, then head that way for half a mile or soI'll get off at the second building on the right."
We all love to be in control of our situationa characteristic especially true of computer users and programmers. If there's one type of complaint you'll hear from computer users, it's along the lines of: "This machine doesn't let me do what I want it to," or "I sure wish I could make the software work a different way." Working with Microsoft Access is no exception. Sure, you can get lots of work done without too much extra work, and you can even customize the way your application looks and behaves. But if you want it to work a specific way, or if you're developing an application for others who have specific needs, it's time to get into Microsoft Visual Basic. It's more work to customize an application, but there are unlimited possibilities when you make the effort to program your own solutions in Visual Basic.
In this chapter, you'll start taking controltelling the application how to work, rather than following the rules built into Access.
Start the Lesson
Customizing a Command Button
In the first chapter, you created a button using the Command Button Wizard, and the button worked just as plannedit moved to the new record in the Contacts form. But what if you create a button with a wizard, and it doesn't work quite right? Or what if the wizard doesn't offer a button that does what you want? You've probably guessed by now: the answer lies in the Visual Basic code that makes the button work.
In this section, you'll open the Visual Basic event procedure for the Add Record button. When you do, you'll get your first look at the Visual Basic window, which contains all the tools and commands for creating Visual Basic code in Access or other Microsoft Office applications. After exploring the Visual Basic code for the Add Record button, you'll write your first line of code, making a small improvement to the code the wizard created.
Open the Contacts Form
Open an Event Procedure in Visual Basic
Next, you'll take a look at the event procedure that makes the Add Record button work.
Viewing the Visual Basic Code Behind Your Application
Every Access database application has a Visual Basic project that stores all its Visual Basic code. When you first view or run code in a database, Visual Basic opens its associated project. Projects in turn contain modules, each storing code for a different purpose. The Project window, displayed by default in the upper left corner of the Visual Basic window, provides a table of contents for the modules in your application.
Each form and report in a database has its own attached form module or report module for storing Visual Basic codefor example, the code that the wizard created for your button is stored in the form module for the Contacts form. Most Visual Basic code you'll write will belong to an individual form or report; however, if you write code that applies to more than one form or report, you can store it in one or more standard modules, using separate modules to group code for different purposes.
When you view form or report modules or read about them in the Access documentation, you may see them referred to as class modules. For the purposes of this book, you needn't concern yourself with the meaning of this phrase (it's borrowed from object-oriented computer science); just remember that form and report modules are in the larger category of class modules, while standard modules are not.
Within your application's modules, Visual Basic code comes in units called procedures, each performing a single taskfor example, the code that responds to the clicking of the Add Record button is one procedure. A module can contain many procedures, one for each event you want to respond to or task you want to perform.
Take a Closer Look at the Button's Click Event Procedure
Let's focus in on the event procedure that the Command Button Wizard created for the Add Record button. When you first open a module, Visual Basic displays all procedures in the module: if you scroll up and down in the Code window, you'll see several other Visual Basic procedures that the Database wizard created for the Contacts form. To simplify the display, you can switch from Full Module view to Procedure view so that the Code window displays only one procedure at a time.
Now the window displays only one event procedure, the AddRecord_Click procedure.
The Visual Basic code in this procedure runs automatically each time you click the Add Record button. The main attraction in the procedure is the following line of Visual Basic code, which tells Access to move to the new record.
DoCmd.GoToRecord , , acNewRec
Let's take apart this line piece by piece to understand how it works. Along the way, you'll learn a few important terms.
To sum up, this line of code uses the GoToRecord method of the DoCmd object to move to the specified recordin this case, to the new record.
What Else Did the Wizard Create?
Although the DoCmd line we dissected and discussed is what really does the job in the AddRecord_Click procedure, you may be wondering about the rest of the code the Command Button Wizard created. Here's the complete procedure.
Private Sub AddRecord_Click() On Error GoTo Err_AddRecord_Click DoCmd.GoToRecord , , acNewRec Exit_AddRecord_Click: Exit Sub Err_AddRecord_Click: MsgBox Err.Description Resume Exit_AddRecord_Click End Sub
The Private Sub and End Sub lines designate the beginning and end of the procedure. You'll see similar lines at the beginning and end of every event procedurebut because Visual Basic adds them to event procedures automatically, you won't usually have to think about them.
The remaining lines in the procedureother than the DoCmd lineprovide error handling for the procedure. The Command Button Wizard includes these error handling lines so that in case an error occurs, your application won't come to a halt. In Chapter 7, you'll learn how to add your own error-handling code to procedures; for now, rest assured that the wizard took care of it for you.
The Command Button Wizard is a great tool for customizing your applications, but it isn't perfect. Although it creates buttons for a variety of tasks, it can't anticipate everything you'll want your buttons to do. Fortunately, if the wizard doesn't get it quite right, you can modify the buttons it creates to suit your application's needs. That's just what you'll do with the Add Record button in this section.
There's nothing really wrong with the Add Record buttonwhen you click it, you jump to the new record in the recordsetbut it could use some polishing. To get it just right, you'll first change a property and then add a line of Visual Basic code to the button's event procedure.
Add an Access Key for the Command Button
The Add Record button is missing a handy feature that the other buttons on the Contacts form already possess. The other buttons have access keysunderlined characters that let you "press" the buttons using the keyboard. Because many users like to have the option of using the keyboard instead of the mouse, it's always wise to make your applications work as well with the keyboard as with the mouse. For the Add Record button, you'll make the "A" into an access key.
To provide an access key for a button, all you have to do is edit the Caption property of the buttonwhich determines the actual text shown on the buttonto include the ampersand (&) symbol. This symbol in a button's caption tells Access that the next character in the caption is the access key for the button.
In order to make changes to the form, you'll first need to switch back from Visual Basic to Access. Visual Basic provides a toolbar button that brings Access to the front.
Use the Access Key to Press the Command Button
Add a Line to Your Event Procedure to Change the Focus
The Add Record button needs one other modification. Notice that after you press the button, the focus is still on the Add Record buttonthere's no current field or insertion point for typing data. In order to begin entering data, the user must first click the First Name field on the form. Ideally, you'd like the focus to move automatically to the first field in the record. To make it do this, you'll write a single line of Visual Basic code.
Test the Command Button
Editing a Form's Module While in Form View
You may have noticed that you were able to make your addition to the Click event procedure while the Contacts form was in Form viewin a sense, you changed the underlying design of the form without switching to Design view. This is a unique feature of Visual Basic as compared to many programming languages: you can edit a code module "on the fly" while the code in the module is potentially being used by the form. You'll soon discover how useful this can be, because you won't usually have to restart a complex application in order to make minor changes.
As you'll learn in later chapters, you can even edit code in a Visual Basic procedure while it's running. In cases where you make a significant change, Visual Basic may need to reset your code and start over. For now, however, you can appreciate the fact that you don't have to constantly switch between Form and Design views to examine and edit code.
As you continue to polish your application, you'll want to use the SetFocus method whenever you can anticipate what users will want to do next. Moving to a form or control with SetFocus is a great way to save extra clickswhich makes your application easier to use.
Save Your Changes to the Add Record Button
Now that you've finished modifying the button, save your changes.
Making a Form Read-Only by Default
If you type in any of the fields in the Contacts form, you begin editing the recordset. This is one of the great advantages of Access over many other database systems: data is almost always available for both viewing and updating. However, business users are commonly worried about "messing up" the information in a databasetheir data is important, and they don't want it to be too easy to make changes to data.
In this section, you'll modify the Contacts form to provide a solution to this common data-entry request. The idea is to make the default mode for a form read-only so that users can't make changes unless they specifically ask to. This way, users can open the form and look at contact information without worrying about accidentally making a change.
The first step is easy: to make a form read-only, you simply set the AllowEdits property for the form to No. But you also need a way for users to tell you when they do want to make changes. What you want is an additional two buttons on your form: one for indicating that they want to edit data, one for saving the record they've edited. For one of these buttons, you won't use the Command Button Wizard at allinstead, you'll create the button on your own, and then write a custom Visual Basic event procedure to make the button work.
Make Existing Records Read-Only in the Contacts Form
As you can see in the previous illustration, the property sheet displays spaces between words in property names to make them easier to read. When you use properties in Access or Visual Basic, however, you don't type spaces between words. In this procedure, for example, the actual property name is AllowEdits, not Allow Edits as shown in the property sheet.
Now that the form is read-only, you need a command button that sets the AllowEdits property back to Yes. But the Command Button Wizard doesn't offer a button that changes the value of a property. It's time to up the ante and push beyond where the wizard goes!
First, you'll create the button and set its properties. Then, you'll use the Build Event command to create your own event procedure for the button's Click event.
Create the Edit Record Command Button
You want to place the Edit Record button in the form footer along with the existing buttons.
Be sure to include a space in the button's caption, but not in the underlying control name. None of the fields or controls on the Contacts form contain spaces in their names, because spaces make fields and controls more difficult to work with (every time you type the name, you have to enclose it in brackets).
Create the Button's Click Event Procedure
As it stands, the new button won't do anything when you click it. You need to write a Visual Basic event procedure to make it work.
As you enter this line, you'll notice that Visual Basic again helps you out by displaying a list of options under the code you're typingfirst when you type the period, and again when you type the equal sign. In this case, just continue typing the entire command.
The equal sign (=) in Visual Basic code means "assign the value of the expression on the right to the thing identified on the left." The thing on the left here is the expression Me.AllowEdits which refers to the AllowEdits property of the formto refer to a property of the current form in the form's module, you use the Me keyword, followed by a period, followed by the property name. This code tells Visual Basic to assign True to the expression on the left. It's as if the form is telling Visual Basic, "Set my AllowEdits property to True."
Setting a property to True in Visual Basic code is the same as setting the property to Yes in the form's property sheet. (To set a property to No in code, you assign it the value False.)
Try the Edit Record Button
If you typed anything incorrectly when creating your event procedure, clicking the button will most likely cause an error message to appear. If this happens, don't worryVisual Basic displays your code so that you can check what you entered against what is shown above. After making corrections to the procedure, click the Continue button on the toolbar, and then switch back to Access.
Add Comments to Your Event Procedure
When you first look at any Visual Basic procedureeven if you wrote it yourselfit's really tough to figure out what the procedure does and why it's there. To help make your applications easier to understand, it's extremely important to include comments embedded in your code. Comments are like notes to yourself, helping to explain what you were thinking of when you wrote the code.
To add a comment to Visual Basic code, simply precede the text of your comment with an apostrophe (').
Create a Command Button to Save the Current Record
With the two buttons you've created, users can either add new records or edit existing ones. But there's one more button you need to add.
When entering or editing data about a contact, you can save data by either moving to a new record or closing the form. Or, you can use the Save Record command (on the Records menu) to explicitly save the current record. But you want to make this command more accessible to users so that they won't have to find it on the menu. Because the Command Button Wizard creates such a button, you may as well use it.
When you make final adjustments to controls on a form or report, you may find it difficult to line them up just right and make them all the same size. To align or size a group of controls easily, select the controls and then choose one of several Size or Align commands from the Format menu.
Creating Event Procedures for Form Events
Up to this point, you've worked only with procedures for the Click eventcode that runs when you click a button. But there are many other events you can respond to. In this section, you'll work with event procedures for two form events.
When a user clicks the Edit Record button you created, your code makes the form editable. When the user moves to another record or saves the current record, you need to return the form to its read-only state so that records won't be vulnerable to accidental changes until the Edit Record button is clicked again.
Much of the trick in programming with Access is figuring out which event to attach code to. You could have added code to the Save Record button that would set the form's AllowEdits property back to False. But there are other ways the user could save an edited recordusing a menu command, for exampleand your application needs to anticipate all these possibilities. Additionally, the user could move to another record without saving the record at all, in which case you also want to return the AllowEdits property to False.
The two cases you need to catch are:
For the first case, you'll add a line of code to the event procedure for the form's Current event, which occurs whenever Access displays an existing record in a form procedure. (The Database Wizard already created this event procedure.) For the second case, you'll create your own event procedure for the form's AfterUpdate event, which occurs whenever a record is saved in a form. Between the two, you'll be sure to return the form to its read-only state at the appropriate times.
Edit the Procedure for the Form's Current Event
Earlier in this chapter you used the Build Event command on the shortcut menu to create or open the Click event procedure for a button. But the Build Event command always opens the default event for the object you choose. Because the Current event isn't the default event for a form, you'll have to use a more general method to open the event procedureyou'll create the procedure by setting an event property in the property sheet.
Copy the Procedure to the Form's AfterUpdate Event
You need the same code to run when the AfterUpdate event occurs, but the AfterUpdate event doesn't yet have any event procedure. Rather than retype the Form_Current code into Visual Basic, you can just copy the code from one procedure to another.
Displaying a Message to the User
It's important to communicate with users of your application. One way to do this is by using a message box. Using the MsgBox statement, you can give some feedback in response to events in your application.
The same users who worry about accidentally changing data are equally concerned that their changes get registered when they finish editing a record. For these users, you can display a message in response to the AfterUpdate event, confirming that the record was saved.
Add Code That Displays a Message Box
If the syntax that pops up for a function or statement doesn't provide enough information, you can always turn to online Help. To display a Help topic with complete information about any Visual Basic keyword in the Code window, just click the keyword and then press the f1 key.
Save Your Changes to the Contacts Form
Now that you've finished adding all the buttons to the form and editing their event procedures, save your changes to the form.
Try the Buttons with Your Changes
Let's make sure the Edit Record and Save Record buttons work together as you intended. Suppose you found out the mobile phone number of the first contact, and want to enter it in the Contacts form.
Close the Application and Exit Access
Preview of the Next Chapter
Now that you've learned to create and edit simple event procedures, you're ready to discover other ways to customize forms using Visual Basic. In the next chapter, you'll explore ways to help users find and filter data. You'll start with simple code like the code you saw in this chapter, but you'll also write some longer proceduresand learn new elements of Visual Basic along the way.
Microsoft Access 2000 Visual Basic Fundamentals
Last Updated: Friday, July 6, 2001