Access 2007 VBA Programming For Dummies
by Stockman, Joseph C.; Simpson, AlanEdition: 1st
Format: Paperback
Pub. Date: 2007-02-20
Publisher(s): For Dummies
Other versions by this Author
-
This Item Qualifies for Free Shipping!*
*Excludes marketplace orders.
List Price: $31.49
Buy New
Arriving Soon. Will ship when available.
$29.99
Rent Book
Select for Price
Rent Digital
$21.60
Used Book
We're Sorry
Sold Out
How Marketplace Works:
- This item is offered by an independent seller and not shipped from our warehouse
- Item details like edition and cover design may differ from our description; see seller's comments before ordering.
- Sellers much confirm and ship within two business days; otherwise, the order will be cancelled and refunded.
- Marketplace purchases cannot be returned to eCampus.com. Contact the seller directly for inquiries; if no response within two days, contact customer service.
- Additional shipping costs apply to Marketplace purchases. Review shipping costs at checkout.
Summary
If you've been using Access for a while, you're probably aware of its power and potential and itching to take advantage of both. Access 2007 VBA Programming For Dummies takes you beyond forms and reports and shows you how to use VBA to create killer Access databases and applications. This gentle introduction to VBA programming covers everything you need to get started, including: Basic programming skills and concepts Explanations of modules, procedures, objects, and arguments Access-unique programming activities, including SQL and recordsets How to use the Visual Basic editor Creating dialog boxes, lists, drop-down menus, and functions Integrating with other Office applications Ready-to-use VBA code examples to type in or copy and paste from the Web Completely revised to reflect all changes found in Microsoft Access 2007, Access 2007 VBA Programming For Dummies gives you access to Access like you've never had it before.
Author Biography
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 | p. 1 |
| About This Book | p. 1 |
| Conventions Used in This Book | p. 2 |
| What You're Not to Read | p. 2 |
| Foolish Assumptions | p. 3 |
| How This Book Is Organized | p. 3 |
| Introducing VBA Programming | p. 3 |
| VBA Tools and Techniques | p. 3 |
| VBA, Recordsets, and SQL | p. 4 |
| Applying VBA in the Real World | p. 4 |
| Reaching Out with VBA | p. 4 |
| The Part of Tens | p. 4 |
| Icons Used in This Book | p. 4 |
| Web Site for This Book | p. 5 |
| Where to Go from Here | p. 5 |
| Introducing VBA Programming | p. 7 |
| Where VBA Fits In | p. 9 |
| Taking a Look at Access | p. 10 |
| Understanding VBA | p. 11 |
| Seeing Where VBA Lurks | p. 12 |
| Finding standard modules | p. 13 |
| Finding class modules | p. 13 |
| From VBA to Access | p. 15 |
| Finding Out How VBA Works | p. 17 |
| Discovering VBA procedures | p. 17 |
| Recognizing VBA procedures | p. 18 |
| Your VBA Toolkit | p. 21 |
| Using the Visual Basic Editor | p. 21 |
| Using Project Explorer | p. 23 |
| Using the Properties window | p. 24 |
| Using the Immediate window | p. 26 |
| Using the Code window | p. 27 |
| Referring to Objects from VBA | p. 29 |
| Setting References to Object Libraries | p. 30 |
| Using the Object Browser | p. 30 |
| Searching the Object Library | p. 33 |
| Jumpstart: Creating a Simple VBA Program | p. 35 |
| Creating a Standard Module | p. 35 |
| Creating a Procedure | p. 36 |
| Understanding Syntax | p. 38 |
| Getting keyword help | p. 40 |
| Help with arguments | p. 43 |
| About named arguments | p. 45 |
| Using Existing Code | p. 46 |
| Copy and paste code from the Web | p. 46 |
| Importing modules | p. 47 |
| Modifying existing code | p. 48 |
| VBA foots and Techniques | p. 49 |
| Understanding Your VBA Building Blocks | p. 51 |
| Commenting Your Code | p. 52 |
| Understanding VBA Data Types | p. 53 |
| Passing Data to Procedures | p. 54 |
| Storing data in variables and constants | p. 57 |
| Storing data in arrays | p. 58 |
| Module-level versus procedure-level | p. 60 |
| Naming conventions for variables | p. 61 |
| Repeating Chunks of Code with Loops | p. 62 |
| Using Do...Loop to create a loop | p. 62 |
| Using While...Wend to create a loop | p. 64 |
| Using For...Next to create a loop | p. 64 |
| Making Decisions in VBA Code | p. 66 |
| Using If...End If statements | p. 67 |
| Using a Select Case block | p. 68 |
| Controlling Access through VBA | p. 71 |
| Understanding Object Models | p. 72 |
| Distinguishing between objects and collections | p. 72 |
| Understanding properties and methods | p. 75 |
| Identifying the icons for objects, properties, and methods | p. 77 |
| Manipulating Properties and Methods | p. 79 |
| Getting the value of a property | p. 79 |
| Changing the value of a property | p. 81 |
| Using an object's methods | p. 82 |
| Seeking help with properties and methods | p. 84 |
| Programming Access Forms | p. 87 |
| Working with Class Procedures | p. 87 |
| Enabling or Disabling Form Controls | p. 90 |
| Using VBA to position the cursor | p. 91 |
| Choosing an object and event for the code | p. 92 |
| Showing and hiding controls | p. 95 |
| Making controls read-only | p. 96 |
| Responding to Form Events | p. 96 |
| Changing the Appearance of Objects | p. 99 |
| Changing colors | p. 99 |
| Controlling boldface, italics, and such | p. 103 |
| Changing special effects | p. 104 |
| Using the With...End With statements | p. 104 |
| Filling form controls with data | p. 105 |
| Opening and Closing Forms | p. 107 |
| Closing a form | p. 109 |
| Adding a related record to another table | p. 109 |
| More DoCmd methods for forms | p. 112 |
| VBA, Recordsets, and SQL | p. 115 |
| The Scoop on SQL and Recordsets | p. 117 |
| What the Heck Is SQL? | p. 117 |
| Writing SQL without knowing SQL | p. 120 |
| Select queries versus action queries | p. 121 |
| Getting SQL into VBA | p. 123 |
| Hiding warning messages | p. 124 |
| Storing SQL statements in variables | p. 125 |
| Creating Tables from VBA | p. 128 |
| Creating new tables from existing tables | p. 128 |
| Creating a new, empty table from VBA | p. 129 |
| Closing and deleting tables through VBA | p. 130 |
| Adding Records to a Table | p. 131 |
| Appending a single record with SQL | p. 132 |
| Query to append one record | p. 133 |
| Changing and Deleting Table Records | p. 134 |
| Performing an Action Query on One Record | p. 136 |
| Working with Select Queries and Recordsets | p. 137 |
| Defining a connection | p. 140 |
| Defining the recordset and data source | p. 141 |
| Filling the recordset with data | p. 142 |
| Managing recordsets | p. 143 |
| Referring to fields in a recordset | p. 145 |
| Closing recordsets and collections | p. 146 |
| Putting Recordsets to Work | p. 147 |
| Looping through Collections | p. 147 |
| Using For Each loops | p. 149 |
| Using shorter names for objects | p. 152 |
| Tips on Reading and Modifying Code | p. 154 |
| Square brackets represent names | p. 154 |
| Other ways to refer to objects | p. 155 |
| The continuation character | p. 157 |
| Skipping Over Used Mailing Labels | p. 159 |
| Looking at How SkipLabels Works | p. 162 |
| Passing data to SkipLabels | p. 164 |
| Declaring variables | p. 165 |
| Copying the label report | p. 165 |
| Getting a report's recordsource | p. 165 |
| Creating the recordset | p. 166 |
| Creating LabelsTempTable from MyRecordSet | p. 166 |
| Calling a Procedure from an Event | p. 171 |
| Applying VBA in the Real World | p. 173 |
| Creating Your Own Dialog Boxes | p. 175 |
| Displaying and Responding to Messages | p. 176 |
| Asking a question | p. 176 |
| Designing a message box | p. 177 |
| Responding to a MsgBox button click | p. 180 |
| Converting Forms to Dialog Boxes | p. 182 |
| Storing dialog box settings | p. 183 |
| Setting form properties | p. 184 |
| Adding controls to the dialog box | p. 187 |
| Creating Custom Combo Boxes | p. 189 |
| Creating a Spin Box Control | p. 195 |
| Detecting a Right-Click | p. 198 |
| Customizing Combo Boxes and List Boxes | p. 201 |
| Programming Combo and List Boxes | p. 202 |
| Listing field names | p. 204 |
| Listing text options | p. 207 |
| Listing Table/Query field values | p. 212 |
| Linking Lists | p. 216 |
| Running code when a form opens | p. 218 |
| Running code when the user makes a choice | p. 219 |
| Linking Lists across Forms | p. 222 |
| Updating a combo box or a list box | p. 223 |
| Opening a form to enter a new record | p. 225 |
| Seeing whether a form is open | p. 226 |
| Getting forms in sync | p. 227 |
| More Combo Box Tricks | p. 228 |
| Using hidden values in combo and list boxes | p. 228 |
| Giving users a quick find | p. 232 |
| Avoiding retyping common entries | p. 235 |
| Creating Your Own Functions | p. 239 |
| The Role of Functions in VBA | p. 239 |
| Creating Your Own Functions | p. 241 |
| Passing data to a function | p. 242 |
| Returning a value from a function | p. 243 |
| Testing a custom function | p. 244 |
| A Proper Case Function | p. 245 |
| Looking at how PCase() works | p. 247 |
| Using the PCase() function | p. 248 |
| A Function to Print Check Amounts | p. 251 |
| Using the NumWord function | p. 254 |
| Looking at how NumWord() works | p. 256 |
| Testing and Debugging Your Code | p. 265 |
| Understanding Compilation and Runtime | p. 266 |
| Considering Types of Program Errors | p. 268 |
| Conquering Compile Errors | p. 269 |
| Expected: expression | p. 271 |
| Expected: end of statement | p. 272 |
| Expected: list separator or ) | p. 272 |
| Dealing with Logical Errors | p. 274 |
| Checking on variables with Debug.Print | p. 275 |
| Slowing down code | p. 279 |
| Getting back to normal in the Code window | p. 282 |
| Wrestling Runtime Errors | p. 283 |
| Responding to a runtime error | p. 283 |
| Trapping runtime errors | p. 285 |
| Writing your own error handlers | p. 288 |
| Reaching Out with VBA | p. 293 |
| Using VBA with Multiple Databases | p. 295 |
| Client-Server Microsoft Access | p. 296 |
| Importing from External Databases | p. 302 |
| Linking to External Data through Code | p. 304 |
| Avoiding Multiple Tables and Links | p. 305 |
| Creating Recordsets from External Tables | p. 308 |
| Importing, Exporting, or Linking to Anything | p. 309 |
| Using a macro to write the code | p. 309 |
| Quick and easy import/export/link | p. 312 |
| Integrating with Other Office Applications | p. 315 |
| Accessing the Object Library | p. 315 |
| Exploring a program's object model | p. 317 |
| Meet the Application object | p. 318 |
| Connecting to other programs | p. 319 |
| Sending E-Mail via Outlook | p. 320 |
| Sending Data to Microsoft Word | p. 325 |
| Creating the Word template | p. 325 |
| Creating the Access form | p. 327 |
| Writing the merge code | p. 328 |
| Interacting with Microsoft Excel | p. 334 |
| Creating the worksheet | p. 335 |
| Creating a query and a form | p. 336 |
| Writing the Excel code | p. 337 |
| Copying a table or query to a worksheet | p. 342 |
| Running Excel macros from Access | p. 346 |
| The Part of Tens | p. 349 |
| Ten Commandments of Writing VBA | p. 351 |
| Thou Shalt Not Harbor Strange Beliefs about Microsoft Access | p. 351 |
| Thou Shalt Not Use VBA Statements in Vain | p. 351 |
| Remember to Keep Holy the VBA Syntax | p. 352 |
| Honor Thy Parens and Quotation Marks | p. 353 |
| Thou Shalt Not Guess | p. 354 |
| Thou Shalt Not Commit Help Adultery | p. 354 |
| Thou Shalt Steal Whenever Possible | p. 355 |
| Thou Shalt Not Bear False Witness against Thy Object Browser | p. 355 |
| Thou Shalt Not Covet Thy Neighbor's Knowledge | p. 356 |
| Thou Shalt Not Scream | p. 356 |
| Top Ten Nerdy VBA Tricks | p. 357 |
| Open a Form from VBA | p. 357 |
| See Whether a Form Is Already Open | p. 358 |
| Refer to an Open Form | p. 358 |
| Move the Cursor to a Control | p. 359 |
| Change the Contents of a Control | p. 360 |
| Update a List Box or Combo Box | p. 360 |
| Show a Custom Message | p. 361 |
| Ask the User a Question | p. 362 |
| Print a Report | p. 363 |
| Get to Know the DoCmd Object | p. 364 |
| Index | p. 367 |
| Table of Contents provided by Ingram. All Rights Reserved. |
An electronic version of this book is available through VitalSource.
This book is viewable on PC, Mac, iPhone, iPad, iPod Touch, and most smartphones.
By purchasing, you will be able to view this book online, as well as download it, for the chosen number of days.
Digital License
You are licensing a digital product for a set duration. Durations are set forth in the product description, with "Lifetime" typically meaning five (5) years of online access and permanent download to a supported device. All licenses are non-transferable.
More details can be found here.
A downloadable version of this book is available through the eCampus Reader or compatible Adobe readers.
Applications are available on iOS, Android, PC, Mac, and Windows Mobile platforms.
Please view the compatibility matrix prior to purchase.