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 1: 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 U: 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

0 0

Post a comment