Exploring the Access Basic Programming Environment

In Chapter 1, you learned about the two Access programming environments, macros and Access Basic. If you use macros in your database programs, you are familiar with the easy-to-use Macro window and its interface. Creating and editing a macro are as easy as choosing actions and arguments from drop-down lists. Simple debugging tools let you step through individual actions in your macros, and you can attach macros to events with a few mouse clicks.

In this chapter, you take a look at the Access Basic interface--the Module window. You can access this editor by a number of different routes, depending on whether the function or procedure you are writing or editing is stored in a global, form, or report module. You learn how to examine existing code, create new procedures, compile and run them using the Immediate window, and get a helping hand from the Access Help system.

Understanding the Module Window

When you write or edit Access Basic code, you use the Module window and its editor facilities. If you are familiar with a text editor such as the Windows Notepad application, you will find the Module editor a comfortable place to visit. Both Notepad and the Access Basic editor are text editors rather than full-blown word processors like Windows Write or Word for Windows. The important difference between these two is that a program such as Word for Windows creates documents containing formatted text, including different fonts, font sizes, and text styles such as bold, italics, and underlines. A text editor creates ASCII text files, with no special formatting.

To open the Module window to create or edit a global module, you begin at the Database window. Global modules are database objects like tables, queries, forms, reports, and macros. You click the Module tab to view existing modules, and then double-click a module name (or click once to select it and then click the Design button). If you want to create a new module, click the New button.

Note:This book uses several of the sample applications shipped with Access 2 as sources of example Access Basic code. If you completed a typical Access installation, these files are in the SAMPAPPS subdirectory of your Access directory.

To examine an existing global module, open the NWIND.MDB database from the SAMPAPPS directory, click the Modules tab, and then double-click the Utility Functions module name. The Module window appears, as shown in figure 2.1.

The top level of this and any global module is the Declarations section. After some descriptive text in comment form, you see two statements. The first Option statement is added by default to any new module by the program, whereas the second statement tells Access to ensure that any new variable is explicitly declared. You learn about variable and constant declarations in Chapter 3.

The Object Box

Looking at the toolbar at the top of your screen, you see several active buttons and two listboxes. You use these controls to access the other procedures in the global module. The left listbox is the Object box, which allows you to move among the various objects associated with the form. Because this is a global module and not a form or report module, the box has only the (general) object listed.

The Procedure Box

To the right of the Object box is the Procedure box. Clicking the arrow to the right of the Procedure box reveals a list of the Utility Functions available for the active module--these are the procedures defined for this module. You can click a name to select a particular procedure, or click the Previous and Next Procedure buttons just to the right of the Procedure box on the toolbar to move from one to another.

As with most things in Access (and Windows in general), there are many ways to perform any given action. Besides the toolbar, you can also use the menus or shortcut keys to move between procedures. Select the Next Procedure or Previous Procedure items on View menu, or press Ctrl+Up Arrow or Ctrl+Down Arrow. You will find the shortcut key commands listed next to the menu item names, whereas hot keys are underlined in the menu or menu item name (they are bold in this book).

Another way to move between procedures is the F2 key, which displays the View Procedures dialog box. Try that now and double-click the IsLoaded function procedure in the lower list.

If you do this, the IsLoaded function appears in the Module window. You can apply all the typical text-processing commands with both the mouse and the keyboard; several shortcut key combinations make it easy to find and replace text strings, insert blank lines, delete lines, and indent or unindent single or multiple lines of code.

You can cut, copy, and paste sections of code using the normal Windows mouse or keyboard techniques, in the same procedure or between procedures. To create room to view two procedures in a single module, select the Split Window item from the View menu. The Module window splits into two equal sections, each with its own scroll bars. The top section displays the Declarations procedure; the bottom section displays the procedure you previously had displayed (in this case, the IsLoaded procedure). You can change the procedures displayed in either of the sections by clicking the Procedure box arrow and clicking another function in the drop-down list.

You can toggle the split window off by reselecting the same menu item. Another easy way to open a split window is to locate the split bar, the thin horizontal line at the top of the Module window--above the function code and just below the module title bar. When the pointer moves over this bar, it turns into a double-sided arrow to indicate that it can now drag the line. Doing so allows you to resize the window into two panes. You return to a single window by dragging the split bar back to the top of the window.

