Home > Articles > Data > Access

This chapter is from the book

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:

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

    NOTE

    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.

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

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

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

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

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

    NOTE

    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.

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

  2. Choose 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 3.15).

    Figure 3.15 The Relationships window shows how the Contact Types, Contacts, and Calls tables of the Contacts.mdb database relate.

    NOTE

    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.

    TIP

    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.

  1. Close the Relationships and Contacts Table windows, saving your layout changes.

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.

NOTE

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.

Access Forms

The following steps introduce you to Access forms and form-based data entry:

  1. Close all open Access windows except the Switchboard form and the Database window.

    NOTE

    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.

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

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

  4. Click the First Record button (with the left-pointing triangle and bar) to return to the first record you typed.

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

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

    NOTE

    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.

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

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

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

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

    NOTE

    Office XP no longer installs the Office Assistant by default, so conventional Windows warning messages replace the Office Assistant's intrusive pop-up window.

  11. Close the Calls form to return to the Contacts form.

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

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

    NOTE

    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.

Access Reports

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:

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

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

  3. To print the report, click the toolbar's Print button.

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

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

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

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

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:

  1. Choose Window, 1 Contacts: Database to open the Database window.

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

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

  4. Close the Immediate window, Alt+Tab over to Access, and minimize the Database window to return to the Main Switchboard.

InformIT Promotional Mailings & Special Offers

I would like to receive exclusive offers and hear about products from InformIT and its family of brands. I can unsubscribe at any time.

Overview


Pearson Education, Inc., 221 River Street, Hoboken, New Jersey 07030, (Pearson) presents this site to provide information about products and services that can be purchased through this site.

This privacy notice provides an overview of our commitment to privacy and describes how we collect, protect, use and share personal information collected through this site. Please note that other Pearson websites and online products and services have their own separate privacy policies.

Collection and Use of Information


To conduct business and deliver products and services, Pearson collects and uses personal information in several ways in connection with this site, including:

Questions and Inquiries

For inquiries and questions, we collect the inquiry or question, together with name, contact details (email address, phone number and mailing address) and any other additional information voluntarily submitted to us through a Contact Us form or an email. We use this information to address the inquiry and respond to the question.

Online Store

For orders and purchases placed through our online store on this site, we collect order details, name, institution name and address (if applicable), email address, phone number, shipping and billing addresses, credit/debit card information, shipping options and any instructions. We use this information to complete transactions, fulfill orders, communicate with individuals placing orders or visiting the online store, and for related purposes.

Surveys

Pearson may offer opportunities to provide feedback or participate in surveys, including surveys evaluating Pearson products, services or sites. Participation is voluntary. Pearson collects information requested in the survey questions and uses the information to evaluate, support, maintain and improve products, services or sites, develop new products and services, conduct educational research and for other purposes specified in the survey.

Contests and Drawings

Occasionally, we may sponsor a contest or drawing. Participation is optional. Pearson collects name, contact information and other information specified on the entry form for the contest or drawing to conduct the contest or drawing. Pearson may collect additional personal information from the winners of a contest or drawing in order to award the prize and for tax reporting purposes, as required by law.

Newsletters

If you have elected to receive email newsletters or promotional mailings and special offers but want to unsubscribe, simply email information@informit.com.

Service Announcements

On rare occasions it is necessary to send out a strictly service related announcement. For instance, if our service is temporarily suspended for maintenance we might send users an email. Generally, users may not opt-out of these communications, though they can deactivate their account information. However, these communications are not promotional in nature.

Customer Service

We communicate with users on a regular basis to provide requested services and in regard to issues relating to their account we reply via email or phone in accordance with the users' wishes when a user submits their information through our Contact Us form.

Other Collection and Use of Information


Application and System Logs

Pearson automatically collects log data to help ensure the delivery, availability and security of this site. Log data may include technical information about how a user or visitor connected to this site, such as browser type, type of computer/device, operating system, internet service provider and IP address. We use this information for support purposes and to monitor the health of the site, identify problems, improve service, detect unauthorized access and fraudulent activity, prevent and respond to security incidents and appropriately scale computing resources.

Web Analytics

