Access™ 2007 VBA Programming For Dummies®
Published by
Wiley Publishing, Inc.
111 River Street
Hoboken, NJ 07030-5774
www.wiley.com
Copyright © 2007 by Wiley Publishing, Inc., Indianapolis, Indiana
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-8600. 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-4355, or online at http://www.wiley.com/go/permissions.
Trademarks: Wiley, the Wiley Publishing logo, For Dummies, the Dummies Man logo, A Reference for the Rest of Us!, The Dummies Way, Dummies Daily, The Fun and Easy Way, Dummies.com, 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. Microsoft and Access are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. 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.
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 for a 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 Internet Websites listed in this work may have changed or disappeared between when this work was written and when it is read. FULFILLMENT OF EACH COUPON OFFER IS THE SOLE RESPONSIBILITY OF THE OFFEROR.
For general information on our other products and services, please contact our Customer Care Department within the U.S. at 877-762-2974, outside the U.S. at 317-572-3993, or fax 317-572-4002.
For technical support, please visit www.wiley.com/techsupport.
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: 2006939596
ISBN: 978-0-470-04653-1
Manufactured in the United States of America
Joe Stockman is an independent consultant, software designer, and author who has been using Microsoft Access since its initial release. He’s also developed courseware and taught classes in Access and VBA. Joe developed his first application in Access, and then migrated into Visual Basic and VB.NET, where he specializes in creating applications for the Windows Mobile platform. He worked for several software companies before forming his consulting business in 2002, where he deals with all types of clients including healthcare, financial, government, manufacturing, and small business. His ability to turn his customers’ wishes into working applications keeps them satisfied. Joe’s also writing the fundamentals column for the Advisor Guide to Microsoft Access magazine.
Alan Simpson is the author of over 100 computer books on databases, Windows, Web site design and development, programming, and networking. His books are published throughout the world in over a dozen languages and have millions of copies. Alan has also taught introductory and advanced computer programming courses at San Diego State University and the UCSD Extension. He has served as a consultant on high-technology, education- oriented projects for the United States Navy and Air Force. Despite that, Alan has no fancy job title because he has never had a real job.
Joe Stockman: To my mom and all my friends and family who supported me — and left me alone — during this project.
Alan Simpson: To Susan, Ashley, and Alec, as always.
Even though only two authors’ names appear on the cover, every book is a team project. These authors would like to thank the many people who contributed to this book. To Carole McClendon and everyone at Waterside Productions, thank you for finding this project and making it happen. Also, many thanks to Kyle Looper and Jean Rogers at Wiley for taking a chance on a new author to help with the rewrite. And also, thanks to Microsoft for making Access a wonderful development environment.
We’re proud of this book; please send us your comments through our online registration form located at www.dummies.com/register/.
Some of the people who helped bring this book to market include the following:
Acquisitions, Editorial, and Media Development
Associate Project Editor: Jean Rogers
(Previous Edition: Christopher Morris)
Acquisitions Editor: Kyle Looper
Copy Editor: Becky Whitney
Technical Editor: Russ Mullen
Editorial Manager: Kevin Kirschner
Media Development Specialists: Angela Denny, Kate Jenkins, Steven Kudirka, Kit Malone
Media Development Coordinator: Laura Atkinson
Media Project Supervisor: Laura Moss
Media Development Manager: Laura VanWinkle
Media Development Associate Producer: Richard Graves
Editorial Assistant: Amanda Foxworth
Sr. Editorial Assistant: Cherie Case
Cartoons: Rich Tennant (www.the5thwave.com)
Composition Services
Project Coordinator: Jennifer Theriot
Layout and Graphics: Carl Byers, Stephanie D. Jumper, Barbara Moore, Barry Offringa, Alicia B. South
Proofreaders: Techbooks, Brian H. Walls
Indexer: Techbooks
Anniversary Logo Design: Richard Pacifico
Publishing and Editorial for Technology Dummies
Richard Swadley, Vice President and Executive Group Publisher
Andy Cummings, Vice President and Publisher
Mary Bednarek, Executive Acquisitions Director
Mary C. Corder, Editorial Director
Publishing for Consumer Dummies
Diane Graves Steele, Vice President and Publisher
Joyce Pepple, Acquisitions Director
Composition Services
Gerry Fahey, Vice President of Production Services
Debbie Stailey, Director of Composition Services
Title
Introduction
About This Book
Conventions Used in This Book
What You’re Not to Read
Foolish Assumptions
How This Book Is Organized
Icons Used in This Book
Web Site for This Book
Where to Go from Here
Part I : Introducing VBA Programming
Chapter 1: Where VBA Fits In
Taking a Look at Access
Understanding VBA
Seeing Where VBA Lurks
Finding Out How VBA Works
Chapter 2: Your VBA Toolkit
Using the Visual Basic Editor
Referring to Objects from VBA
Setting References to Object Libraries
Using the Object Browser
Searching the Object Library
Chapter 3: Jumpstart: Creating a Simple VBA Program
Creating a Standard Module
Creating a Procedure
Understanding Syntax
Using Existing Code
Part II : VBA Tools and Techniques
Chapter 4: Understanding Your VBA Building Blocks
Commenting Your Code
Understanding VBA Data Types
Passing Data to Procedures
Repeating Chunks of Code with Loops
Making Decisions in VBA Code
Chapter 5: Controlling Access through VBA
Understanding Object Models
Manipulating Properties and Methods
Chapter 6: Programming Access Forms
Working with Class Procedures
Enabling or Disabling Form Controls
Responding to Form Events
Changing the Appearance of Objects
Opening and Closing Forms
Part III : VBA, Recordsets, and SQL
Chapter 7: The Scoop on SQL and Recordsets
What the Heck Is SQL?
Creating Tables from VBA
Adding Records to a Table
Changing and Deleting Table Records
Performing an Action Query on One Record
Working with Select Queries and Recordsets
Chapter 8: Putting Recordsets to Work
Looping through Collections
Tips on Reading and Modifying Code
Skipping Over Used Mailing Labels
Looking at How SkipLabels Works
Calling a Procedure from an Event
Part IV : Applying VBA in the Real World
Chapter 9: Creating Your Own Dialog Boxes
Displaying and Responding to Messages
Converting Forms to Dialog Boxes
Creating Custom Combo Boxes
Creating a Spin Box Control
Detecting a Right-Click
Chapter 10: Customizing Combo Boxes and List Boxes
Programming Combo and List Boxes
Linking Lists
Linking Lists across Forms
More Combo Box Tricks
Chapter 11: Creating Your Own Functions
The Role of Functions in VBA
Creating Your Own Functions
A Proper Case Function
A Function to Print Check Amounts
Chapter 12: Testing and Debugging Your Code
Understanding Compilation and Runtime
Considering Types of Program Errors
Conquering Compile Errors
Dealing with Logical Errors
Wrestling Runtime Errors
Part V : Reaching Out with VBA
Chapter 13: Using VBA with Multiple Databases
Client-Server Microsoft Access
Importing from External Databases
Linking to External Data through Code
Avoiding Multiple Tables and Links
Creating Recordsets from External Tables
Importing, Exporting, or Linking to Anything
Chapter 14: Integrating with Other Office Applications
Accessing the Object Library
Sending E-Mail via Outlook
Sending Data to Microsoft Word
Interacting with Microsoft Excel
Part VI : The Part of Tens
Chapter 15: Ten Commandments of Writing VBA
I. Thou Shalt Not Harbor Strange Beliefs about Microsoft Access
II. Thou Shalt Not Use VBA Statements in Vain
III. Remember to Keep Holy the VBA Syntax
IV. Honor Thy Parens and Quotation Marks
V. Thou Shalt Not Guess
VI. Thou Shalt Not Commit Help Adultery
VII. Thou Shalt Steal Whenever Possible
VIII. Thou Shalt Not Bear False Witness against Thy Object Browser
IX. Thou Shalt Not Covet Thy Neighbor’s Knowledge
X. Thou Shalt Not Scream
Chapter 16: Top Ten Nerdy VBA Tricks
Open a Form from VBA
See Whether a Form Is Already Open
Refer to an Open Form
Move the Cursor to a Control
Change the Contents of a Control
Update a List Box or Combo Box
Show a Custom Message
Ask the User a Question
Print a Report
Get to Know the DoCmd Object
: Further Reading
Welcome to Access 2007 VBA Programming For Dummies. As you already know (we hope), Microsoft Access is a huge database management program, offering lots of ways to manage data (information). Common uses of Access include managing mailing lists, memberships, scientific and statistical data, entire small businesses, and just about anything else that involves storing and managing large amounts of information.
As the title implies, this book is about using Visual Basic for Applications (VBA) to enhance the power of Access databases. If you want Access to print words on a check, skip mailing labels that you’ve already used, or manipulate data behind the scenes, you have to write VBA code.
By the time you finish this book, you should know exactly what VBA is all about and how it fits into Access. You’ll discover the meanings of all those obscure terms that programmers throw around — code, variable, array, loop, object — as though they were common knowledge. You’ll be able to write and use your own, custom code, just like advanced programmers do.
This book covers VBA in Access 2007. Although many changes and improvements to Access have occurred in all the versions that Microsoft has released, the VBA programming language has hardly changed a bit over the years. Although Access 2007 looks completely different from previous versions, the underlying objects are virtually unchanged. The code that you see in this book should also work in Access 2000, 2002, and 2003. The vast majority of the code in this book also works just fine even in last century’s versions, such as Access 97.
We wish we could say that this book is exactly like a coffee-table book, where you could just pick it up, flip to any page, and have everything make perfect sense to you. Well, we could say that, but we’d be lying if we did. It’s not because we want to break from the coffee-table book idea. It’s really more because some stuff in life doesn’t make much sense until after you already know something else.
Here, it isn’t really possible to make much sense of VBA code until you understand what VBA code is and why it exists. And, we are talking about Microsoft Access VBA here. To make sense of much of anything in this book, you have to already be familiar with Microsoft Access tables, queries, forms, and reports. We just don’t have enough room in this book to explain all that stuff from scratch and still have enough pages left over to talk about VBA.
On the bright side, we did everything we could to make it easy to find what you need to know, when you need to know it. You certainly don’t have to read this book from cover to cover to make sense of things. After you find the topic you’re looking for, you should be able to read through the section and be done with it quickly. Often, you can skip reading sections altogether and get all you need to know from looking at the figures.
While we’re on the topic of using this book without boring yourself to death by attempting to read it, we also stuck with some conventions for displaying text in these pages. For example, any VBA programming code appears in a monospace font with a gray background, like this:
‘VBA code to say Hello World on the screen.
Sub Hello()
MsgBox “Hello World”
End Sub
When we have just a little chunk of code to show in text, like this — Dim Wit As Date — you can see what is and what isn’t VBA code.
The ⇒ symbol that you see in text separates individual menu options (commands) that you choose in sequence. For example, rather than say “Choose New from the File menu” or “Click File on the menu bar and then click New on the drop-down menu,” we just say
Choose File⇒New from the menu bar.
When you see something in bold, we want you to enter (type) it.
Not many people in the world would put reading a computer book into the Read for Fun category. We think that reading a computer book is more likely to fall into the Read for Work or Don’t Read category. To minimize the time you have to spend away from the fun stuff, we put some information in sidebars and beside Technical Stuff icons. That information is definitely optional reading that you’re welcome to ignore.
To stay focused on VBA in this book, we need to assume that you’re already familiar with Access and that you’re comfortable creating tables, forms, reports, and queries. However, we don’t assume that you’re a true Microsoft Access expert. Let’s face it: Access isn’t exactly an easy program for most people to tackle.
Another assumption we make is that you have already created an Access database with at least some tables and forms in it. In fact, writing VBA code is usually the last step in creating a custom Access database.
Finally, we don’t assume that you’re already an accomplished programmer who is just picking up a new programming language. Rather, we assume that you’ve never written any programming code in your life — and maybe you aren’t even all that sure what programming code means or how it relates to Microsoft Access.
All books contain a lot of information. That’s what makes them books. To break down topics into smaller, more manageable chunks, we split this book into six main parts.
This part has all the information you need to get started. If you’ve already been using VBA for a few months or years, you can skim this part. If you don’t know a VBA procedure from a PTO meeting, you might want to take a closer look at Part I before venturing forth to other parts.
Here you discover how to write VBA code to make Access do things for you. For example, you’ll see how you can make Access open forms, respond to button clicks, change the appearance of objects, and more.
Here you get friendly with tools and techniques for managing your Access tables by using VBA with SQL (Structured Query Language) and recordsets. All those buzzwords make this process sound more technical than it really is. But as you’ll see, if you’ve done anything at all with queries, you’ve already been working with SQL recordsets. The idea is the same. We just use fancier terminology in the VBA world.
In this part, you get into some more advanced programming tricks, mostly by using techniques presented in earlier parts in new and creative ways. You’ll also see how to use the VBA debugging techniques, which can be real lifesavers when things go wrong and you just can’t figure out why the code you wrote isn’t doing what you intended.
VBA isn’t a programming language solely for Microsoft Access. You can also use VBA to customize all the Microsoft Office application programs, including Word, Excel, and Outlook. Furthermore, VBA can import data from, and export data to, a variety of formats that extend its reach even beyond Microsoft Access. Part V shows you how that’s all done.
What For Dummies book would be complete without a Part of Tens? Ten is such a nice number to work with, given our ten fingers and all. Chapter 15 covers the main strategies that you can adopt to avoid going crazy trying to get VBA to do your bidding. Chapter 16 goes over the top ten nerdy programming tricks you’re most likely to want to do almost from your first day of using VBA.
As you flip through this book, you’ll notice little icons sprinkled throughout its pages. These icons, as described here, point out little chunks of text that deserve either a little extra attention or very little attention:
.jpg)
If you can find a way to copy and paste — rather than type — VBA code into your database, go for it. Much of the sample VBA code shown in this book is the kind of thing you can just drop into an Access database and start using. There’s no need to retype the whole thing. Anyway, we post all the useful code at this Web site:
www.dummies.com/go/access2007vbaprog
When you get to the site, you’ll see where to find the code and how to copy and paste it into your own database, and find a link where you can send us your questions.
Now that you know what this book is about and how it’s organized, the next question is “Where do I start?” Your best bet, if you’re an absolute VBA beginner, is at Chapter 1. Try to slog through the first three (short) chapters to get your bearings.
Experienced VBA users can probably start anywhere that looks interesting. If you get in over your head at some point, watch for cross-references to earlier chapters where you can quickly fill in the knowledge gap that’s causing the confusion.
In this part . . .
VBA lets you do some pretty amazing stuff in an Access database. With VBA, you can make Access do boring, repetitive jobs that you might otherwise have to do on your own. You can even get Access to do things that it couldn’t possibly do on its own. Before you dive right in and try to make such things happen, you need to step back a moment and get a feel for how VBA fits into the whole Microsoft Access scheme of things. Then you need to get friendly with the tools available to you for turning ideas into stuff that actually happens when you want it to happen. We lead you through all of that in Chapters 1 and 2.
With your road map and tool kit in hand, you’ll be ready to get into what Access VBA is really all about — writing code (also known as programming) — to make Access do exactly what you want it to do. Yes, you write code by simply typing it, unless, of course, you can just copy and paste the code, as is often the case. Chapter 3 talks about both writing and swiping VBA code.