Access 2000 VBA Handbook

John Wiley & Sons

Chapter One

* Using the Database Wizard

* Automating operations with the Command Button Wizard

* Navigating with hyperlinks

* Creating a navigation control center with the Switchboard Manager

* Controlling the user interface

* Creating custom menus and toolbars

A database is a collection of records and files. To create a database, you need a system that will help you to store, retrieve, and sort your data, as well as analyze and convert it into useful information. If the database is large or complex, you'll probably want to use a commercial computer database application such as Microsoft Access.

Access has a terrific set of tools and wizards to help you create a database, including tables to store data, queries to retrieve and manipulate data, forms to enter and view data, data access pages to view and work with data from the Internet or an intranet, and reports to print information. But if you stop at this point, you'll have taken advantage of only a fraction of the power that Access offers; you'll have used only five of the seven database container objects, having left macros and modules untouched.

Without macros and modules, a database is interactive. In an interactive database, the user initiates each individual action the computer carries out by choosing a menu command or by clicking a toolbar button. The user is the one who supplies the connections between the forms and reports in the database. In order to perform tasks, the user needs to know which menu commands to use and which sequence to use them in, as well as how the forms and reports are related. In an interactive database, the user has complete control. Aknowledgeable user has the power to use the interactive database in productive ways. Aless sophisticated user has the power to corrupt the data and damage the database by selecting the wrong command at the wrong time.

In this book, you'll learn how to transform your interactive database into an automated database application. Awell-designed, fully automated database application can be used by any user. The user doesn't need to know the sequence of steps for a task or the Access commands. The user needs only to click a single button to execute a complicated task.

When you create a fully automated application, you create a custom user interface. The user interface is what users see on the screen and how they use the keyboard and mouse to communicate with the computer. In the custom application's user interface, the user clicks command buttons to move between tasks, perform data-entry operations, find records, and print reports. The custom user interface is where the user lives in your database application. From the user's perspective, the custom user interface is your database application.

When creating the new interface, you should supply the tools to open forms, perform data entry, locate specific records or groups of records, import data, archive old records, and print reports. You should also provide a choice of paths for navigating through your database, making sure that users always know where they are and how to backtrack along the path.

Access provides a set of wizards and helpers to assist you with some of the automation. This chapter introduces you to the Database Wizard for creating the first draft of a complete application, the Command Button Wizard and Combo Box Wizard for creating automated command buttons and combo boxes, and the Switchboard Manager for creating road maps to the forms and reports in the application.

This chapter also shows you how to use hyperlink techniques from Internet technology to navigate between database objects. You'll learn how to use hyperlinks to navigate directly from a form in your application to any document in your computer's file system or in any other computer that is connected to your computer using the Internet's TCP/IP network.

At each stage of user interface construction, your goals are to build in ease of use, intuitive understanding, and protection of the application. This chapter shows you how to create custom menus and toolbars so your application provides only the tools and commands that a user needs. You'll learn how to protect your application with a password and how to set startup conditions so a user who survives the password test is greeted by your application's startup form and its custom menus and toolbars. This chapter ends with a preview of VBA (Visual Basic for Applications), the powerful programming language used in Access, and gives you a glimpse of the additional power you'll have when you learn to use it.

Using the Database Wizard

The Database Wizard can help you to create database applications for a number of different business and personal scenarios. Some of these are as follows:

Asset Tracking Ledger Contact Management Order Entry Event Management Resource Scheduling Expenses Service Call Management Inventory Control Time and Billing

Once you identify the scenario that is closest to the application you want to create, the wizard, in the usual wizard style, displays a series of screens telling you about the application and soliciting your input. After collecting your choices, the wizard uses the template you selected to create and customize the necessary tables, queries, forms, reports, data access pages, and modules.

NOTE Data access pages are a new feature in Access 2000 that allow users to extend database applications to the corporate intranet by creating data-bound HTML pages quickly and easily. This helps users share information faster and more efficiently.

