Training
Certifications
Books
Special Offers
Community




 
Microsoft® Access 2000/Visual Basic® for Applications Fundamentals
Author Evan Callahan
Pages 384
Disk 1 Companion CD(s)
Level Beg/Int
Published 05/07/1999
ISBN 9780735605923
ISBN-10 0-7356-0592-0
Price(USD) $39.99
To see this book's discounted price, select a reseller below.
 

More Information

About the Book
Table of Contents
Sample Chapter
Related Series
Related Books
About the Author

Support: Book & CD

Rate this book
Barnes Noble Amazon Quantum Books

 


Chapter 2: Customize an Application with Visual Basic



Chapter Objectives

Estimated Time: 40 minutes

In this chapter you will learn how to:

  • View and understand the Visual Basic code created by the Command Button Wizard

  • Edit code in the Visual Basic window

  • Set form and control properties using Visual Basic code

  • Display a message box

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 so—I'll get off at the second building on the right."

We all love to be in control of our situation—a 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 control—telling the application how to work, rather than following the rules built into Access.

Start the Lesson

  • Start Access and open the Ch02 Contacts database in the practice files folder.

Customizing a Command Button

In the first chapter, you created a button using the Command Button Wizard, and the button worked just as planned—it 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

  • On the main Switchboard form, click Enter/View Contacts.

    The Contacts form displays the first record in the recordset.

    Click to view graphic
    Click to view graphic

Open an Event Procedure in Visual Basic

Next, you'll take a look at the event procedure that makes the Add Record button work.

  1. Click the Design View button.

    Now you'll tell Access to open the button's event procedure in Visual Basic.

  2. Scroll down in the form to display the form footer.

  3. With the right mouse button, click the Add Record button.

    Click to view graphic
    Click to view graphic

  4. Click Build Event.

    The Build Event command tells Access to open the Visual Basic window and display the default event procedure for the selected object. For a command button, the default event procedure is the Click event procedure, so the Visual Basic code window shows the AddRecord_Click procedure.

    Click to view graphic
    Click to view graphic

    Take a look around the Visual Basic window—browse the menus and hover over the toolbar buttons to view their ScreenTips. This is where you'll be spending a lot of your time as you learn more about programming with Access.

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.

Click to view graphic
Click to view graphic

Each form and report in a database has its own attached form module or report module for storing Visual Basic code—for 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.


NOTE
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 task—for 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.

  • Click the Procedure View button (at the bottom left corner of the Code window).

    Click to view graphic
    Click to view graphic

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.

  • Each word that Visual Basic recognizes as part of its programming language is called a keyword. There's a keyword for every statement, function, method, object, and property you use to program Access.

  • The first keyword in the line is DoCmd (read as "DO-command"), which you'll be seeing quite a bit when programming Access. DoCmd is an object—it shares this honorable position with other objects you're already familiar with, such as forms, reports, and controls. You use the DoCmd object to perform common actions in the Access interface, such as opening a form, printing a report, or choosing a menu command.

  • The second keyword is GoToRecord, which is a method of the DoCmd object. Each object that Access recognizes has its own set of methods that you use with that object. The methods of the DoCmd object are all the actions it allows you to perform—in fact, if you've created macros in Access, you'll recognize the things you can do with the DoCmd object as the macro actions available in the Macro window. The GoToRecord method, as its name suggests, tells Access to move to a specified record in the current recordset.

  • To perform a method on an object, such as the DoCmd object, you type the object immediately followed by its method, separating the keywords with a period.

  • What follows the GoToRecord method are its arguments, which provide any information that's necessary to carry out the method. The arguments for the GoToRecord method, for example, allow you to specify such critical options as which record you want to go to. You specify arguments by typing a space after the method, and then typing the argument values separated by commas. If you don't need to specify one or more of the argument values, you can leave them out by simply typing a comma for each—for example, the line above skips the first two arguments, supplying only the third argument.

  • Some methods, such as GoToRecord, have specially-defined constant values that you can enter as arguments. The argument acNewRec is a constant that tells the GoToRecord method to move to the new record at the end of the recordset (other options include moving to the next record or the first record in the recordset). The constant acNewRec actually stands for the number 5—but the constant name is much easier to remember and makes your code easier to understand.

To sum up, this line of code uses the GoToRecord method of the DoCmd object to move to the specified record—in 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 procedure—but because Visual Basic adds them to event procedures automatically, you won't usually have to think about them.

The remaining lines in the procedure—other than the DoCmd line—provide 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.


