Access 2007 VBA Programming For Dummies

by Joseph C. Stockman, Alan Simpson

Overview

Customize combo and list boxes

Go beyond forms and reports and build custom database solutions

Need to know how VBA works in Access 2007? This plain-English guide introduces you to the fundamentals of VBA programming, helping you get the most out of your Access apps and giving you practical code examples for immediate use in your databases. You'll see how to control Access through VBA and apply powerful real-world VBA solutions.

Discover how to

• Create simple VBA programs
• Program Access forms
• Manage Access tables with SQL and recordsets
• Test and debug your code
• Use VBA with multiple databases
• Integrate with other Office apps

Product Details

ISBN-13:	9781118050750
Publisher:	Wiley
Publication date:	02/08/2011
Sold by:	JOHN WILEY & SONS
Format:	eBook
Pages:	416
File size:	22 MB
Note:	This product may take a few minutes to download.

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.

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
Introducing VBA Programming     3
VBA Tools and Techniques     3
VBA, Recordsets, and SQL     4
Applying VBA in the Real World     4
Reaching Out with VBA     4
The Part of Tens     4
Icons Used in This Book     4
Web Site for This Book     5
Where to Go from Here     5
Introducing VBA Programming     7
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
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
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
VBA foots and Techniques     49
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
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
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
VBA, Recordsets, and SQL     115
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
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
Applying VBA in the Real World     173
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
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
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
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
Reaching Out with VBA     293
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
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
The Part of Tens     349
Ten Commandments of Writing VBA     351
Thou Shalt Not Harbor Strange Beliefs about Microsoft Access     351
Thou Shalt Not Use VBA Statements in Vain     351
Remember to Keep Holy the VBA Syntax     352
Honor Thy Parens and Quotation Marks     353
Thou Shalt Not Guess     354
Thou Shalt Not Commit Help Adultery     354
Thou Shalt Steal Whenever Possible     355
Thou Shalt Not Bear False Witness against Thy Object Browser     355
Thou Shalt Not Covet Thy Neighbor's Knowledge     356
Thou Shalt Not Scream     356
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