The Database Wizard is able to create both simple and complex databases. Depending on the scenario you choose, the wizard may create several groups of tables. When there are pairs of tables in a many-to-many relationship, the wizard automatically resolves the relationship into a pair of one-to-many relationships by creating a relationship table. The wizard creates simple data-entry forms for each table and may even create a form/subform combination to display a one-to-many relationship. The wizard creates summary reports appropriate to the scenario you choose.

Creating Navigation Paths with Switchboards

After creating the individual data-entry forms and summary reports, the wizard automatically creates forms called switchboards, which provide navigation paths between groups of forms and reports. The wizard creates a Main Switchboard to serve as the control center for the application. The Main Switchboard has command buttons for each of the basic database tasks. Clicking a button on the Main Switchboard takes you to a form that you use to perform a database task, such as entering data into one of the tables. Clicking a button on the Main Switchboard may also take you to another switchboard with buttons that take you to other forms, reports, or other switchboards. Figure 1.1 illustrates switchboard navigation paths.

The buttons react when you click them because the wizard has created an individual set of instructions for each button. The wizard writes instructions and stores them in one of two places:

In standard modules that are listed as separate objects in the Modules pane of the Database window

In form modules and report modules that are built into the forms and reports (as part of the form or report definition), stored as part of the form listed in the Forms pane or the report listed in the Reports pane of the Database window

To observe the Database Wizard at work, we'll create an application for tracking employee expenses.

1. Start up Access 2000 and click the Access Database Wizards, Pages, and Projects radio button (see Figure 1.2a). Click OK, then click the Databases tab in the New dialog and choose Expenses as the template to use to create your new database (see Figure 1.2b).

