Alison Balteramp;#039;s Mastering Microsoft Office Access 1002003 [Electronic resources] نسخه متنی

Tools for Working in the Visual Basic Editor

Effectively using the tips and tricks of the trade, many of which are highlighted in this chapter, can save you hours of time. These tricks help you to navigate around the coding environment, as well as to modify your code quickly and easily. They include the capability to easily zoom to a user-defined procedure, search and replace within modules, get help on VBA functions and commands, and split the Code window so that two procedures can be viewed simultaneously.

The Access 97, Access 2000, Access 2002, and Access 2003 development environments are better than those of their predecessors. Several features have been added to make coding easier and more pleasant for you. These enhancements include the capability to do the following:

• List properties and methods

• List constants

• Get quick information on a command or function

• Get parameter information

• Enable Access to finish a word for you

• Get a definition of a function

All these features that help you with coding are available with a right-click when you place your cursor within the Module window.

List Properties and Methods

With the List Properties and Methods feature, you can view all the objects, properties, and methods available for the current object. To invoke this feature, right-click after the name of the object and select List Properties, Methods (you can also press Ctrl+J). The applicable objects, properties, and methods appear in a list box (see Figure 7.15). To find the appropriate object, property, or method in the list, use one of these methods:

• Begin typing the name of the object, property, or method.

• Use the up-arrow and down-arrow keys to move through the list.

• Scroll through the list and select your choice.

Use one of these methods to insert your selection:

• Double-click the entry.

• Click to select the entry. Then press Tab to insert, or Enter to insert and move to the next line.

TIP

The Auto List Members option, available on the Editor tab of the Options dialog box, causes the List Properties and Methods feature, as well as the List Constants feature, to be invoked automatically each time you type the name of an object or property.

List Constants

The List Constants feature opens a drop-down list displaying valid constants for a property you have typed and for functions with arguments that are constants. It works in a similar manner to the List Properties and Methods feature. To invoke it, right-click after the name of the property or argument (in cases where multiple arguments are available, the previous argument must be delimited with a comma) and select List Constants (or press Ctrl+Shift+J). A list of valid constants appears (see Figure 7.16). You can use any of the methods listed in the previous section to select the constant you want.

Quick Info

The Quick Info feature gives you the full syntax for a function, statement, procedure, method, or variable. To use this feature, right-click after the name of the function, statement, procedure, method, or variable, and then select Quick Info (or press Ctrl+I). A tip appears, showing the valid syntax for the item (see Figure 7.17). As you type each parameter in the item, it's displayed in boldface type until you type the comma that delineates it from the next parameter.

TIP

The Auto Quick Info option, available in the Options dialog box, causes the Quick Info feature to be invoked automatically each time you type the name of an object or property.

Parameter Info

The Parameter Info feature gives you information about the parameters of a function, statement, or method. To use this feature, after the delimiter that denotes the end of the function, statement, or method name, right-click and select Parameter Info (or press Ctrl+Shift+I). A pop-up list appears with information about the parameters of the function or statement. This list doesn't close until you enter all the required parameters, you complete the function without any optional parameters, or you press the Esc key.

NOTE

The Parameter Info feature supplies information about the initial function only. If parameters of a function are themselves functions, you must use Quick Info to find information about the embedded functions.

Complete Word

The Complete Word feature completes a word you're typing. To use this feature, you must first type enough characters for Visual Basic to recognize the word you want. Next, right-click and select Complete Word (or press Ctrl+Spacebar). Visual Basic then finishes the word you're typing.

Definition

The Definition feature shows the place in the Code window where the selected variable or procedure is defined. To get a definition of a variable or procedure, right-click in the name of the variable or procedure of interest, and select Definition (or press Shift+F2). Your cursor is moved to the module and location where the variable or procedure was defined.

As you become more proficient with VBA, you can create libraries of VBA functions and subroutines. When you're viewing a call to a particular subroutine or function, you often want to view the code behind that function. Fortunately, VBA gives you a quick and easy way to navigate from procedure to procedure. Assume that the following code appears in your application:

Private Sub cmdOkay_Click()
Dim intAgeInTen As Integer
If IsNull(Me.txtNameValue) Or IsNull(Me.txtAge.Value) Then
MsgBox "You must fill in name and age"
Exit Sub
Else
MsgBox "Your Name Is: " & Me.txtName.Value & " _
and Your Age Is: " & Nz(Me.txtAge.Value)
Call EvaluateAge(Nz(Me.txtAge.Value))
intAgeInTen = AgePlus10(Fix(Val(Me.txtAge.Value)))
MsgBox "In 10 Years You Will Be " & intAgeInTen
End If
End Sub

If you want to quickly jump to the procedure called EvaluateAge, all you need to do is place your cursor anywhere within the name, EvaluateAge, and then press Shift+F2. This immediately moves you to the EvaluateAge procedure. Ctrl+Shift+F2 takes you back to the routine you came from (in this case, cmdOkay_Click). This procedure works for both functions and subroutines.

TIP