Creating and Editing a CBF Procedure

You have seen how to access the Module window for a global module. You can also create procedures local to a form or report by using Access 2's new Code Behind Form (CBF) technology. A form or report module is automatically created when you create a form or report; your event procedures are stored in the form or report rather than in separate module objects as with global modules.

All of the same Module window editing features you read about earlier are available to you with event procedures, and Access gives you several additional tools to speed your event procedure development. The easiest way to open a CBF Module window is by clicking the Code button on the Access toolbar. Figure 2.2 shows the Code button in the Database window toolbar; clicking it first opens the selected form or report in Design view, and then opens its attached Module window in front. If the form or report is already open in Design view, clicking the Code button simply opens the Module window. You can also perform the same task by using View, Code.

Note:As you may notice, there are no event procedures in any of the NWIND.MDB forms or reports. The NWIND.MDB database was created for the original Access 1.x release, which did not support CBF. Consequently, its Access Basic code examples are stored in the Utility Functions global module. There are many example CBF procedures in the two other included sample databases, ORDERS.MDB and SOLUTION.MDB, which are discussed later in this chapter.

Before you can create or edit a CBF procedure, you should add a new form to the NWIND database. This way, if you want to save your work as you go through this book, you don't have to worry about changing any of the forms that shipped in the NWIND database. Of course, you can always reload the sample databases from your Access installation disks.

To create a new form, follow these steps:

Click the Form tab in the Database window; then click the New button; then click Blank Form.
Make sure the Toolbox toolbar is visible and that the Control Wizards tool is deselected.
Click the command button icon in the Toolbox; then click again to place the default-sized command button Button0 on your new Form1 form.
Making sure your new button is selected, select the Properties window (click the Properties button on the Access toolbar, if necessary) and use the drop-down arrow at the top of the Properties window to select the All Properties list item.
Select the Button0 text in the Name property textbox (if it isn't already selected), type OpenForm, and press Enter.
Type Open Form in the Caption property textbox (make sure to leave a space between the two words, unlike the previous Name property setting).
Click the Properties button on the toolbar to toggle the Properties window closed.

You are ready to begin writing your first event procedure.

Build Event and the Code Builder

At this point you are ready to use Access 2's new shortcut menu capability to enter the Module editor for the new form. To do this, follow these steps:

Because you are going to create code for the renamed Open Form command button, right-click the command button to display the shortcut menu.
Choose the Build Event... menu item, which opens the Choose Builder dialog box.
Select the Code Builder option from the list and click OK, or just double-click the Code Builder choice.

At this point the Module window opens, with its title bar reflecting the fact that you entered the Module editor for the Form.Form1 module. Your screen should appear similar to the one shown in figure 2.3.

You may notice something interesting about the code template automatically generated by the Code Builder. Because you right-clicked the command button, the Code Builder created the code template for the OpenForm button's default event procedure, which is surprisingly not the Click event procedure. The Code Builder concatenated (pasted together) the command button name you entered in the Name property textbox with an underscore (_) character and the Click event name. Access event procedures that attach to a form or report are subprocedures rather than functions, so the template is bracketed with the Sub and End Sub statements. When you click the command button at runtime, Access executes the code in the OpenForm_Click event procedure.

Now that you have the beginning and ending of your code, all that is left to do is the actual code that does something. Type the line of code shown as follows:

      DoCmd OpenForm "Orders"

This certainly is a lot smaller than the Command Button Wizard code, which contained error-handling and other statements you learn about in the next few chapters. But that doesn't mean it doesn't do the job, at least for now. One of the great things about the Access Basic programming environment is that you can build your code incrementally, adding more and more to your procedure as you deem it necessary.

Before moving on, test the procedure by leaving Design view and displaying the form in Form view. To do this, follow these steps:

Save the form and module by selecting File, Save (or pressing Ctrl+S or clicking on the Save button on the toolbar).
Close the module.
Click the Form View toolbar button or choose the Form item from the View menu.
Click the Open Form button and watch as the Orders form of the NWIND database is loaded and displayed.

Before you continue, click the Designbutton on the toolbar to return to Design view.

Event Properties and the Build Button

When you save the OpenForm_Click event procedure, Access sets the OnClick property of the Open Form command button object to [Event Procedure]. You can confirm this by closing or minimizing the newly opened Orders form; then selecting the Form1 Module window and viewing its Properties window. Select the command button; then select Event Properties from the Properties window drop-down list to confirm the change to the On Click property textbox, as shown in figure 2.4. If you click anywhere in the textbox or to its left or right, not only does a drop-down arrow appear, but also another button with three periods appears.

You should be familiar with both the drop-down arrow, which reveals a list of available macros in addition to the [Event Procedure] option, and the Build button, which displays the Choose Builder dialog box. You can also right-click the property box and choose the Build menu item. In any case, the event procedure appears in the Module window.

Working with Event Procedures and Functions

Notice that the Object box in the CBF module toolbar now reflects the current OpenForm command button object. Clicking the Object box drop-down arrow reveals the other objects associated with the form. In this case, the form, OpenForm, Detail0, and default (general) objects make up the available choices. The Procedure box drop-down arrow reveals the other events the command button object is capable of triggering. Events that have event procedures have a checkmark next to the event name; you can verify that the OpenForm Click event is active by clicking the drop-down arrow and viewing the list. (An event is active if it has program code assigned to it.)

To view not only the current form objects and events but those of other currently open forms as well as global modules, press the F2 key to display the View Procedures dialog box, as shown in figure 2.5.

You see the two modules in the active database listed in the upper half of the window: the Form.Form1 CBF module and the Utility Functions global module. The lower half displays the procedures associated with the module selected in the upper pane.

Note: If you left the Orders form minimized rather than closing it earlier, you might wonder why that form's CBF module is listed. CBF form modules are automatically created when a form is created.

Remember that functionsstored in global modules are available throughout the database application, whereas procedures stored in CBF modules are accessible only within the parent form or report. If you click the Utility Functions module name in the list, you see the IsLoaded function listed among others in the lower section. Now double-click or select the name and click OK to load the IsLoaded function in its own Module window, as shown in figure 2.6. Although more than one Module window can appear simultaneously, you must use the Split Window item on the View menu to open more than one procedure in a single Module window.

Writing a Clean-Up Procedure

The OpenForm event procedure needs some more work before you can call it finished, but you can continue to develop your Access Basic code in other areas while occasionally revisiting your earlier work. One thing you should always try to do is anticipate the results of your program's actions, making sure your code cleans up after itself. In the case of the OpenForm procedure, you should have a mechanism for closing the Orders form after you are done with it. A logical place to handle this task is when you close your Form1 form.

You can add a new event procedure in many ways, including three not yet covered. They include:

Clicking the New Procedure toolbar button and filling out the New Procedure dialog box.
You click the New Procedure toolbar button (the third button to the right of the Procedures box) or choose the New Procedure menu item from the Edit menu to display the New Procedure dialog box. Notice that the option selection defaults to the Function choice. If you are creating an event procedure, click the Sub option button. Type the new procedure name in the Name textbox and click OK. The new template appears in the current Module window displacing the last displayed code.
Typing the name of the procedure preceded by the Function or Sub keyword in any of three places: in the Declarations section, before you start a procedure, or after you end a procedure.
You can use this technique in any Module window. Simply begin typing on a new line, entering either the Sub or Function keyword followed by the procedure name. When you press Enter, Access creates a new procedure template, replacing the current procedure code in the Module window.
Selecting an event from the Procedure box drop-down list.
The easiest way to create a new event procedure from within the Module editor is to choose the object whose event you want to respond to from the toolbar's Object box drop-down list; then click the appropriate event list item in the Procedure box drop-down list.

To create the procedure to handle closing the Orders form, first make sure you are in the Form.Form1 Module window; then select the Form object from the Object box list; and finally click the Close event item in the Procedure box list. Your Module window shows the Sub Form_Close procedure template. You should see the cursor blinking at the beginning of the blank line in between the two prewritten lines. Type the code shown here and press Enter to move to the next line or Tab to indent where appropriate.

     If IsLoaded("Orders") Then
          DoCmd Close A_FORM, "Orders"
     End If

You may notice that the Access editor has some built-in intelligence it uses in processing your code entries. To illustrate, use your mouse to select the DoCmd and Close commands and retype them in lowercase letters so that the line looks like this:

     docmd close A_FORM, "Orders"

Now watch the line of code as you click the next line of code; the two words revert to their correct capitalization. Access recognizes the two words as part of the Access Basic language and reformats them according to a consistent set of rules. The editor also handles other common mistakes, such as when you enter the End If statement on the last line as one word--endif. Try it and watch the ever-vigilant editor work its magic.

You learn more about conditional execution and the If... Then construction in Chapter 4, but you should be able to guess what this procedure is doing. It uses the IsLoaded function stored in the global utilities module to test whether the Orders form is loaded, and if so, closes it with the Close action. If you want to remind yourself just what the IsLoaded function looks like, simply click to place the cursor anywhere within the IsLoaded function name in the Form_Close code, and then press Shift+F2. Because the Form_Close procedure is in the Form.Form1 CBF module, Access executes the Utility Functions global module in its own window and loads the IsLoaded function in the Module window. This capability makes it easy to navigate quickly to a previously defined function in the same or another global module.

Testing the New Code

You may wonder how the Form_Close event procedure is triggered. You added the OpenForm command button to use in loading the Orders form, but you can use the default capabilities of the Form1 form's Control menu box. When you double-click the Control menu box at runtime, the Form's Close event occurs. To test the interaction of the OpenForm_Click and Form_Close procedures with the IsLoaded function, first make sure the form (Form1) is selected. Next, click the Form View toolbar button and click the Open Form command button on the Form1 form.

Note: If a form is already open, applying the OpenForm action again simply brings that form to the front of the screen and activates it.

Now bring the Form1 form to the front, either by minimizing the Orders form, or by choosing Window, Form:Form1. Click the Form1 Control menu box once to display the control menu and choose the Close menu item, or double-click the Control menu box. Both the Form1 and Orders forms close. Congratulations on some fine programming.

Adding Comments

You just created two event procedures, one of which utilized an existing function to perform some housekeeping chores. The code at this early stage does not contain many of the ingredients that characterize a professional program. There are no declared variables to save on memory, no error-handling to protect against an abrupt halt to your application, and no commentsthat describe the procedures and their statements.

Actually, some descriptive text is already embedded in the IsLoaded function: the name. Some of the actions and commands also have descriptive terminology, as in OpenForm, Close, and DoCmd. Even the If...Then construction is a lot like the way people speak in everyday life. But Access Basic is not an inexact language; you need to know exactly what your procedures and functions are meant to do, both now and down the road. When you write some code, it seems fresh and obvious in your mind. Later, when you use it without a problem for months and want to borrow something for another project, that same code that seemed so crystal clear may now look like Greek to you.

If you forget how something works, at least you might be able to remember what the code was meant to do. Someone who wants to borrow your function and use or adapt it is in worse shape. If you are working with others on a project, it is imperative to document your work. Some of the example code, such as the IsLoaded function, is relatively easy to decipher. But as you dig deeper in to the Access Basic toolchest, you want to take advantage of the sophisticated programming techniques embodied in the extensive sample code provided both in the on-line Help examples and the applications included in the SAMPAPPS directory.

Note:The syntax for writing comments is easy to learn; the style you use with your comments is for you to decide. Whatever system you use, a good rule of thumb is to use one technique; this helps you get in the habit of maintaining your code while organizing your thoughts as you work.

You indicate acomment in one of two ways, either using the Rem reserved word or the single apostrophe (') character. You can use Rem or the single apostrophe character at the beginning of a line to make whatever follows a comment. You also can use the single apostrophe at the end of code on a line to add a comment that applies specifically to that line's statement.

Note:You can also use the Rem statement after some code on a line, but only after a colon (:). This works because the colon character is used to allow multiple statements on a single line. The Rem syntax is maintained for compatibility with older versions of Basic, so you may want to use it sparingly--if at all--in new code.

After you turn a line orpart of a line into a comment, Access ignores the commented text and executes the remaining code. Comments take many forms, from elaborate documentation of the routine to bare-bones notes. The IsLoaded function that follows has a five lines of comments that describe what data you pass to the routine, the purpose of the function, what value the function returns, and the location in the Access User's Guide that uses this code as an example. There is also a specific comment following the Exit Function command that explains how the function exits after finding a form, rather than completing the entire routine. Many programmers use only this last form of comment as a shorthand way of describing code that they feel is largely self-explanatory.

     Function IsLoaded (MyFormName)
     ' Accepts: a form name
     ' Purpose: determines whether a form is loaded.
     ' Returns: True if specified the form is loaded;
     '                 False if the specified form is not loaded.
     ' From: User's Guide Chapter 25
         Dim i
         IsLoaded = False
         For i = 0 To Forms.Count - 1
             If Forms(i).FormName = MyFormName Then
                 IsLoaded = True
                 Exit Function     ' Quit function once form has been found.
             End If
         Next
     End Function

Comments have important uses other than as documentation. If you are building a procedure and have trouble with a line of code, it is often easier to just "comment out" the line with an apostrophe and substitute another, rather than deleting the entire statement and having to rewrite it later if you want to try it or something similar to it again. When you want to use the code again, simply delete the single apostrophe and you are back in business. This "placeholder" strategy is very useful during the debugging process, which is covered in Chapter 8.

You can also leave notes in comments, perhaps as a reminder to write a companion procedure such as the one you wrote to clean up your open form. It is also useful to mention related functions, procedures, and objects; a procedure may not directly interact with another procedure, but the placing of each section of code in the big picture is extremely valuable.

Another form of comment not ordinarily recognized as such is the Tab formatting of your procedures. As your programs become more complex and layered with nested conditional statements, the formatting you apply makes the code more readable and easier to debug. The editor does not indent your code automatically; it is up to you to follow the conventions that indent loops and conditional statements. The IsLoaded function shown previously has an If... Then conditional statement nested inside a For... Next loop. The tabbed indenting makes the procedure's structure clearer at a glance.

Before moving on, take a moment to add a comment or two to your two event procedures. Use the IsLoaded framework if you like or create your own way of doing things. Comments are a lot like backing up your data: a little attention paid early on comes back to you in spades later.

Compiling Code

When you first run a new function or procedure, Access transforms the declarations and statements from source code to object code. Where you can read and understand the English-like Access Basic language, the Access program needs to read and deal with instructions in a form more suitable for the computer than the classroom.

From the moment you begin to enter code in the Module editor, Access is processing these source instructions by interpreting them one line at a time. Interpreted code is therefore an intermediate step between source code and compiled object code, in that it takes time for each line to be examined for syntax and then translated into machine language. If Access detects a syntax error, the compiler stops and displays an error message (which may or may not be helpful in pinpointing the problem).

The Access Module editor offers a way of compiling all your functions and procedures in active modules before you run your application. You choose the Compile Loaded Modules menu item from the Run menu in the Module window; this allows the compiler to discover programming errors at an early stage, rather than during testing when you have to cycle back to Design mode and reenter the editor.

Among other things, Access substitutes binary codes called tokens for Access Basic reserved words to speed program execution. The compiler also performs more robust error checking. If you leave off the End If at the close of an If... Then statement, the compiler produces an error message box that states--Block If without End If" along with a Help button that leads to a Help screen that states the problem in even clearer terms. Another bonus is that Access compiles only new or changed code when you choose Compile Loaded Modules again, so recompiling takes very little time.

Running Your Code: Using the Immediate Window

You saw how Access helps develop your procedures by giving you feedback regarding correct syntax and formatting. You also created a form and command button to trigger the events that launch your event procedures. The Access Basic programming environment provides another way to test the functions and procedures you write--the Immediate window.

The Immediate window is accessible only from the Module window. You can launch it by choosing the Immediate Window menu item of the View menu, or by simply clicking the toolbar button, as shown in figure 2.7.

You use the Immediate window here to launch the IsLoaded function and learn what it returns. According to the comments at the beginning of the function, if the specified form is loaded, the function returns True, otherwise it returns False. True and False are defined as constants in Access Basic, which means that unlike variables, they have a value that does not change. You can use the Immediate window to display those values.

Click in the Immediate window to make sure it is active; then type Print True and press Enter. The Immediate window responds by displaying the value -1. Now type ? True and see the result; you can use the ? in place of Print as a shortcut. Typing ? False produces a value of 0. The Immediate window provides an interactive way of directly evaluating a constant, variable, control, field, property, expression, or procedure and printing the results to the screen or a file.

You will become very familiar with the Immediate window in later chapters as you explore the building blocks of Access Basic. For now, use the Immediate window to observe the return values produced by the IsLoaded function when it runs with different arguments. First, type ? IsLoaded and press Enter. An error message appears informing you that you need to supply the function's parentheses. If you then reissue the command with the requested parentheses, you are treated to another error message indicating that you need to supply arguments.

You can edit the Immediate window text just like a text processor, so rather than retyping the line, simply click after ? IsLoaded and add code so that the line now reads ? IsLoaded ("Orders"). Make sure you do not have the Orders form still loaded and then press Enter. You should see the value 0 displayed on the next line, corresponding to the value of the False constant. To produce a True return, click in the line that calls the function and replace the Orders argument with the name of a loaded form, such as Form1. Now the result is "1, or True when you press Enter again.

Now that you know about the Immediate window, you can use it to get your bearings whenever you are not sure how a built-in function works or just what output a function produces. The Immediate window becomes one of your best friends in Chapter 3, when you examine variables, constants, and operators.

Using the Help System

Speaking of best friends, you can't forget the Access Help system. If you are familiar with the Windows Help technology, you already know how to display and navigate the hypertext-linked help screens. As with the rest of the Access development environment, interactive help is available for the Module window and Access Basic.

You can find Access Basic help from outside the Module window by selecting the Contents menu item from the Help menu in any Access screen. Then click Language and Technical Reference, which brings you to the gateway to Access Basic information. You can choose Programming Topics by Category, including Actions, Error Messages, Events and Event Properties, Functions, Methods, Objects and Collections, Properties, SQL, and Statements. Or you can display a list of all programming topics, as shown in figure 2.8.

This screen is the mother of all Access Basic Help information, offering every single programming element at the click of a mouse button. You can refine the choices by first letter by clicking the appropriate letter button at the top of the screen. The alphabetic letters remain at the top of the screen even as you use the scroll bars to move through the numerous entries. You can return to the list of operator symbols at the top of the screen by clicking the asterisk (*) to the left of the letter A.

As with all Help screens, you can retrace your steps one by one by clicking the Back button, or you can use the History list to view your travels through the language. You also can use the Search button to enter specific topics of interest and then go to one of the available choices. Although the Programming Topics screen interface is quick to use for specific language elements, the Search interface gives you more general overview information about various issues.

Context-Sensitive Help

Access offers context-sensitive help throughout the program, including the Module editor. You can click the Help button tool on the toolbar or press Shift+F1 to change the cursor to a question mark, allowing you to bring up specific help on interface elements you click. Pressing F1 works as it does throughout Access and Windows, with an added capability in the Module window. You simply place the cursor on the statement, built-in function, or other language element you want help with; then press F1 to bring up help on the specific word.

To illustrate, display your OpenForm_Click procedure in the Form.Form1 Module window; then click and place the cursor on the DoCmd statement and press F1. The DoCmd help screen appears, as shown in figure 2.9.

You can now browse a complete definition of the statement's syntax, argument descriptions, and other relevant information. You can click the See Also cross-reference text for related topics, or click the Examples label to display an example screen that shows the DoCmd statement in action.

The example screen opens on top of the current Help screen and allows you to copy and print the sample code and text. You can't copy the example directly from this screen, however. If you click and try to highlight the code, nothing happens. Instead, you must click the Copy button and use the mouse or keyboard to select the lines you need in the Access Help Copy dialog box. Finally, click the Copy button, which copies the highlighted text to the Windows clipboard. Now all you need to do is close or minimize the help screens and paste the code into the Module editor window at the appropriate position in your procedure.

Caution:If you choose to copy code from an example in the Help system, make sure that you copy only the code lines. If you copy other lines (such as those that explain how to use the example), you generate an error when you paste the information in the Module window.

These example code segments are very useful, not just in their original form, but also as templates you can modify to do your own work with less typing. It is a good idea to access context-sensitive help frequently while writing your code, because the examples are well-tested and the information is often more up-to-date than the printed manuals.

Note:Starting with Access 2, the Microsoft Access Language Reference manual is not included with the program and the other manuals. You need to purchase the Access Development Toolkit to obtain the printed version, but you can use the on-line Help system to get all the same information.

Wizards and Cue Cards

Access provides several other interactive tools to help you with your Access Basic programming. Microsoft included its Wizard technology in virtually all of its current productivity applications, offering interactive help in creating tables, queries, forms, and reports. Unlike the main Help system, which you need to search or browse to find answers to your questions, Wizards ask you relevant questions and actually create objects according to your answers. You learned about the Command Button Wizard in Chapter 1, which not only creates the command button but also builds an Access Basic event procedure to execute your action.

You can use the Access Help system to find out more about the various Wizards Access has by using the Search mechanism to look up Wizards: Reference. The Builders, Wizards, and Add-Ins help screen shown in figure 2.10 is a convenient centrally located area from which to look for Access's built-in expert system. Builders are grouped with Wizards because they interact with you to construct various objects, graphics, and expressions. For example, you can access the Expression Builder in many places, including a property sheet, a criteria cell in the QBE grid, the Macro window, and the Module window. Add-ins are also covered because you can use Access Basic to create libraries of procedures and objects that interact with your applications. In fact, Wizards are simply add-ins installed by Microsoft. You learn more about libraries in Chapter 10.

Wizards are a more recent development than the other interactive help tool, Cue Cards. Where Wizards actually do the work for you, Cue Cards is a tutorial tool that teaches you as you follow its suggestions. It is tightly bound with the Help system, offering both instruction and one-click access to relevant on-line help on many cards. Conversely, the Help system's See Also pop-up window often refers you to relevant Cue Cards.

There are no Cue Cards specifically tailored to Access Basic issues, but the Macro Cue Cards are a great help in prototyping your automation strategies. Later, you can convert your macros into procedures that can also include error-handling and other more robust programming capabilities. The intricate hypertexted linking of the Cue Cards and Help systems is a powerful tool for helping you to understand the Access environment.

The Sample Databases

There is one other tremendous resource for help and examples--the sample database applications included with Access; in particular the Solutions database. Most of Access's included manual documentation refers to the NWIND.MDB application, whereas the Orders database includes a number of useful CBF event procedures and a Show Me Help system all of its own. Here, you can learn by example about the AutoExec macro, custom toolbars, and other interface design features that interact with Access Basic procedures and functions.

The Solutions database shown in figure 2.11 is not a complete application, but rather a number of different examples showing you how to perform advanced actions in Access and Access Basic. You use the built-in Show Me help system to explain how to utilize Access 2's enhanced programmable capabilities to manipulate your forms and controls at runtime. You can examine code that finds a record based on a value you select from a list, changes the color of a control based on the value in the control, and creates a WHERE clause using search criteria that the user enters, among many other sample procedures.

The Show Me help tool walks you through each example, offering hypertexted event procedure references that you can click to access the underlying programming while seeing the context in which the code is used. As you progress on your Crash Course journey, the Solutions database offers more and more.

Summary

In this chapter, you explored the Access Basic programming interface, learning about the editing and support tools that you use to write functions and procedures. In particular, the following information was discussed:

Learning what the Module window is
Using the Object and Procedure boxes
Loading a user-defined function in the global module window
Using the split window to load multiple procedures
Working with CBF modules and event procedures
Using Build Event, the Code Builder, and the Event Properties window
Adding comments and compiling and running your code
Navigating through the Access Basic Help system
Exploring the Access interactive tools

Crash Course in Access Basic

Author:

Publisher:
ISBN
Pages:
Price:

Allen L. Wyatt
Steve Gillmor
Que Corporation
1-56529-927-2
224
$19.99