Modifying a Command Button Created Using the Wizard

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 button—when you click it, you jump to the new record in the recordset—but 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 keys—underlined 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 button—which determines the actual text shown on the button—to 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.

  1. Click the View Microsoft Access button on the toolbar.

    The Contacts form, still in Design view, comes to the front. Note that you don't have to close the form module in the Visual Basic window to continue working on the form in Access—Visual Basic stays open in the background, and you can switch back to it whenever you like.

  2. With the right mouse button, click the Add Record button, and then click Properties on the shortcut menu.

    Access displays the property sheet for the Add Record button.

  3. Click the All tab in the property sheet.

  4. In the Caption property box, click the left edge of the Add Record text (before the "A"), and then type &.

    Adding the ampersand (&) symbol tells the wizard to make the "A" in Add Record the access key for the button. This way, the user can press alt+a to achieve the same effect as clicking the Add Record button. The button won't actually show the ampersand, but the "A" will appear underlined on the button.

    Click to view graphic
    Click to view graphic

  5. Close the property sheet.

Use the Access Key to Press the Command Button

  1. Click the Form View button on the toolbar.

  2. Press alt+a.

    Access moves to the new record in the Contacts table just as if you'd clicked the button using the mouse.

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 button—there'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.

Click to view graphic
Click to view graphic

  1. Switch back to Visual Basic (on the Windows taskbar, click the Microsoft Visual Basic - Ch02 Contacts button).

    Visual Basic still displays the AddRecord_Click event procedure.

  2. Click the blank line underneath the DoCmd line in the event procedure, press tab, and then type FirstName.

    FirstName is the name of a control object on your form. (Be sure not to insert a space into the name FirstName—the underlying control name doesn't contain a space.) Next, you'll specify a method that you want to use with the FirstName object: the SetFocus method. As you saw earlier in the chapter, you separate a method from its object with a period.

  3. Type a period, and then type Set.

    When you type a period after an object name, Visual Basic assumes that you want to follow the period with a method or property of that object. To help you enter a valid method or property, it displays a list underneath the line you're typing. As you continue typing, the list automatically scrolls down to display entries that begin with the characters you type.

    Click to view graphic
    Click to view graphic

    Typing Set was enough to move to the SetFocus method in the list. Although you could continue typing code or double-click any method or property in the list, pressing spacebar is the easiest way to enter the selected item in the list.

  4. Press spacebar, and then press enter.

    Now the line of Visual Basic code is complete:

    FirstName.SetFocus
    

    This line tells Access to set the focus to the specified form or control object—in this case, the FirstName control.

    Here's the procedure with the new line of code.

    Click to view graphic
    Click to view graphic

Test the Command Button

  1. Switch to Access (click the View Microsoft Access button on the toolbar).

  2. Click the Add Record button.

    Access moves to the new record in the Contacts table. This time, however, you'll notice that the insertion point is flashing in the FirstName field, ready for the user to enter data.


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 view—in 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 clicks—which 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.

  • Click the Save button on the toolbar.

    Access saves your changes to the form and its form module.

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 database—their 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 all—instead, you'll create the button on your own, and then write a custom Visual Basic event procedure to make the button work.

Click to view graphic
Click to view graphic

Make Existing Records Read-Only in the Contacts Form

  1. Click the Design View button on the toolbar.

  2. Double-click the form selection box at the upper left corner of the form window (at the intersection of the rulers).

    Click to view graphic
    Click to view graphic

    Access displays the form's properties in the property sheet.

  3. Click the AllowEdits property, and then set the property to No.

    Click to view graphic
    Click to view graphic

    That's all it takes to solve the problem of accidentally editing data; now when you open the form to an existing record, you won't be able to change data. Note that we're leaving the other two "Allow" properties—AllowDeletions and AllowAdditions—set to Yes so that users can still delete or add records. Accidental deletions shouldn't be a problem, because Access warns users automatically before deleting records.

  4. Close the property sheet.

  5. Click the Form View button on the toolbar.

  6. Try to type a few letters in the First Name field.

    Nothing happens, because the record is read-only—so the existing data is safe.


NOTE
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.

Creating a Command Button Without a Wizard

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.

  1. Click the Design View button on the toolbar.

  2. Scroll down in the form to display the form footer.

  3. If the toolbox isn't displayed, click the Toolbox button on the toolbar.

  4. In the toolbox, click the Control Wizards tool to deselect it, and then click the Command Button tool.

    Deselecting the Control Wizard tool tells Access that you don't want to use a wizard to create this control—you'll set its properties on your own.

  5. In the form footer, click just to the right of the Add Record button.

    Click to view graphic
    Click to view graphic

    Without the Control Wizards tool selected, the button appears immediately, but with a default name (such as Command47) and no associated event procedure.

  6. Click the Properties button on the toolbar.

  7. In the Name property box, type EditRecord.

  8. Press ENTER, and then in the Caption property box type &Edit Record.

    The ampersand (&) character in the button's caption tells Access to make the "E" in Edit Record the access key for the command button.


NOTE
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).

