Table of Contents

Introduction 1

About This Book 2

Conventions Used in This Book 2

What You're Not to Read 3

Foolish Assumptions 3

How This Book Is Organized 3

Part I: Introducing VBA Programming 3

Part II: VBA Tools and Techniques 4

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 5

Web Site for This Book 5

Where to Go from Here 6

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 25

Using the Code window 26

Referring to Objects from VBA 28

Setting References to Object Libraries 29

Using the Object Browser 30

Searching the Object Library 32

Chapter 3: Jumpstart: Creating a Simple VBA Program 35

Creating a Standard Module 35

Creating a Procedure 36

Understanding Syntax 38

Getting keyword help 39

Help with arguments 43

About named arguments 45

Modifying Existing Code 46

Copy-and-paste code from the Web 46

Importing standard 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 55

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 78

Getting the value of a property 79

Changing the value of a property 80

Using an object's methods 81

Seeking help with properties and methods 82

Chapter 6: Programming Access Forms 87

Working with Class Procedures 87

Enabling 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 103

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 111

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 125

Storing SQL statements in variables 126

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

Doing 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

Methods for managing recordsets 144

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 153

Square brackets represent names 154

Other ways to refer to objects 155

Using the continuation character 156

Skipping Over Used Mailing Labels 158

How SkipLabels Works 162

Passing data to SkipLabels 164

Declaring variables 165

Copying the label report 165

Getting a report's record source 165

Creating the recordset 166

Creating LabelsTempTable from MyRecordSet 166

Calling a Procedure from an Event 170

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 186

Creating Custom Combo Boxes 188

Creating a Spin Box Control 195

Detecting a Right-Click 198

Chapter 10: Customizing Lists and Drop-Down Menus 201

Programming Combo and List Boxes 202

Listing field names 204

Listing text options 207

Listing Table/Query field values 213

Linking Lists 217

Running code when a form opens 219

Running code when the user makes a choice 220

Linking Lists across Forms 223

Updating a combo box or a list box 224

Open a form to enter a new record 226

Seeing whether a form is open 227

Getting forms in sync 228

More Combo Box Tricks 229

Hidden values in combo and list boxes 229

Giving users a quick find 233

Avoid retyping common entries 237

Chapter 11: Creating Your Own Functions 241

The Role of Functions in VBA 241

Creating Your Own Functions 243

Passing data to a function 244

Returning a value from a function 245

Testing a custom function 246

A Proper Case Function 247

How PCase() works 249

Using the PCase() function 250

A Function to Print Check Amounts 253

Using the NumWord function 255

How NumWord() works 258

Chapter 12: Testing and Debugging Your Code 267

Understanding Compilation and Runtime 268

Considering Types of Program Errors 270

Conquering Compile Errors 271

Expected: expression 273

Expected: end of statement 274

Expected: list separator or ) 274

Dealing with Logical Errors 276

Checking on variables with Debug.Print 277

Slowing down code 280

Getting back to normal in the Code window 284

Wrestling Runtime Errors 285

Responding to a runtime error 286

Trapping runtime errors 287

Writing your own error handlers 290

Part V: Reaching Out with VBA 295

Chapter 13: Using VBA with Multiple Databases 297

Client-Server Microsoft Access 297

Importing from External Databases 303

Linking to External Data through Code 305

Avoiding Multiple Tables and Links 307

Creating Recordsets from External Tables 309

Importing/Exporting/Linking to Anything 311

Using a macro to write the code 311

Quick and easy import/export 313

Chapter 14: Integrating with Other Office Applications 317

Accessing the Object Library 317

Exploring a program's object model 319

Meet the Application object 319

Connecting to other programs 321

Sending E-mail via Outlook 322

Sending Data to Microsoft Word 326

Creating the Word template 327

Creating the Access form 329

Writing the merge code 330

Interacting with Microsoft Excel 336

Creating the worksheet 336

Creating a query and a form 337

Writing the Excel code 338

Running Excel macros from Access 347

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

1. Open a Form from VBA 357

2. See Whether a Form Is Already Open 358

3. Refer to an Open Form 358

4. Move the Cursor to a Control 359

5. Change the Contents of a Control 360

6. Update a List Box or Combo Box 361

7. Show a Custom Message 361

8. Ask the User a Question 362

9. Print a Report 363

10. Get to Know the DoCmd Object 364

Chapter 17: (Way More Than) Ten Shortcut Keys 367

Code and Immediate Window Shortcuts 367

General VBA Editor Shortcut Keys 369

Debug Shortcut Keys 369

Index 371

0 0

Post a comment