Publisher’s Acknowledgments
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
01_046531 ffirs.qxp 1/16/07 9:07 PM Page viii
Contents at a Glance
Introduction 1
Part I: Introducing VBA Programming 7
Chapter 1: Where VBA Fits In 9
Chapter 2: Your VBA Toolkit 21
Chapter 3: Jumpstart: Creating a Simple VBA Program 35
Part II: VBA Tools and Techniques 49
Chapter 4: Understanding Your VBA Building Blocks 51
Chapter 5: Controlling Access through VBA 71
Chapter 6: Programming Access Forms 87
Part III: VBA, Recordsets, and SQL 115
Chapter 7: The Scoop on SQL and Recordsets 117
Chapter 8: Putting Recordsets to Work 147
Part IV: Applying VBA in the Real World 173
Chapter 9: Creating Your Own Dialog Boxes 175
Chapter 10: Customizing Combo Boxes and List Boxes 201
Chapter 11: Creating Your Own Functions 239
Chapter 12: Testing and Debugging Your Code 265
Part V: Reaching Out with VBA 293
Chapter 13: Using VBA with Multiple Databases 295
Chapter 14: Integrating with Other Office Applications 315
Part VI: The Part of Tens 349
Chapter 15: Ten Commandments of Writing VBA 351
Chapter 16: Top Ten Nerdy VBA Tricks 357
Index 367
02_046531 ftoc.qxp 1/16/07 9:07 PM Page ix
Table of Contents
Introduction 1
About This Book 1
Conventions Used in This Book 2
What You’re Not to Read 2
Foolish Assumptions 3
How This Book Is Organized 3
Part I: Introducing VBA Programming 3
Part II: VBA Tools and Techniques 3
Part III: VBA, Recordsets, and SQL 4
Part IV: Applying VBA in the Real World 4
Part V: Reaching Out with VBA 4
Part VI: The Part of Tens 4
Icons Used in This Book 4
Web Site for This Book 5
Where to Go from Here 5
Part I: Introducing VBA Programming 7
Chapter 1: Where VBA Fits In . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .9
Taking a Look at Access 10
Understanding VBA 11
Seeing Where VBA Lurks 12
Finding standard modules 13
Finding class modules 13
From VBA to Access 15
Finding Out How VBA Works 17
Discovering VBA procedures 17
Recognizing VBA procedures 18
Chapter 2: Your VBA Toolkit . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .21
Using the Visual Basic Editor 21
Using Project Explorer 23
Using the Properties window 24
Using the Immediate window 26
Using the Code window 27
Referring to Objects from VBA 29
Setting References to Object Libraries 30
Using the Object Browser 30
Searching the Object Library 33
02_046531 ftoc.qxp 1/16/07 9:07 PM Page xi
Access 2007 VBA Programming For Dummies
xii
Chapter 3: Jumpstart: Creating a Simple VBA Program . . . . . . . . . . . .35
Creating a Standard Module 35
Creating a Procedure 36
Understanding Syntax 38
Getting keyword help 40
Help with arguments 43
About named arguments 45
Using Existing Code 46
Copy and paste code from the Web 46
Importing modules 47
Modifying existing code 48
Part II: VBA Tools and Techniques 49
Chapter 4: Understanding Your VBA Building Blocks . . . . . . . . . . . . .51
Commenting Your Code 52
Understanding VBA Data Types 53
Passing Data to Procedures 54
Storing data in variables and constants 57
Storing data in arrays 58
Module-level versus procedure-level 60
Naming conventions for variables 61
Repeating Chunks of Code with Loops 62
Using Do Loop to create a loop 62
Using While Wend to create a loop 64
Using For Next to create a loop 64
Making Decisions in VBA Code 66
Using If End If statements 67
Using a Select Case block 68
Chapter 5: Controlling Access through VBA . . . . . . . . . . . . . . . . . . . . . .71
Understanding Object Models 72
Distinguishing between objects and collections 72
Understanding properties and methods 75
Identifying the icons for objects, properties, and methods 77
Manipulating Properties and Methods 79
Getting the value of a property 79
Changing the value of a property 81
Using an object’s methods 82
Seeking help with properties and methods 84
Chapter 6: Programming Access Forms . . . . . . . . . . . . . . . . . . . . . . . . .87
Working with Class Procedures 87
Enabling or Disabling Form Controls 90
Using VBA to position the cursor 91
Choosing an object and event for the code 92
02_046531 ftoc.qxp 1/16/07 9:07 PM Page xii
Showing and hiding controls 95
Making controls read-only 96
Responding to Form Events 96
Changing the Appearance of Objects 99
Changing colors 99
Controlling boldface, italics, and such 103
Changing special effects 104
Using the With End With statements 104
Filling form controls with data 105
Opening and Closing Forms 107
Closing a form 109
Adding a related record to another table 109
More DoCmd methods for forms 112
Part III: VBA, Recordsets, and SQL 115
Chapter 7: The Scoop on SQL and Recordsets . . . . . . . . . . . . . . . . . . .117
What the Heck Is SQL? 117
Writing SQL without knowing SQL 120
Select queries versus action queries 121
Getting SQL into VBA 123
Hiding warning messages 124
Storing SQL statements in variables 125
Creating Tables from VBA 128
Creating new tables from existing tables 128
Creating a new, empty table from VBA 129
Closing and deleting tables through VBA 130
Adding Records to a Table 131
Appending a single record with SQL 132
Query to append one record 133
Changing and Deleting Table Records 134
Performing an Action Query on One Record 136
Working with Select Queries and Recordsets 137
Defining a connection 140
Defining the recordset and data source 141
Filling the recordset with data 142
Managing recordsets 143
Referring to fields in a recordset 145
Closing recordsets and collections 146
Chapter 8: Putting Recordsets to Work . . . . . . . . . . . . . . . . . . . . . . . . .147
Looping through Collections 147
Using For Each loops 149
Using shorter names for objects 152
xiii
Table of Contents
02_046531 ftoc.qxp 1/16/07 9:07 PM Page xiii
Access 2007 VBA Programming For Dummies
xiv
Tips on Reading and Modifying Code 154
Square brackets represent names 154
Other ways to refer to objects 155
The continuation character 157
Skipping Over Used Mailing Labels 159
Looking at How SkipLabels Works 162
Passing data to SkipLabels 164
Declaring variables 165
Copying the label report 165
Getting a report’s recordsource 165
Creating the recordset 166
Creating LabelsTempTable from MyRecordSet 166
Calling a Procedure from an Event 171
Part IV: Applying VBA in the Real World 173
Chapter 9: Creating Your Own Dialog Boxes . . . . . . . . . . . . . . . . . . . .175
Displaying and Responding to Messages 176
Asking a question 176
Designing a message box 177
Responding to a MsgBox button click 180
Converting Forms to Dialog Boxes 182
Storing dialog box settings 183
Setting form properties 184
Adding controls to the dialog box 187
Creating Custom Combo Boxes 189
Creating a Spin Box Control 195
Detecting a Right-Click 198
Chapter 10: Customizing Combo Boxes and List Boxes . . . . . . . . . . .201
Programming Combo and List Boxes 202
Listing field names 204
Listing text options 207
Listing Table/Query field values 212
Linking Lists 216
Running code when a form opens 218
Running code when the user makes a choice 219
Linking Lists across Forms 222
Updating a combo box or a list box 223
Opening a form to enter a new record 225
Seeing whether a form is open 226
Getting forms in sync 227
More Combo Box Tricks 228
Using hidden values in combo and list boxes 228
Giving users a quick find 232
Avoiding retyping common entries 235
02_046531 ftoc.qxp 1/16/07 9:07 PM Page xiv
Chapter 11: Creating Your Own Functions . . . . . . . . . . . . . . . . . . . . . .239
The Role of Functions in VBA 239
Creating Your Own Functions 241
Passing data to a function 242
Returning a value from a function 243
Testing a custom function 244
A Proper Case Function 245
Looking at how PCase( ) works 247
Using the PCase( ) function 248
A Function to Print Check Amounts 251
Using the NumWord function 254
Looking at how NumWord( ) works 256
Chapter 12: Testing and Debugging Your Code . . . . . . . . . . . . . . . . . .265
Understanding Compilation and Runtime 266
Considering Types of Program Errors 268
Conquering Compile Errors 269
Expected: expression 271
Expected: end of statement 272
Expected: list separator or ) 272
Dealing with Logical Errors 274
Checking on variables with Debug.Print 275
Slowing down code 279
Getting back to normal in the Code window 282
Wrestling Runtime Errors 283
Responding to a runtime error 283
Trapping runtime errors 285
Writing your own error handlers 288
Part V: Reaching Out with VBA 293
Chapter 13: Using VBA with Multiple Databases . . . . . . . . . . . . . . . .295
Client-Server Microsoft Access 296
Importing from External Databases 302
Linking to External Data through Code 304
Avoiding Multiple Tables and Links 305
Creating Recordsets from External Tables 308
Importing, Exporting, or Linking to Anything 309
Using a macro to write the code 309
Quick and easy import/export/link 312
Chapter 14: Integrating with Other Office Applications . . . . . . . . . . .315
Accessing the Object Library 315
Exploring a program’s object model 317
Meet the Application object 318
Connecting to other programs 319
xv
Table of Contents
02_046531 ftoc.qxp 1/16/07 9:07 PM Page xv
Sending E-Mail via Outlook 320
Sending Data to Microsoft Word 325
Creating the Word template 325
Creating the Access form 327
Writing the merge code 328
Interacting with Microsoft Excel 334
Creating the worksheet 335
Creating a query and a form 336
Writing the Excel code 337
Copying a table or query to a worksheet 342
Running Excel macros from Access 346
Part VI: The Part of Tens 349
Chapter 15: Ten Commandments of Writing VBA . . . . . . . . . . . . . . . . .351
I. Thou Shalt Not Harbor Strange Beliefs about Microsoft Access 351
II. Thou Shalt Not Use VBA Statements in Vain 351
III. Remember to Keep Holy the VBA Syntax 352
IV. Honor Thy Parens and Quotation Marks 353
V. Thou Shalt Not Guess 354
VI. Thou Shalt Not Commit Help Adultery 354
VII. Thou Shalt Steal Whenever Possible 355
VIII. Thou Shalt Not Bear False Witness against Thy Object Browser 355
IX. Thou Shalt Not Covet Thy Neighbor’s Knowledge 356
X. Thou Shalt Not Scream 356
Chapter 16: Top Ten Nerdy VBA Tricks . . . . . . . . . . . . . . . . . . . . . . . . .357
Open a Form from VBA 357
See Whether a Form Is Already Open 358
Refer to an Open Form 358
Move the Cursor to a Control 359
Change the Contents of a Control 360
Update a List Box or Combo Box 360
Show a Custom Message 361
Ask the User a Question 362
Print a Report 363
Get to Know the DoCmd Object 364
Index 367
Access 2007 VBA Programming For Dummies
xvi
02_046531 ftoc.qxp 1/16/07 9:07 PM Page xvi
Introduction
W
elcome 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 stor-
ing 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 improve-
ments 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 ver-
sions, 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 major-
ity of the code in this book also works just fine even in last century’s ver-
sions, such as Access 97.
About This Book
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.
03_046531 intro.qxp 1/16/07 9:07 PM Page 1
Here, it isn’t really possible to make much sense of VBA code until you under-
stand 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.
Conventions Used in This Book
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 (com-
mands) 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.
What You’re Not to Read
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
2
Access 2007 VBA Programming For Dummies
03_046531 intro.qxp 1/16/07 9:07 PM Page 2
Không có nhận xét nào:
Đăng nhận xét