2. In the next dialog, enter Expenses as the name and save the database to the VBAHandbook folder (see Figure 1.3). (If you haven't created this folder, see the Introduction for instructions on setting it up.) Click the Create button to start the Database Wizard. The wizard's first screen explains the kinds of information the database will manage (see Figure 1.4).

3. The next screen gives you the opportunity to make minor changes in the database (see Figure 1.5). The list box on the left displays the tables to be created. When you click on a table, the list box on the right changes to display the fields for the selected table. You can't add new tables or delete tables from the list, but you can add the fields shown in italics. For each table, check the fields you want to add.

4. Specify styles for the forms (see Figure 1.6a) and reports (see Figure 1.6b) in the next two screens.

5. You can use the next screen to enter a title for the database and include a bitmap picture (see Figure 1.7a). If you add a picture, it will appear on reports that the wizard creates. In the final screen (see Figure 1.7b), you can select to start the database immediately after it is created and to display help. Clicking the Finish button puts the wizard to work. While the wizard toils, a dialog displays one progress meter showing the overall progress and another progress meter showing the progress in creating a specific object.

WARNING If you have not yet configured a printer on your computer, Access will generate an error indicating that it cannot properly create the database. In this instance, just select Start -> Settings -> Printers and double-click Add Printer. This will launch the Add Printer Wizard.

The wizard creates the tables and relationships, a form named Switchboard, and the forms and reports. The last text that flashes above the lower progress meter states that the wizard is setting database properties. When the job is finished, the Main Switchboard is displayed (see Figure 1.8), and the Database window is minimized.

Exploring the Application

The Main Switchboard is the first stop in navigating through the application. (You'll learn how to use the Main Switchboard as a startup form later in this chapter.) The Main Switchboard gives an immediate sense of the main tasks that the application manages. The command buttons direct you to forms for carrying out database tasks or to other switchboards that may branch to still more forms and switchboards. The last two buttons appear on every Main Switchboard that the Database Wizard creates. We'll explore the Change Switchboard Items button later in the chapter. Clicking the Exit this database button closes the database without exiting Access.

Take a few minutes to travel to other forms by clicking buttons. The buttons on the switchboards and forms provide navigational paths through the application to the various tasks. The overall organization emerges as shown in the task flow diagram in Figure 1.9. The directional arrows in the task flow diagram indicate whether a form has a command button that takes you back to a previous form.

The first button on the Main Switchboard takes you to the Expense Reports by Employee form, which has a button that opens the Expense Reports form. Clicking the Preview Report button on the Expense Reports form opens a preview of the Expense Report report. Clicking this sequence of buttons takes you along a one-way path from the Main Switchboard to the Expense Report report (see Figure 1.10). The path is one way because there are no command buttons to take you back to the Main Switchboard. (Of course, you can use a form's default Close button in its upper-right corner or choose the Close command from the File menu to close the form and return to the previous form.) Later, we'll make the navigation back to the previous form easier by adding command buttons to the forms.

Notice that when you select an employee in the Expense Reports by Employee form and click the button on the form, the Expense Reports form opens with information for the same employee; that is, the opened form is synchronized with the form that opened it. If you click into the first form, move to a different employee, and then click back into the second form, you'll see that the two forms remain synchronized. Similarly, when you click the Preview Report button on the Expense Reports form, the report that opens is synchronized with the form that opened it.

The second button on the Main Switchboard takes you to another switchboard called the Forms Switchboard, where you have the choice of displaying the Expense Categories data-entry form or returning to the Main Switchboard (see Figure 1.11).

The third button branches to a third switchboard, called the Reports Switchboard, where you can click either of two summary reports or return to the Main Switchboard. Clicking either of the summary report buttons opens a custom dialog form, where you enter the beginning and ending dates for a report (see Figure 1.12). Clicking the Preview button after entering dates takes you to a preview of a summary report for the specified interval.

The Database Wizard has done more than simply provide navigational paths between forms and reports. The wizard also has built a custom dialog form for collecting input. In this example, the date interval is the criterion for a parameter query that selects the appropriate records for the summary report (a parameter query that gets its information from a form is using a technique called Query By Form, which you'll learn about in Chapter 13).

Examining the Wizard's Work

Let's look behind the scenes to explore how the wizard accomplishes some of its tasks. The Database Wizard uses a number of elementary and advanced techniques, some of which you'll be learning to use in your database applications.

Restore the Database window (by maximizing the Expenses window) and note the following:

There is only one form called Switchboard, yet we have seen three switchboards in the Expenses example: the Main Switchboard, the Forms Switchboard, and the Reports Switchboard. If you open the Switchboard form in Design view, you see a form with eight command buttons and eight blank labels (see Figure 1.13). The Database Wizard uses this form for all of the switchboards. Each switchboard is created on-the-fly as a different version of the same form when you click a command button. Clicking the second button on the Main Switchboard converts the form into the Forms Switchboard, and clicking the third button converts the form into the Reports Switchboard. The wizard has created instructions for converting the form, including changing the caption, displaying the correct number of buttons and labels, and empowering the command buttons displayed by each version of the form to carry out their specific tasks.

TIP To open an object in Design view, select the object in the Objects pane of the Database window by clicking it, then click the Design button.

In addition to the four data tables, there is a Switchboard Items table. One of the fields in this table, the ItemText field, holds the labels for the buttons on the various switchboards. The other fields store information for creating the switchboards and making the buttons work. Note that this table is the record source for the Switchboard form (record sources will be discussed in Chapter 6).

Although the wizard has used the Query By Form technique to select records based on your input in a dialog form, there are no queries listed in the Queries pane. There are, in fact, no queries stored as saved queries in the Expenses application. If you've studied the way the Form Wizard and Report Wizard create their objects, you know that these wizards use SQL statements instead of saved queries as the record sources for the forms and reports. Most of the applications that the Database Wizard can create have no stored queries and use only SQL statements directly for record and row sources.

There is a single Report Date Range form that the wizard uses for both of the custom dialog forms. The wizard has created instructions to change the caption depending on the button you click in the Reports Switchboard.

There is a single standard module named Global Code listed in the Modules pane of the Database window. There are no macros listed in the Macros pane.

(Continues...)

---

Excerpted from Access 2000 VBA Handbook by Susann Novalis Excerpted by permission.
All rights reserved. No part of this excerpt may be reproduced or reprinted without permission in writing from the publisher.
Excerpts are provided by Dial-A-Book Inc. solely for the personal use of visitors to this web site.

---