- Understanding Access's Approach to Application Design
- Creating an Access Application from a Template File
- Touring the Contact Management Application
- Using the Switchboard Manager
- Exploring Form Design View and VBA Class Modules
- Downloading Templates from the Microsoft Office Update Site
- Saving a Form As a Data Access Page
- In the Real World--Putting What You've Learned in Perspective
Touring the Contact Management Application
The Contact Management application appears complex to most first-time Access users. The Database Wizard generates Table, Form, Report, and Module objects in the new database. The following sections explain the purpose of each object in the context of your new Contact Management application.
Table Objects in the Database Window
Tables are the foundation of Access databases. To examine some Table objects generated from the Contact Management template by the Database Wizard, do the following:
Click the Database window's Restore button to open the Database window in Normal mode. An Outlook-style shortcut bar appears at the left of the list.
By default, Access 2002 saves database files in Access 2000 format for backward compatibility. Using Access 2002 format is advantageous only for very large database applications.
Click the Tables shortcut to display a list of the three Create Table... options and the four Table objects in the Contacts database (see Figure 3.11).
Figure 3.11 The Tables page of the Database window displays the Table objects of Contacts.mdb.
Double-click the Contacts item to open the Contacts table in Datasheet view. The fields of the Contacts table correspond to the items in the list of the second Wizard dialog (refer to step 6 in the preceding section). The first field is an AutoNumber field that sequentially numbers the records you add; you can't change the value of an AutoNumber field.
Type a test contact entry in the initially visible fields of the Contacts table. When you type in the First Name cell, a pencil symbol appears to the left of the Contact ID cell, indicating an edit in process, and a new row--called the tentative append record in this book--appears below the test contact record (see Figure 3.12). Click in any cell of the tentative append record to save your entry; you must move to another row in the datasheet to ensure that entries add to the Contacts table.
Figure 3.12 The Datasheet view of the Contacts table displays a test entry.
Minimize the Contacts datasheet, return to the Database window, and double-click to open the Contact Types table. In the Contact Type field, type a typical title, such as Executive. Add records with Director, Manager, and Supervisor as Contact Types. Click in a cell of the first record to save your last entry, and then close the Contact Types window.
Restore the contacts table, and use the scroll bar to display the rightmost fields. Click the Contact Type ID cell of the first record of the table to display a drop-down list button in the grid. Click the button to open a list with items you added to the Contact Types table (see Figure 3.13).
Figure 3.13 The drop-down lookup list of the Contact table's Type ID field displays choices for the field value.
→If you're not comfortable navigating the interface yet, see "Understanding Access's Table Display," p. 124.
The Contact Type ID field of the Contacts table is related to the Contact Type ID field of the Contact Types table, which contains the numeric values 1 (Executive), 2 (Director), 3 (Manager), and any additional records you added in step 5 of the preceding list. Relations between tables are the foundation of relational database management systems (RDBMSs). The relation between the Contacts and Contact Types tables is called a many-to-one relationship because many records in the Contacts table can relate to a single record in the Contact Types table. The Contact Type ID field of the Contacts table, which actually contains numeric value 1 or 2, is called a lookup field because it looks up data in the Contact Types table based on the numeric value and substitutes the corresponding text value for the number. You can change the Contact Type ID from Director to Manager by selecting Manager from the drop-down list.
Additionally, inserting entries from a lookup field is a popular method for avoiding spelling errors and reducing the number of keystrokes for repeated selections.
→For other ways to use this method of data entry, see "Using Lookup Fields in Tables," p. 413.
Scroll to the first field (ContactID) of the table and click the plus (+) sign at the left of the ContactID field to open a subdatasheet that displays an empty Calls datasheet for your test contact entry. (You might need to click the ContactID field to display the plus sign.) Subdatasheets display records of other tables related to the currently open table. Type entries typical of a phone call in the subdatasheet fields (see Figure 3.14). The Calls table has a many-to-one relationship to the Contacts table.
Figure 3.14 The subdatasheet lets you add new records to the related Calls table.
Tools, Relationships or click the toolbar's Relationships
button to display graphically the relations between the Contact Types, Contacts,
and Calls tables in Access's Relationships window. Drag down the bottom
of the Contacts list to expose the ContactTypeID field (see Figure
Figure 3.15 The Relationships window shows how the Contact Types, Contacts, and Calls tables of the Contacts.mdb database relate.
The line between the ContactTypeID fields of the Contact Types and Contacts tables illustrates a many-to-one relationship. (The arrowhead identifies the "one" table in the relation.) The line between the ContactID fields of the Contacts and Calls tables also indicates a many-to-one relationship. (The infinity symbol, x, identifies the "many" side and the 1 represents the "one" side of the relation.) The ContactID line indicates that Access enforces the referential integrity of the relationship.
→For details on how relationships and referential integrity protect your data, see "Maintaining Data Integrity and Accuracy," p. 75.
Close the Relationships and Contacts Table windows, saving your layout changes.
The field names in the Relationships window and the field names that appear in the Datasheet views of the corresponding tables aren't the same. The field names in the Datasheet views are captions, which include spaces for readability. The actual field names, which don't include spaces, appear in the field lists of the Relationships window.
The Switchboard Form
The Main Switchboard is the controlling form of the Contact Management application (refer to Figure 3.10). Switchboard forms take the place of the conventional menu choices of Windows applications. The five buttons on the Main Switchboard perform the following functions:
Enter/View Contacts opens the two-page Contacts form.
Enter/View Contact Types opens a small form for adding additional records to the Contact Types table.
Preview Reports opens the Reports Switchboard page, which lets you preview and print an Alphabetical Contact Listing Report or a Weekly Call Summary Report or return to the Main Switchboard. Preview Reports is equivalent to choosing Print Preview from the File menu.
Change Switchboard Items opens the Switchboard Manager form, which lets you customize the Switchboard pages, add a new page, or delete a page.
Exit This Database closes the Contacts database but doesn't shut down Access.
Figure 3.16 shows the relationship between the buttons on the three versions of the Switchboard and the forms and reports that make up the Contact Management application. For clarity, this diagram omits the Call Details Subform, Call Listing Subform, and Report Date Range form. A subform is a form that's contained within another form; subforms are unique to Access. Lines between forms and tables with arrows on each end indicate the capability to display and edit table data. Lines between reports and tables have only a single arrow, because reports involve only reading table data.
Figure 3.16 This diagram shows the relationship of Switchboard buttons to forms, reports, and tables of the Contact Manager application.
→For help building a new subform, see "Creating a Transaction-Processing Form with the Form Wizard," p. 515.
Figure 3.16 shows three individual Switchboard forms. Contact Manager uses records in the Switchboard Items table to customize a single Switchboard form to perform the three functions shown in the diagram.
Compared to menu commands, switchboards offer more control over the sequence of user interaction with data display and entry forms. The Main Switchboard acts as a home base that you return to on completion of one or more specific tasks. Simplified navigation of multiple layers of forms is the reason that many Access developers use switchboards or their equivalent for complex applications.
The following steps introduce you to Access forms and form-based data entry:
Close all open Access windows except the Switchboard form and the Database window.
If you accidentally closed the Switchboard form, restore the Database window, click the Forms shortcut, and double-click the Switchboard item in the list to open the Main Switchboard.
Click the Enter/View Contacts button to open the Contacts form, which displays in text boxes most of the data you entered in the first record of the Contacts table (see Figure 3.17). The record displayed in the form is called the current record of the table.
Figure 3.17 On opening, the upper part of the Contacts form displays most of the information for the first record of the Contacts table.
Click the Next Record button (with the right-pointing triangle) at the bottom left of the form to display successive records of the Contacts table. If you added only a single Contacts record, the text boxes empty in preparation for adding a new record.
Click the First Record button (with the left-pointing triangle and bar) to return to the first record you typed.
Click the 2 button to show the bottom part of the form, which displays the data for the remaining fields of the Contacts table. You also can navigate to the bottom of the display with the form's scroll bar. Clicking inside the Notes text box adds a scroll bar to that box (see Figure 3.18). Click the 1 button to return to the top of the form.
Figure 3.18 Clicking the 2 button moves to the lower part of the Contacts form, which displays additional contact data.
Press the Tab key 10 times to move the focus to the Work Phone field. (The Tab key is the primary method of navigating through a form's fields.) Click the Dial button to open the AutoDialer form of the Utility.mda library (see Figure 3.19). Clicking the Setup button opens the Windows 98 or Windows NT Modem Properties sheet. Close the Modem Properties sheet, if you opened it, and then click Cancel to close the AutoDialer form.
Figure 3.19 The AutoDialer form is a part of the Ultility.mda library database that's attached when you launch Access.
AutoDialer uses the Windows 98 or Windows 2000/NT built-in Phone Dialer accessory. AutoDialer detects whether the call is within your local dialing area (set by the Dialing Properties sheet that you open from the Modem Properties sheet). The Phone Dialer prepends the required 1 digit to the area code and telephone number when dialing long distance.
Click the Calls button to open the Calls form, which superimposes the Contacts form. The Calls form displays only the records in the Calls table for the contact selected in the Contacts form, a process called synchronizing forms (see Figure 3.20). The Calls form must display multiple records, so the Calls form uses a Datasheet view of the Calls table.
Figure 3.20 The Calls form synchronizes to the active (current) record of the Contacts form.
Add a new Calls record for the Contacts record you entered earlier by clicking the Subject field of the tentative append record and adding some text. When you start typing text, a new tentative append record appears.
Click inside the Call Notes text box and type a short transcript of the call (see Figure 3.21). The Call Notes caption and text box are contained in the Call Details Subform.
Figure 3.21 A new record added to the Calls form.
To delete the record you just added, click the gray record selector button to the left of the Call Date field and press Delete. You receive the warning message shown in Figure 3.22.
Figure 3.22 You receive a warning message before permanently deleting a record.
Office XP no longer installs the Office Assistant by default, so conventional Windows warning messages replace the Office Assistant's intrusive pop-up window.
Close the Calls form to return to the Contacts form.
If you want to add a new contact record to the Contacts form, click the Tentative Append record navigation button (also known as the New Record button) to open a form in which only the Contact ID field is filled in (see Figure 3.23).
Close the Contacts form to return to the Main Switchboard.
Figure 3.23 Click the Tentative Append button to add a new record to the Contacts table.
The Contact ID field uses the AutoNumber field data type, which automatically assigns the next number in sequence to an added record. If you don't enter text in any field and then move the record pointer with the navigation buttons to an active record, the empty record isn't appended.
To delete a record, click the record selector bar at the left of the form and press the Delete key. If you delete a record of the Contacts table that has related records in the Calls table, the related Calls records are deleted simultaneously, a process called cascading deletions. The related Calls records are deleted because referential integrity is enforced between the Calls and Contacts tables, and cascading deletions are specified in the properties of the relation in the Relationships window.
Clicking the Enter/View Other Information button regenerates the Forms Switchboard. To add a new contact type, click the Enter/View Contact Types button to open the simple Contact Types form. Like Contact ID, Contact Type ID is an AutoNumber field. Close the Contact Types form and then click the Return to Main Switchboard button to return the Switchboard form to its original status.
Reports are one of Access's strongest selling points. The capability to program the generation of complex, fully formatted reports sets Access apart from its competitors, including Microsoft's own Visual Basic. To preview and optionally print the two reports of the Contact Management application, follow these steps:
Click the Preview Reports button to generate the Reports Switchboard, and then click Preview the Alphabetical Contact Listing Report to open the small Print Preview window in Normal mode. Fit appears in the toolbar's Zoom list.
Type or select 75% in the Zoom list to view almost all the report (see Figure 3.24).
Figure 3.24 The Print Preview window of the Alphabetical Contact Listing report displays the contacts you enter prior to printing the report.
To print the report, click the toolbar's Print button.
Close the Print Preview window, and click the Restore button of the Switchboard form. Click the Preview the Weekly Call Summary Report button to open the Weekly Call Summary form. The default beginning and ending report dates are for the current system date and the preceding six days.
Edit the date in the Begin(ning) Call Date and the Ending Call Date text boxes to a range of dates for which data is available, if necessary. You receive an error message if there are no Call records within the specified date range.
Click the Weekly Call Summary form's Preview button to open the report in Print Preview mode (see Figure 3.25).
Figure 3.25 The Weekly Call Summary report displays a chronological list of sales calls.
Print a copy of the report if you want, and then close the Print Preview window and click the Return to Main Switchboard button.
Access modules contain VBA subprocedures and functions that are accessible to VBA code contained in the class module of any form or report. You also can call VBA functions in modules by using the RunCode action of an Access macro. Access 2000 was the first version of Access to take advantage of the shared VBA editor used by other Office applications. The chapters in Part VII, "Programming and Converting Access Applications," focus on writing VBA subprocedures and functions to automate your Access applications.
To see and test a simple example of VBA code for a function, follow these steps:
Choose Window, 1 Contacts: Database to open the Database window.
Click the Modules tab and then double-click the Global Code item in the list to open the Global Code module in the VBA code editor. The Global Code module contains a single function, IsLoaded (see Figure 3.26), which returns True if a specified form is open (loaded) or False if not.
Figure 3.26 The Contact Management template includes VBA code for the IsLoaded user-defined VBA function.
To test the IsLoaded function, press Ctrl+G to open the Immediate window. Type ? IsLoaded("Switchboard") in the bottom pane of the Immediate window. The VBA debugger returns 1 (the numeric value of the VBA True intrinsic constant), indicating that the Switchboard form is open (see Figure 3.27).
Figure 3.27 The value returned by executing the IsLoaded function from the Immediate window depends on the value of the strFormName argument you pass and the name of the currently active form.
Close the Immediate window, Alt+Tab over to Access, and minimize the Database window to return to the Main Switchboard.