Access.2003.VBA.doc

(26239 KB) Pobierz

Access 2003 VBA

Programmer's Reference

Access 2003 VBA

Programmer's Reference

Patricia Cardoza Teresa Hennig Graham Seach Armen Stein

WI LEY
Wiley Publishing, Inc.

Access 2003 VBA Programmer's Reference

Published by

Wiley Publishing, Inc.

10475 Crosspoint Boulevard Indianapolis, IN 46256

www.wiley.com

Published simultaneously in Canada

eISBN: 0-7645-7166-4

Manufactured in the United States of America

10987654321

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 Section 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, Inc., 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: permcoordinator@wiles. com.

LIMIT OF LIABILITY/DISCLAIMER OF WARRANTY: THE PUBLISHER AND THE AUTHOR MAKE NO REPRESENTATIONS OR WARRANTIES WITH RESPECT TO THE ACCURACY OR COMPLETENESS OF THE CONTENTS OF THIS WORK AND SPECIFICALLY DISCLAIM ALL WARRANTIES, INCLUDING WITHOUT LIMITATION, WARRANTIES OF FITNESS FORA PARTICULAR PURPOSE. NO WARRANTY MAY BE CREATED OR EXTENDED BY SALES OR PROMOTIONAL MATERIALS. THE ADVICE AND STRATEGIES CONTAINED HEREIN MAY NOT BE SUITABLE FOR EVERY SITUATION. THIS WORK IS SOLD WITH THE UNDERSTANDING THAT THE PUBLISHER IS NOT ENGAGED IN RENDERING LEGAL, ACCOUNTING, OR OTHER PROFESSIONAL SERVICES. IF PROFESSIONAL ASSISTANCE IS REQUIRED, THE SERVICES OF A COMPETENT PROFESSIONAL PERSON SHOULD BE SOUGHT. NEITHER THE PUBLISHER NOR THE AUTHOR SHALL BE LIABLE FOR DAMAGES ARISING HEREFROM. THE FACT THAT AN ORGANIZATION OR WEBSITE IS REFERRED TO IN THIS WORK AS A CITATION AND/OR A POTENTIAL SOURCE OF FURTHER INFORMATION DOES NOT MEAN THAT THE AUTHOR OR THE PUBLISHER ENDORSES THE INFORMATION THE ORGANIZATION OR WEBSITE MAY PROVIDE OR RECOMMENDATIONS IT MAY MAKE. FURTHER, READERS SHOULD BE AWARE THAT INTER-NET WEBSITES LISTED IN THIS WORK MAY HAVE CHANGED OR DISAPPEARED BETWEEN WHEN THIS WORK WAS WRITTEN AND WHEN IT IS READ.

For general information on our other products and service 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.

Trademarks: Wiley, the Wiley logo, Wrox, the Wrox logo, Programmer to Programmer and related trade dress are trademarks or registered trademarks of John Wiley & Sons, Inc. and/or its 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.

To my husband Rob, together we can climb mountains. To my parents, who've given me the foundation to succeed in life; I love you. -Patricia

To my family, for their unwavering love and encouragement. To the PNWADG, for the privilege of leading such a great group of developers. -Teresa

I dedicate this book (or at least my chapters) to my beautiful wife Julie-Anne, for carrying the world on her shoulders so I could work uninterrupted, and for her unfailing support, encouragement, and sacrifice, despite illness and personal hardship. And to my children, Madeleine and Eli, who tried to understand why Daddy couldn't spend so much time with them. I am indeed a lucky man.

-Graham

Credits

Authors

Patricia Cardoza Teresa Hennig Armen Stein Graham Seach

Executive Acquisitions Editor Robert Elliott

Development Editor Eileen Bien Calabro

Production Editor Angela Smith

Copy Editor TechBooks

Senior Production Manager Fred Bernardi

Editorial Manager Kathryn Maim

Vice President & Executive Group Publisher Richard Swadley

Vice President and Executive Publisher Robert Ipsen

Vice President and Publisher Joseph B. Wikert

Executive Editorial Director Mary Bednarek

Proofreading and Indexing TECHBOOKS Production Services

