Table Of Content

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]. 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. is a trademark of Wiley Publishing, Inc. About the Author Cary N. Pragueis 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 conferences 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’sjournal. 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. Irwinis 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 software 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 market, 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 Bibleseries 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 international 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 Reardonis considered a leading developer of custom database applications. She has over ten years’ experience developing client/server and PC-based applications. She has accumulated much of her application development experience 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 custom applications to both the public and private sectors. She specializes in developing client information systems for state-managed and privately-held healthcare organizations. She has also developed a job costing and project management system for an international construction company. Her corporate experience includes seven years with The Travelers where she was an Associate Software Engineer serving 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 Project Coordinator Greg Croy Regina Snyder Project Editor Graphics and Production Specialists Andrea C. Boucher Beth Brooks, Amanda Carter, Jennifer Click, Joyce Haughey, Barry Offringa, Technical Editor Lynsey Osborn, Heather Pope Greg Guntle Quality Control Technicians Editorial Manager Carl William Pierce Carol Sheehan Brian H. Walls Vice President and Executive Group Senior Permissions Editor Publisher Carmen Krikorian Richard Swadley Media Development Specialist Vice President and Executive Angela Denny Publisher Bob Ipsen Proofreading and Indexing TECHBOOKS Production Services Executive Editorial Director Mary Bednarek 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 enrichedmy life and provided me with new friends and new challenges thatIneverknew 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