Click to view graphic
Click to view graphic

  1. Close the property sheet.

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.

  1. With the right mouse button, click the Edit Record button, and then click Build Event on the shortcut menu.

    Access displays the Choose Builder dialog box. (If you wanted to create an expression or a macro rather than Visual Basic code, you could choose Expression Builder or Macro Builder.)

  2. Select Code Builder, and then click OK.

    Access tells Visual Basic to open the form module for the Contacts form and create an event procedure for the button's Click event.

    Click to view graphic
    Click to view graphic

    The procedure that Visual Basic creates is called EditRecord_Click. The name of an event procedure has two parts, separated by an underscore character: the name of an object, and the name of the event that the procedure responds to. You can think of the name EditRecord_Click as meaning "the code that runs when you click the EditRecord control."

  3. Press ENTER, press TAB, and then type the following line of Visual Basic code.

    Me.AllowEdits = True
    


NOTE
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 typing—first 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 form—to 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.)

  1. Press ENTER, and then type the following line so that the focus will move back to the first control on the form—just as in the AddRecord_Click event procedure.

    FirstName.SetFocus
    

    Press enter. Your event procedure should look like this:

    Click to view graphic
    Click to view graphic

Try the Edit Record Button

  1. Switch to Access.

    The Contacts form is still in Design view. To test the code, you'll switch to Form view.

  2. Click the Form View button.

  3. Click the Dear field, and try to type in it.

    You can't type anything—the record is still read-only.

  4. Click the Edit Record button you just created.

    Although it happened so fast you couldn't have seen it, Access ran your event procedure when you clicked the button. The AllowEdits property should now be set to Yes. Also, you'll notice that the focus is on the First Name field as you specified using the SetFocus method in the event procedure. But you want to edit the Dear field to add this contact's nickname to the record.

  5. Click the Dear field, and then type Red.

    You can now edit data, which means that the button's event procedure successfully changed the AllowEdits property to True.


NOTE
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 worry—Visual 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 procedure—even if you wrote it yourself—it'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 (').

  1. Switch to Visual Basic.

  2. Click the blank line underneath the Private Sub statement in the Code window, and then type the following line.

    ' Make the Contacts form editable.
    

    Now when you look at this event procedure later on, you won't need to figure out what it does or why you wrote it.

  3. Press ENTER. You'll notice that Visual Basic displays the comment text in green.

  4. Click the far right side of the line that includes the SetFocus method, press TAB twice, and then type the following text.

    ' Move to the FirstName field.
    

    As you can see, a comment can either begin on a new line or explain the line of code to the left. A comment on a line by itself often gives information about several lines of code or a whole procedure, while a comment to the right of a line of code generally explains what that one line does.

    Click to view graphic
    Click to view graphic

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.

  1. Switch to Access.

  2. Click the Design View button on the toolbar.

  3. Scroll down in the form to display the form footer.

  4. In the toolbox, click the Control Wizards tool to select it, and then click the Command Button tool.

  5. In the form footer, click just to the right of the Edit Record button.

    Click to view graphic
    Click to view graphic

    The Command Button Wizard starts, asking what actions you want the new button to perform.

  6. In the Categories list, select Record Operations.

  7. In the Actions list, select Save Record, and then click Next.

    The wizard asks whether you want a picture or text on your button.

  8. Select the Text option.

  9. In the Text box, click the left edge of the Save Record text, and then type &.

    Adding the ampersand (&) symbol tells the wizard to make the "S" in Save Record the access key for the button.

  10. Click Next.

  11. Type SaveRecord as the button name, and then click Finish.

    The wizard finishes creating the button and its event procedure and places it on your form.

  12. If necessary, resize and align the buttons in the footer so that they're uniform. (When you create buttons by different methods, it's easy for them to end up with different shapes or sizes.)


TIP
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.

    Click to view graphic
    Click to view graphic

  1. Click the Save button on the toolbar.

    Access saves both the form and its module.