About the Authors Authors

Patricia Cardoza is an Exchange and Outlook Applications developer, writer, and Microsoft Outlook MVP. Patricia currently serves as Exchange Administrator and lead developer for Pacific Southwest Container, a large manufacturing company. She has authored two other books, Special Edition Using Microsoft Office Outlook 2003 and Absolute Beginner's Guide to Microsoft OneNote 2003. She has also written numerous articles about Outlook, Exchange, and wireless devices for a variety of journals including .NET Magazine and Tech Republic. In her spare time, she loves to cook, read, watch movies with her husband, and spoil her three cats.

Teresa Hennig is the owner of Eade.com, which specializes in developing cost-effective Access applications. Her energy and enthusiasm are complemented by her ability to quickly grasp a situation and provide a solution. Her biggest rewards are being a partner with her clients and sharing their joy in their successes. Her site, www.DatabaseAnswerSite.com, is recognized as a resource for learning about relational databases by several universities and technical support organizations.

An avid Access aficionado, Teresa is the president of the two leading Access user groups in the US northwest: the Pacific Northwest Access Developers Group (PNWADG) and the Access Special Interest Group. She is also dedicated to helping the Spinal Cord Society raise money to find a cure for spinal cord injuries. And, she wishes that she had more time to enjoy dancing.

Graham Seach is Chief Development Officer for a Sydney-based database development company, Pacific Database (www.pacificdb.com.au). He has been developing applications in Access since version 1.0, has participated in the Microsoft Office 2003 beta program, and has presented at several Microsoft events, notably the Microsoft Office 2003 System launch in Sydney and Melbourne in 2003.

Graham holds an MCP in Access Development, master-level Brainbench certification in Access, and is recognized by Microsoft as an MVP (most valuable professional). He has received this award three times. He regularly provides worldwide Access developer support for Microsoft via the Microsoft newsgroups, and has been published in MSDN Magazine. Graham's technical focus is now on Access and the SQL Server integration, having provided many business solutions to a wide range of government, military, and private organizations.

Armen Stein is the owner and founder of J Street Technology, Inc., a team of database application developers in Redmond, Washington. He is President Emeritus of the Pacific Northwest Access Developers Group, and has contributed to Access/Office/VB Advisor magazine and Smart Access. He has taught database classes at Bellevue Community College and Seattle Central Community College and also developed and taught his own 1-day training class called Access Development the I Street Way. Armen earned a business administration/computer science degree from Western Washington University, and has been developing computer applications since 1984. His other interests include activities with his family, backgammon, cheering on the Mariners, and driving his 1969 Ford Bronco in the sun.

Contributors

Steve Clark is a Microsoft Access MVP, an MCP, and a Project Manager and Developer for the FMS Professional Solutions Group (www.fmsinc.com/consulting). As a developer, he specializes in database development and has produced Access, VB(.NET), and ASP(.NET) applications for clients from all elements of business, both nationally and internationally. He earned a bachelor's degree in computer science from the University of Cincinnati in 1994 and has been with FMS since 1998 and an Access MVP since 2001. When he's not managing or developing, he enjoys participating in all forms of motorcycle racing.

Brian M. Sockey is founder and president of Farsight Solutions, Inc. (DBA TeleVantage NorthWest), a Microsoft Business Solutions partner, and value-added reseller of Artisoft TeleVantage software-based business telephone systems. Before starting TeleVantage NorthWest, Brian worked in the Developer Support group at Microsoft, where he specialized in client/server development issues involving Microsoft Access and SQL Server. Brian enjoys the great outdoors, theater, and good food (almost everything except blue cheese).

Randall I. Weers, the founder and president of Procia, Inc., specializes in process and data management applications utilizing the Microsoft Office suite. His primary focus is to help people manage business processes and business rules through the appropriate application of process improvement and automation. Randall's experience includes everything from business process analysis to technical and training manual writing. Randall makes his home in Seattle, Washington, where he is the facilitator for IT WorkGroups, a Puget Sound-based affiance of senior-level independent information technology professionals.

