Access VBA Programming For Dummies. Wiley

by Joseph C. Stockman and Alan Simpson

Get access to the latest release for Creating Powerful Databases and Applications

Contains Ready-to-Use VBA Code and Access Procedures

Access VBA Programming For Dummies

Authors’ Acknowledgments
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.

About the Author
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, educationoriented
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.

Introduction

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.

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.

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.

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 (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.

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

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.

Foolish Assumptions

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.

How This Book Is Organized

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.

Part I: Introducing VBA Programming

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.

Part II: VBA Tools and Techniques

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.

Part III: VBA, Recordsets, and SQL

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.

Part IV: Applying VBA in the Real 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.

Part V: Reaching Out with VBA

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.

Part VI: The Part of Tens

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.

Screenshot

Purchase Now !
Just with Paypal

Product details

Price
File Size	9,858 KB
Pages	411 p
File Type	PDF format
ISBN	978-0-470-04653-1
Copyright	2007 by Wiley Publishing, Inc

Contents at a Glance
Introduction
Part I: Introducing VBA Programming
Chapter 1: Where VBA Fits In
Chapter 2: Your VBA Toolkit
Chapter 3: Jumpstart: Creating a Simple VBA Program
Part II: VBA Tools and Techniques
Chapter 4: Understanding Your VBA Building Blocks
Chapter 5: Controlling Access through VBA
Chapter 6: Programming Access Forms
Part III: VBA, Recordsets, and SQL
Chapter 7: The Scoop on SQL and Recordsets
Chapter 8: Putting Recordsets to Work
Part IV: Applying VBA in the Real World
Chapter 9: Creating Your Own Dialog Boxes
Chapter 10: Customizing Combo Boxes and List Boxes
Chapter 11: Creating Your Own Functions
Chapter 12: Testing and Debugging Your Code
Part V: Reaching Out with VBA
Chapter 13: Using VBA with Multiple Databases
Chapter 14: Integrating with Other Office Applications
Part VI: The Part of Tens
Chapter 15: Ten Commandments of Writing VBA
Chapter 16: Top Ten Nerdy VBA Tricks
Index


Table of Contents

Introduction.............

About This Book...............

Conventions Used in This Book .....

What You’re Not to Read......

Foolish Assumptions ........

How This Book Is Organized...........

Part I: Introducing VBA Programming ...

Part II: VBA Tools and Techniques..............

Part III: VBA, Recordsets, and SQL................

Part IV: Applying VBA in the Real World ...

Part V: Reaching Out with VBA........

Part VI: The Part of Tens ........

Icons Used in This Book....

Web Site for This Book ................

Where to Go from Here.......

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

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

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

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

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

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

  ●▬▬▬▬▬❂❂❂▬▬▬▬▬●

●▬▬❂❂▬▬●

●▬❂▬●

●❂●

═════● ●═════