Pearson may use third party web trend analytical services, including Google Analytics, to collect visitor information, such as IP addresses, browser types, referring pages, pages visited and time spent on a particular site. While these analytical services collect and report information on an anonymous basis, they may use cookies to gather web trend information. The information gathered may enable Pearson (but not the third party web trend services) to link information with application and system log data. Pearson uses this information for system administration and to identify problems, improve service, detect unauthorized access and fraudulent activity, prevent and respond to security incidents, appropriately scale computing resources and otherwise support and deliver this site and its services.

Cookies and Related Technologies

This site uses cookies and similar technologies to personalize content, measure traffic patterns, control security, track use and access of information on this site, and provide interest-based messages and advertising. Users can manage and block the use of cookies through their browser. Disabling or blocking certain cookies may limit the functionality of this site.

Do Not Track

This site currently does not respond to Do Not Track signals.

Security


Pearson uses appropriate physical, administrative and technical security measures to protect personal information from unauthorized access, use and disclosure.

Children


This site is not directed to children under the age of 13.

Marketing


Pearson may send or direct marketing communications to users, provided that

  • Pearson will not use personal information collected or processed as a K-12 school service provider for the purpose of directed or targeted advertising.
  • Such marketing is consistent with applicable law and Pearson's legal obligations.
  • Pearson will not knowingly direct or send marketing communications to an individual who has expressed a preference not to receive marketing.
  • Where required by applicable law, express or implied consent to marketing exists and has not been withdrawn.

Pearson may provide personal information to a third party service provider on a restricted basis to provide marketing solely on behalf of Pearson or an affiliate or customer for whom Pearson is a service provider. Marketing preferences may be changed at any time.

Correcting/Updating Personal Information


If a user's personally identifiable information changes (such as your postal address or email address), we provide a way to correct or update that user's personal data provided to us. This can be done on the Account page. If a user no longer desires our service and desires to delete his or her account, please contact us at customer-service@informit.com and we will process the deletion of a user's account.

Choice/Opt-out


Users can always make an informed choice as to whether they should proceed with certain services offered by InformIT. If you choose to remove yourself from our mailing list(s) simply visit the following page and uncheck any communication you no longer want to receive: www.informit.com/u.aspx.

Sale of Personal Information


Pearson does not rent or sell personal information in exchange for any payment of money.

While Pearson does not sell personal information, as defined in Nevada law, Nevada residents may email a request for no sale of their personal information to NevadaDesignatedRequest@pearson.com.

Supplemental Privacy Statement for California Residents


California residents should read our Supplemental privacy statement for California residents in conjunction with this Privacy Notice. The Supplemental privacy statement for California residents explains Pearson's commitment to comply with California law and applies to personal information of California residents collected in connection with this site and the Services.

Sharing and Disclosure


Pearson may disclose personal information, as follows:

  • As required by law.
  • With the consent of the individual (or their parent, if the individual is a minor)
  • In response to a subpoena, court order or legal process, to the extent permitted or required by law
  • To protect the security and safety of individuals, data, assets and systems, consistent with applicable law
  • In connection the sale, joint venture or other transfer of some or all of its company or assets, subject to the provisions of this Privacy Notice
  • To investigate or address actual or suspected fraud or other illegal activities
  • To exercise its legal rights, including enforcement of the Terms of Use for this site or another contract
  • To affiliated Pearson companies and other companies and organizations who perform work for Pearson and are obligated to protect the privacy of personal information consistent with this Privacy Notice
  • To a school, organization, company or government agency, where Pearson collects or processes the personal information in a school setting or on behalf of such organization, company or government agency.

Links


This web site contains links to other sites. Please be aware that we are not responsible for the privacy practices of such other sites. We encourage our users to be aware when they leave our site and to read the privacy statements of each and every web site that collects Personal Information. This privacy statement applies solely to information collected by this web site.

Requests and Contact


Please contact us about this Privacy Notice or if you have any requests or questions relating to the privacy of your personal information.

Changes to this Privacy Notice


We may revise this Privacy Notice through an updated posting. We will identify the effective date of the revision in the posting. Often, updates are made to provide greater clarity or to comply with changes in regulatory requirements. If the updates involve material changes to the collection, protection, use or disclosure of Personal Information, Pearson will provide notice of the change through a conspicuous notice on this site or other appropriate way. Continued use of the site after the effective date of a posted revision evidences acceptance. Please contact us if you have questions or concerns about the Privacy Notice or any objection to any revisions.

Last Update: November 17, 2020