If you prefer, you can right-click the name of the routine you want to jump to and select Definition. To return to the original procedure, right-click again and select Last Position.

NOTE

If the definition is in a referenced library, the Object Browser is invoked, and the definition is displayed.

Mysteries of the Coding Environment Solved

If you're a developer who's new to VBA, you might be confused by the VBE. We will begin by talking about the Code window. The Code window has two combo boxes, shown in Figure 7.18. The combo box on the left lists objects. For a form or report, the list includes all its objects; for a standard module, which has no objects, only (General) appears.

The combo box on the right lists all the event procedures associated with a particular object. Figure 7.19 shows all the event procedures associated with a command button. Notice that the Click event is the only one that appears in bold because it's the only event procedure that has been coded.

The Project Window

The Project window, shown in Figure 7.20, enables you to easily maneuver between the modules behind the objects within your database. The elements of your project are displayed hierarchically in a tree view within the Project window. All elements of the project are divided into Microsoft Access Classes and Modules. All Form, Report, and Class modules are found within the Microsoft Access Classes. All Standard modules are found within Modules. To view the code behind an object, simply double-click the object within the Project window. To view the object, such as a form, single-click the name of the form in the Project window and then click the View Object tool (the second icon from the left on the Project Window toolbar). You are returned to Microsoft Access with the selected object active.

The Properties Window

The Properties window, pictured in Figure 7.21, enables you to view and modify object properties from within the VBE. At the top of the Properties window is a combo box that allows you to select the object whose properties you wish to modify. The objects listed in the combo box include the parent object selected in the Project window (for example, the form) and the objects contained within the parent object (for example, the controls). After an object is selected, its properties can be modified within the list of properties. The properties can be viewed either alphabetically or categorically. In the example, the command button cmdIfThenElse is selected. The properties of the command button are shown by category.

The View Microsoft Access Tool

If at any time you want to return to the Access application environment, simply click the View Microsoft Access icon (the left icon) on the toolbar. You can then return to the VBE using the taskbar, or using one of the methods covered earlier in this chapter.

Find and Replace

Often, you name a variable only to decide later that you want to change the name. VBA comes with an excellent find-and-replace feature to help you with this change. You can simply search for data, or you can search for a value and replace it with some other value. To invoke the Find dialog box, shown in Figure 7.22, choose Edit, Find, or use Ctrl+F.

Type the text you want to find in the Find What text box. Notice that you can search in the Current Procedure, Current Module, Current Project, or Selected Text. The option Find Whole Word Only doesn't find the text if it's part of another piece of text. For example, if you check Find Whole Word Only, and then search for

Count , VBA doesn't find

Counter . Other options include toggles for case sensitivity and pattern matching.

You can also use the Replace dialog box to search for text and replace it with another piece of text (see Figure 7.23). You can invoke this dialog by selecting Edit, Replace from the menu, or by pressing Ctrl+H (or Alt+E, E). It offers all the features of the Find dialog box, but also enables you to enter Replace With text. In addition, you can select Replace or Replace All. Replace asks for confirmation before each replacement, but Replace All replaces text without this prompt. I recommend you take the time to confirm each replacement, because it's all too easy to miscalculate the pervasive effects of a global search-and-replace.

Help

A very useful but under-utilized feature of VBA is the ability to get context-sensitive help while coding. With your cursor placed anywhere in a VBA command or function, press the F1 key to get context-sensitive help on that command or function. Most of the help topics let you view practical examples of the function or command within code. Figure 7.24 shows help on the With…End With construct. Notice that the Help window includes the syntax for the command, a detailed description of each parameter included in the command, and remarks about using the command. At the top of the window, you can see hypertext links to related topics (See Also), as well as a link to an example of using the With…End With construct. If you click on Example, a specific example of the construct appears that you can copy and place into a module (see Figure 7.25). This feature is a great way to learn about the various parts of the VBA language.

Splitting the Code Window

You can split the VBA Code window so that you can look at two routines in the same module at the same time. This option is useful if you're trying to solve a problem involving two procedures or event routines in a large module. To split your Code window, as shown in Figure 7.26, choose Window, Split.

Notice the splitter. Place your mouse cursor on the gray splitter button just above the Code window's vertical scrollbar. By clicking and dragging, you can size each half of the window. The window can be split into only two parts. After you have split it, you can use the Object and Procedure drop-down lists to navigate to the procedure of your choice. The drop-down lists will work for either of the two panes of the split window, depending on which pane was last selected.

NOTE

You can only view routines in the same module in a particular Code window, but several Code windows can be open at the same time. Each time you open an Access, Form, or Report module, Access places you in a different window. You can then size, move, and split each module.

Using Bookmarks to Save Your Place

The Access 2000, Access 2002, and Access 2003 coding environments enable you to create place markers called

bookmarks so that you can easily return to key locations in your modules. To add a bookmark, right-click on the line of code where you will place the bookmark and choose Toggle, Bookmark, or choose Bookmarks, Toggle Bookmark from the Edit menu. You can add as many bookmarks as you like.Chapter 14.