Access 2003 bible

Access 2003 Bible

Access 2003 Bible

Cary N. Prague, Michael R. Irwin, and Jennifer Reardon

Access 2003 Bible

Published by

Wiley Publishing, Inc.

111 River Street

Hoboken NJ 07030

www.wiley.com

Copyright © 2004 by Wiley Publishing, Inc., Indianapolis, Indiana

ISBN: 0-7645-3986-8

Manufactured in the United States of America

10 9 8 7 6 5 4 3 2 1

1B/RX/QZ/QT/IN

Published by Wiley Publishing, Inc., Indianapolis, Indiana

Published simultaneously in Canada

No part of this publication may be reproduced, stored in a retrieval system or transmitted in any form or by any means,

electronic, mechanical, photocopying, recording, scanning or otherwise, except as permitted under Sections 107 or 108 of

the 1976 United States Copyright Act, without either the prior written permission of the Publisher, or authorization through

payment of the appropriate per-copy fee to the Copyright Clearance Center, 222 Rosewood Drive, Danvers, MA 01923, (978)

750-8400, fax (978) 646-8700. Requests to the Publisher for permission should be addressed to the Legal Department, Wiley

Publishing, Inc., 10475 Crosspoint Blvd., Indianapolis, IN 46256, (317) 572-3447, fax (317) 572-4447, E-mail:

[email protected].

is a trademark of Wiley Publishing, Inc.

LIMIT OF LIABILITY/DISCLAIMER OF WARRANTY: WHILE THE PUBLISHER AND AUTHOR HAVE USED THEIR BEST

EFFORTS IN PREPARING THIS BOOK, THEY MAKE NO REPRESENTATIONS OR WARRANTIES WITH RESPECT TO THE

ACCURACY OR COMPLETENESS OF THE CONTENTS OF THIS BOOK AND SPECIFICALLY DISCLAIM ANY IMPLIED

WARRANTIES OF MERCHANTABILITY OR FITNESS FOR A PARTICULAR PURPOSE. NO WARRANTY MAY BE CREATED

OR EXTENDED BY SALES REPRESENTATIVES OR WRITTEN SALES MATERIALS. THE ADVICE AND STRATEGIES

CONTAINED HEREIN MAY NOT BE SUITABLE FOR YOUR SITUATION. YOU SHOULD CONSULT WITH A

PROFESSIONAL WHERE APPROPRIATE. NEITHER THE PUBLISHER NOR AUTHOR SHALL BE LIABLE FOR ANY LOSS

OF PROFIT OR ANY OTHER COMMERCIAL DAMAGES, INCLUDING BUT NOT LIMITED TO SPECIAL, INCIDENTAL,

CONSEQUENTIAL, OR OTHER DAMAGES.

For general information on our other products and services or to obtain technical support, please contact our Customer

Care Department within the U.S. at 800-762-2974, outside the U.S. at 317-572-3993 or fax 317-572-4002.

Wiley also publishes its books in a variety of electronic formats. Some content that appears in print may not be available in

electronic books.

Library of Congress Control Number: 2003101923

Trademarks: Wiley and related trade dress are registered trademarks of John Wiley & Sons, Inc., and/or it affiliates, in the

United States and other countries, and may not be used without written permission. All other trademarks are the property

of their respective owners. Wiley Publishing, Inc., is not associated with any product or vendor mentioned in this book.

About the Author

Cary N. Prague is an internationally best-selling author and lecturer in the database

industry. He owns Database Creations, Inc., the world’s largest Microsoft Access

add-on company. Their products include a line of financial software; Business! for

Microsoft Office, a mid-range accounting system, POSitively Business! Point of Sale

software, the Inventory Barcode manager for mobile data collection, and the Check

Writer and General Ledger. Database Creations also produces a line of developer

tools including the appBuilder, an application generator for Microsoft Access, the

EZ Access Developer Tools for building great user interfaces, appWatcher for main￾taining code bases among several developers, and Surgical Strike, the only Patch

Manager for Microsoft Access.

Cary also owns Database Creations Consulting, LLC., a successful consulting firm

specializing in Microsoft Access and SQL Server applications. Local and national

clients include many Fortune 100 companies including manufacturers, defense con￾tractors, insurance, health-care, and software industry companies. His client list

includes Microsoft, United Technologies, ABB, Smith & Wesson Firearms, Pratt and

Whitney Aircraft, ProHealth, OfficeMax, Continental Airlines, and other Fortune 500

companies.

Formerly, he has held numerous management positions in corporate information

systems, including Director of Managed Care Reporting for MetraHealth, Director of

Corporate Finance and Software Productivity at Travelers Insurance where he was

responsible for software support for 35,000 end users, and Manager of Information

Systems support for Northeast Utilities.