Creating Event Procedures for Form Events

Up to this point, you've worked only with procedures for the Click event—code 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 record—using a menu command, for example—and 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:

  • Whenever the user moves to another existing record

  • Whenever the user saves the current record using any method

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 procedure—you'll create the procedure by setting an event property in the property sheet.

  1. Double-click the form selection box at the upper left corner of the form window (at the intersection of the rulers).

    Access displays the form's properties in the property sheet.

  2. Click the Event tab in the property sheet.

  3. Click the OnCurrent property, and then click the Build button to the right of the property box.

    Click to view graphic
    Click to view graphic

  4. Select Code Builder, and then click OK.

    Access tells Visual Basic to create the Form_Current event procedure and display it in a Code window.

    Click to view graphic
    Click to view graphic

  5. Press ENTER and then tab, and then type the following line of Visual Basic code.

    Me.AllowEdits = False   ` Return the form to its read-only state.
    

    By setting the form's AllowEdits property to False, this line of code returns the form to its read-only state each time you move to an existing record in the form.

  6. Press ENTER.

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.

  1. In the Code window, select the line of code you added (click to the left of the line of code—but to the right of the gray margin—so that the whole line is highlighted).

  2. On the Edit menu, click Copy.

  3. Switch to Access.

  4. Click the AfterUpdate property, and then click the Build button to the right of the property box.

  5. Select Code Builder, and then click OK.

    Visual Basic displays the Form_AfterUpdate event procedure.

  6. Press ENTER.

  7. On the Edit menu, click Paste.

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

  1. Press TAB, type MsgBox, and then press spacebar.

    A box appears underneath the line you're typing, displaying the names of the arguments available with MsgBox. This is another way that Visual Basic helps you as you enter code—just when you need to know the syntax for a Visual Basic statement or function, there it is.

    Click to view graphic
    Click to view graphic

  2. Type "Record Saved." and then press enter.

    This completes the MsgBox line, passing as an argument the message that you want to display to the user. Because it's a string argument, you enclose the message in quotation marks. (In Visual Basic, any text data or combination of text and numbers is referred to as a string.)

    Here's the complete code for the Form_AfterUpdate event procedure.

    Click to view graphic
    Click to view graphic


TIP
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.

  1. Close Visual Basic.

    Access returns to the front.

  2. Close the property sheet.

  3. Click the Save button on the toolbar.

    Access saves your changes to the form and its form module.

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.

  1. Click the Form View button on the toolbar.

  2. Click the Edit Record button.

  3. Click the Mobile Phone field, and then type 206 555 2365.

    When you change data in a record and haven't yet saved the new in-formation, the record selector (at the left side of the form) displays a pencil icon.

    Click to view graphic
    Click to view graphic

  4. Click the Save Record button.

    Access saves the record in the Contacts table and changes the pencil back to the current record indicator. Having saved the record, it fires the form's AfterUpdate event, which in turn runs your event procedure and displays your message.

    Click to view graphic
    Click to view graphic

  5. Click OK.

  6. Click the First Name field, and then try to type a few letters.

    Because the form's AfterUpdate event procedure set the AllowEdits property back to False, you can't edit data any longer. Everything's working as planned!

  7. Close the Contacts form.

Close the Application and Exit Access

  1. On the Switchboard form, click Exit This Database.

  2. On the File menu, click Exit.

Chapter Summary

To Do this
Open or create the default event procedure for a control In Design view, with the right mouse button click the control, and then click Build Event.
Open or create any event procedure for a control In the property sheet for the control, click the event property for the event, and then click the Build button.
Add an access key for a command button In the button's Caption property setting, precede the access key with an ampersand (&).
Use a method of an object in code Type the name of the object followed immediately by a period (.) and the name of the method.
Set a property in code Type the name of the property followed by the equal sign (=) and then the value you want to assign to the property.
Add a comment to a procedure Type an apostrophe (') followed by your comment text.
Let Visual Basic enter a keyword for you When a list of property names, method names, or values appears under the line you're typing, double-click the item you want. Or, type enough letters to move to the item you want, and then press spacebar.

For online information about Click the Help button, and then ask for Help on
Editing code in modules vb editor
Setting form and control properties property sheet
Assigning values in Visual Basic set control values

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 procedures—and learn new elements of Visual Basic along the way.


Visit Microsoft Press for more information on
Microsoft Access 2000 Visual Basic Fundamentals

Top of Page


Last Updated: Friday, July 6, 2001