Sam Radakovitz joined Microsoft in 1998 and was a member of the Access team for Access 2000, 2002, and 2003. A lot of his efforts were focused on wizards and security features. For Access 2003, Sam was thoroughly engaged in the new macro security. This made it a perfect fit for him to review this book's security chapters, especially the one on macro security. When creating Access applications, Sam enjoys going outside the box and adding a bit of creativity. So, being invited to provide applications that could demonstrate some of Access's new features seemed like an awesome opportunity to share his ideas with other developers.

Acknowledgments

We'd like to thank all the folks at Wiley who made this book possible. Bob Elliott worked with Patricia and Teresa to build the amazing team of authors present on this book as well as to get all the mounds of paperwork straight. Eileen Bien Calabro showed incredible patience formatting, organizing, and editing our chapters. She reassured us that we could get all this done and done well, in a remarkably short time, as long as we focused. Our technical editor, Michael Stowe, tirelessly checked all of our facts and gently steered us back on course when needed. Techbooks International Pvt. Ltd. worked incredibly hard to format all the pages and work with all of the figures in this book. Thanks everyone!

-The Group

Thanks to all who had to put up with me when I had to meet deadlines. My husband, Rob, endured many nights of takeout and several weekends when I was glued to my computer. My parents, Joe and Sally, have supported me in everything I do and I thank God for them every day.

I would not be where I am today in my career if it were not for the Microsoft Most Valuable Professional (MVP) program. I am honored to be included in that esteemed group of professionals.

Lastly, I'd like to thank Teresa Hennig. She handled the majority of the scheduling of chapters and helped me keep all of the information for this book organized. Coordinating four authors and several contributors takes patience and dedication. Thanks Teresa for all your hard work.

-Patricia

First, I'd like to thank Paul Eade for being such an incredible resource and wealth of knowledge. You are an amazing friend, and I cherish the opportunities to laugh and just be silly.

I can't say enough about the Microsoft Access Team, including Rita Nikas. Their passion and dedication has made Access an incredibly powerful program. A special note of appreciation to Bill Ramos, Tim Getsch, and Sanjay Jacob for investing so much of themselves into finding out what developers want, for making the ADE so awesome, and for being so responsive to all of our requests! I also want to thank Michael Kaplan for sharing his opinions and encouragement and for doing so much for the Access groups.

To everyone I've met through this book ... thank you for an amazing opportunity to enjoy a new challenge and to help Access developers! Very special thanks go to Patricia Cardoza, who seems like Wonder Woman-balancing a career and family while writing multiple books. She not only wrote several elements, but also reviewed every chapter and helped us all in countless ways.

And thanks to Randy Weers, who offered help when I needed it. Yes, work should be fun! And last thanks go to all the Mikes and Michaels. I can't imagine a world without Michaels!

-Teresa

Acknowledgments

Needless to say, I want to first thank my wife and children for their support, encouragement, and understanding throughout a very difficult and demanding time.

I want to thank Rita Nikas, my Microsoft MVP Lead, who first made me aware of the book, and whose resolute determination to solve problems and to source much needed information will always be appreciated.

I also want to thank Mike Gunderloy, who, despite the fact that we had never met, kindly sent pages from his own book, to help me with mine; a gesture I will never forget.

Finally, I would like to express my thanks to Professor Roger Box of Charles Sturt University, who gave me a 2-week extension on my final assignment, so I could make a book deadline.

-Graham

I would like to acknowledge the encouragement and support from my wife, Lori, and kids, Lauren and Jonathan.

-Armen

I'm glad that I could help Randy and Teresa. It is cool to feature some of my work on the book's Web site. I want to thank my girlfriend Denice and my parents for putting up with my long hours of work and extra work! Without everyone's support and understanding my daily life would be much more difficult!

-Steve

My thanks to Teresa Hennig for giving me the opportunity to contribute to this book and for cracking the whip when I was struggling to keep moving forward. My deep appreciation to Sam Radakovitz for the invaluable insight into the new security features of Access. Thanks also to the members in IT Workgroups who helped me deliver a better picture for the Access Security model. And most of all, in loving memory of my wife Lisa, I dedicate my efforts on this book.

-Randall

Contents