He is one of the top best-selling authors in the computer database management

market, having written over 40 books that have sold over one million copies on

software including Microsoft Access, Borland (Ashton-Tate) dBASE, Paradox,

R:Base, Framework, and graphics. Cary’s books include 11 books in the Access Bible

series (recently number one on the Ingram Bestselling Database Titles list and in

the Amazon.com top 100), Access 97 Secrets, dBASE for Windows Handbook, dBASE

IV Programming (winner of the Computer Press Association’s Book of the Year

award for Best Software Specific Book), and Everyman’s Database Primer Featuring

dBASE IV. He recently completed several books for Access 2003 including Weekend

Crash Course in Office Access 2003 Programming. Cary recently sold a product line

named eTools for Microsoft Access to MightyWords, a division of FatBrain.com and

Barnes and Noble.

Cary is certified in Access as a Microsoft Certified Professional and has passed the

MOUS test in Access and Word. He is a frequent speaker at seminars and confer￾ences around the country. He is on the exclusive Microsoft Access Insider Advisory

Board and makes frequent trips to Microsoft headquarters in Redmond, WA. He has

been voted the best speaker by the attendees of several national conferences.

Recently, he was a speaker for Microsoft sponsored conferences in New Orleans,

Hawaii, Phoenix, Chicago, Toronto, Palm Springs, Boston, and Orlando. He has also

spoken at Borland’s Database Conference, Digital Consulting’s Database World,

Microsoft’s Developer Days, Computerland’s Technomics Conference, COMDEX,

and COMPAQ Computer’s Innovate. He was a contributing editor to Access Advisor

magazine and has written for the Microsoft Office Developer’s journal.

He is active in local town politics serving on the South Windsor, Connecticut Board

of Education, Parks and Recreation Commission, and the Board of Assessment

Appeals.

Cary holds a master’s degree in computer science from Rensselaer Polytechnic

Institute, and an M.B.A and Bachelor of Accounting from the University of

Connecticut. He is also a Certified Data Processor.

Michael R. Irwin is considered one of the leading authorities on automated database

and Internet management systems today. He is a noted worldwide lecturer, a winner

of national and international awards, best-selling author, and developer of

client/server, Internet, Intranet, and PC-based database management systems.

Michael has extensive database knowledge, gained by working with the Metropolitan

Police Department in Washington, D.C. as a developer and analyst for the Information

Systems Division for over 20 years and assorted Federal Agencies of the United States

Government. Since retiring in June 1992, he runs his own consulting firm, named The

Irwin Group, and is principal partner in the company - IT in Asia, LLC, specializing in

Internet database integration and emphasizing Client/Server and net solutions. With

consulting offices in Cincinnati, Ohio, Bangkok, Thailand, and Manila, Philippines, his

companies offer training and development of Internet and database applications.

His company has the distinction of being one of the first Microsoft Solution’s

Providers (in 1992). His local, national, and international clients include many soft￾ware companies, manufacturers, government agencies, and international companies.

His range of expertise includes database processing and integration between main￾frame, minicomputer, and PC-based database systems, as well as B-2-B and B-2-C

integration between back-end databases; he is a leading authority on PC-based

databases.

He is one of the top best-selling authors in the computer database management mar￾ket, having authored numerous database books, with several of them consistently

on the best-sellers lists. His books, combined, have sold nearly a million copies

worldwide. His most recent works include The OOPs Primer (Borland Press, dBASE

5.5 for Windows Programming (Prentice Hall), Microsoft Access 2002 Bible, Microsoft

Access 2002 Bible Gold Edition (co-authored), and Working with the Internet. The

Access Bible series have constantly been number one on the Ingram Best-selling

Database Titles list and is consistently in the Amazon.com and Buy.com top 10. He

has also written several books on customs and cultures of the countries of Asia

(including China, Japan, Thailand, and India). Two of his books have won interna￾tional acclaim. His books are published in over 24 languages worldwide. He has been

a contributing editor and author to many well-known magazines and journals.

He is a frequent speaker at seminars and conferences around the world and has

been voted the best speaker by the attendees of several international conferences.

Michael has developed and markets several add-on software products for the

Internet and productivity related applications. Many of his productivity applications

can be obtained from several of his Internet sites or on many common download

sites. Many of his application and systems are distributed as freeware and careware.

He has also developed and distributes several development tools and add-ins for a

wide range of developer applications.

Jennifer Reardon is considered a leading developer of custom database applica￾tions. She has over ten years’ experience developing client/server and PC-based

applications. She has accumulated much of her application development experi￾ence working as lead developer for Database Creations. She has partnered with

Cary Prague developing applications for many Fortune 500 companies.

Her most significant projects include a spare parts inventory control system for

Pratt & Whitney’s F22 program, an engineering specifications system for ABB￾Combustion Engineering, and an emergency event tracking system for the State of

Connecticut. She was also the lead developer of many of the Database Creations

add-on software products including Business, Yes! I Can Run My Business, Check

Writer, and the User Interface Construction Kit.

She has co-authored Access 2003 Bible, Access 2002 Bible, and Access 2000 Weekend

Crash Course. She has also written chapters in other books on subjects including

Data Access Pages, the Microsoft Database Engine, the VBA programming environ￾ment, creating help systems, and using Microsoft Office 2000 Developer. She has

authored chapters in Microsoft Access 97 Bible and Access 97 Secrets.

Jennifer owns her own consulting firm, Advanced Software Concepts, providing cus￾tom applications to both the public and private sectors. She specializes in develop￾ing client information systems for state-managed and privately-held healthcare

organizations. She has also developed a job costing and project management sys￾tem for an international construction company. Her corporate experience includes

seven years with The Travelers where she was an Associate Software Engineer serv￾ing on numerous mission-critical client/server software development projects using

Easel, C, SQL Server, and DB2. She has contributed several chapters for books on

dBase and Microsoft Access.

Jennifer holds a Bachelor of Science degree from the University of Massachusetts.

Credits

Acquisitions Editor

Greg Croy

Project Editor

Andrea C. Boucher

Technical Editor

Greg Guntle

Editorial Manager

Carol Sheehan

Vice President and Executive Group

Publisher

Richard Swadley

Vice President and Executive

Publisher

Bob Ipsen

Executive Editorial Director

Mary Bednarek

Project Coordinator

Regina Snyder

Graphics and Production Specialists

Beth Brooks, Amanda Carter, Jennifer

Click, Joyce Haughey, Barry Offringa,

Lynsey Osborn, Heather Pope

Quality Control Technicians

Carl William Pierce

Brian H. Walls

Senior Permissions Editor

Carmen Krikorian

Media Development Specialist

Angela Denny

Proofreading and Indexing

TECHBOOKS Production Services

This book is dedicated to some special people from South Windsor, Connecticut (in no

particular order) who helped me in my quest of the last year to join the South Windsor

Board of Education. Lincoln Streeter, Matt Streeter, Tim Moriarty, Tom Berstene, Teri

Dickie-Gaignat, Carrie Momnie, Tom and Audrey Delnicki, Mayor Bill Aman, Town

Manager Matt Galligan, John Mitchell, Barbara Barbour, Walter Mealy, Herb Asplund,

Roy Normen, Jan Murtha, Bob Wilson, Hap Fitts, and many others in the South

Windsor Republican Town Committee. Craig Zimmerman of the South Windsor Parks

and Recreation Commission, Ray Favreau, Mike McCarty, and Elle Randazzo of the

South Windsor Recreation Department, Joel Nadel of the Board of Assessment Appeals

and Charlie Dana, Assessor, Anne Flint and Gabrielle Batz of the South Windsor

Chamber of Commerce, and Dr. Joseph Wood, Superintendent of Schools. By allowing

me to explore new opportunities and shamelessly promote myself, you have

enriched my life and provided me with new friends and new challenges

that I never knew existed.

— CNP

This book is dedicated to the people closest to me. First and foremost it is dedicated to

my family — my wife, Dra. Arni Irwin, my sons Richard Rocco, Joseph Patrick, and

David Joseph. While writing this book, they had to put up with my constantly telling

them “Sorry, I have to do this first” in order for me to finish on deadline. It is also

dedicated to my mother, Aurelia Irwin — 74 years young and as strong as ever — who

has continued to be my sounding board and example of strength. Finally, partner,

Richard Mahonski, who passed away this year — I will truly miss him.

— MRI

This book is dedicated to my parents-in-law Tom and Arden Reardon. Way back during

my college years, they provided me with the unique opportunity to be involved in the

world of publishing as a part-time typesetter for their respective printing businesses.

Together, we forged a method of setting type using my PC and modem and their

Mergenthalers — this was 1983 — much before the era of desktop publishing as we

know it today. Now here I am enjoying being on the other side of the fence as an

author. But, all the while that I am keyboarding my own work, I am always thinking

back to the old days of how we used to have to keyboard all those crazy codes for font

changes and em spaces and line spacing. Thanks for always being so supportive of me

in the past 30 years that I have been part of your family.

— JR

Preface

Welcome to the Access 2003 Bible —your personal guide to a powerful, easy￾to-use database management system. This book is in its ninth revision and

has been totally re-written for Microsoft Office Access 2003 with new text, new pic￾tures, and a completely new and improved set of example files..

This book examines Access 2003 with more examples than any other Access book

ever written. We think that Microsoft Access is an excellent database manager and

the best Windows database on the market today. Our goal with this book is to share

what we know about Access and, in the process, to help make your work and your

life easier.

This book contains everything you need in order to learn Microsoft Access to a

mid-advanced level. You’ll find that the book starts off with the basics and builds,

chapter by chapter, on topics previously covered. In places where it is essential

that you understand previously covered topics, we present the concepts again and

review how to perform specific tasks before moving on. Although each chapter is

an integral part of the book as a whole, each chapter can also stand on its own and

has its own example files. You can read the book in any order you want, skipping

from chapter to chapter and from topic to topic. (Note that this book’s index is par￾ticularly thorough; you can refer to the index to find the location of a particular

topic you’re interested in.)

The examples in this book have been well thought out to simulate the types of

tables, queries, forms, and reports most people need to create when performing

common business activities. There are many notes, tips, and techniques (and even

a few secrets) to help you better understand the product.

This book can easily substitute for the manuals included with Access. In fact, many

users do not get manuals today, often relying on just the online help. This book will

guide you through each task you might want to do in Access. We even created

appendixes to be used as reference manuals for common Access specifications.

This book follows a much more structured approach than the Microsoft Access

manuals, going into more depth on almost every topic and showing many different

types of examples.

xii Access 2003 Bible

Is This Book for You?

We wrote this book for beginning, intermediate, and even advanced users of

Microsoft Access 2003. With any product, most users start at the beginning. If, how￾ever, you’ve already read through the Microsoft Access manuals and worked with

the Northwinds sample files, you may want to start with the later parts of this book.

Note, however, that starting at the beginning of a book is usually a good idea so you

don’t miss out on the secrets and tips in the early chapters.

We think this book covers Microsoft Access in detail better than any other book

currently on the market. We hope you will find this book helpful while working with

Access, and that you enjoy the innovative style of a Wiley book (formerly IDG

Books, the people who make the For Dummies books).

Yes — If you have no database experience

If you’re new to the world of database management, this book has everything you

need to get started with Microsoft Access. It then offers advanced topics for refer￾ence and learning.

Yes — If you’ve used other database managers

like dBASE or Filemaker

If you’re abandoning another database (such as dBASE, Filemaker, Alpha, Paradox,

FoxPro, R:Base) or even upgrading from Access 2.0, Access 95 or 97, or even

Access 2000 or 2002 this book is for you. You’ll have a head start because you’re

already familiar with database managers and how to use them. With Microsoft

Access, you will be able to do all the tasks you’ve always performed with character￾based databases — without programming or getting lost. This book will take you

through each subject step by step.

Yes — If you want to learn the basics of Visual Basic

Applications Edition (VBA) programming

VBA has replaced the Access Basic language. We know that an entire book is needed

to properly cover VBA, but we took the time to put together many chapters that

build on what you learn in the forms chapters of this book. The VBA programming

chapters use the same examples you will be familiar with by the end of the book.

Conventions Used in This Book

✦ When you are instructed to press a key combination (press and hold down

one key while pressing another key), the key combination is separated by a

xiii Preface

plus sign. Ctrl+Esc, for example, indicates that you must hold down the Ctrl

key and press the Esc key; then release both keys.

✦ Point the mouse refers to moving the mouse so that the mouse pointer is on a

specific item. Click refers to pressing the left mouse button once and releasing

it. Double-click refers to pressing the left mouse button twice in rapid succes￾sion and then releasing it. Right-click refers to pressing the right mouse but￾ton once and releasing it. Drag refers to pressing and holding down the left

mouse button while moving the mouse.

✦ When you are instructed to select a menu, you can use the keyboard or the

mouse. To use the keyboard, press and hold down the Alt key (to activate the

menu bar) and then press the underlined letter of the menu name; press Alt+E

to select the Edit menu, for example. Or you can use the mouse to click on the

word Edit on-screen. Then, from the menu that drops down, you can press the

underlined letter of the command you want (or click on the command name)

to select it.

✦ When you are instructed to select a command from a menu, you will often see

the menu and command separated by an arrow symbol. Edit_Paste, for exam￾ple, indicates that you need to select the Edit menu and then choose the Paste

command from the menu.

✦ Italic type is used for new terms and for emphasis.

✦ Bold type is used for material you need to type directly into the computer.

✦ A special typeface is used for information you see on-screen — error mes￾sages, expressions, and formulas, for example.

Icons and Alerts

You’ll notice special graphic symbols, or icons, used in the margins throughout this

book. These icons are intended to alert you to points that are particularly impor￾tant or noteworthy. The following icons are used in this book:

This icon highlights a special point of interest about the topic under discussion.

This icon points to a useful hint that may save you time or trouble.

This icon alerts you that the operation being described can cause problems if

you’re not careful.

Caution

Tip

Note