Access 2007 VBA Programming For Dummies

Introduction     1About This Book     1Conventions Used in This Book     2What You're Not to Read     2Foolish Assumptions     3How This Book Is Organized     3Introducing VBA Programming     3VBA Tools and Techniques     3VBA, Recordsets, and SQL     4Applying VBA in the Real World     4Reaching Out with VBA     4The Part of Tens     4Icons Used in This Book     4Web Site for This Book     5Where to Go from Here     5Introducing VBA Programming     7Where VBA Fits In     9Taking a Look at Access     10Understanding VBA     11Seeing Where VBA Lurks     12Finding standard modules     13Finding class modules     13From VBA to Access     15Finding Out How VBA Works     17Discovering VBA procedures     17Recognizing VBA procedures     18Your VBA Toolkit     21Using the Visual Basic Editor     21Using Project Explorer     23Using the Properties window     24Using the Immediate window     26Using the Code window     27Referring to Objects from VBA     29Setting References to Object Libraries     30Using the Object Browser     30Searching the Object Library     33Jumpstart: Creating a Simple VBA Program     35Creating a Standard Module     35Creating a Procedure     36Understanding Syntax     38Getting keyword help     40Help with arguments     43About named arguments     45Using Existing Code     46Copy and paste code from the Web     46Importing modules     47Modifying existing code     48VBA foots and Techniques     49Understanding Your VBA Building Blocks     51Commenting Your Code     52Understanding VBA Data Types     53Passing Data to Procedures     54Storing data in variables and constants     57Storing data in arrays     58Module-level versus procedure-level     60Naming conventions for variables     61Repeating Chunks of Code with Loops     62Using Do...Loop to create a loop     62Using While...Wend to create a loop     64Using For...Next to create a loop     64Making Decisions in VBA Code     66Using If...End If statements     67Using a Select Case block     68Controlling Access through VBA     71Understanding Object Models     72Distinguishing between objects and collections     72Understanding properties and methods     75Identifying the icons for objects, properties, and methods     77Manipulating Properties and Methods     79Getting the value of a property     79Changing the value of a property     81Using an object's methods     82Seeking help with properties and methods     84Programming Access Forms     87Working with Class Procedures     87Enabling or Disabling Form Controls     90Using VBA to position the cursor     91Choosing an object and event for the code     92Showing and hiding controls     95Making controls read-only     96Responding to Form Events     96Changing the Appearance of Objects     99Changing colors      99Controlling boldface, italics, and such     103Changing special effects     104Using the With...End With statements     104Filling form controls with data     105Opening and Closing Forms     107Closing a form     109Adding a related record to another table     109More DoCmd methods for forms     112VBA, Recordsets, and SQL     115The Scoop on SQL and Recordsets     117What the Heck Is SQL?     117Writing SQL without knowing SQL     120Select queries versus action queries     121Getting SQL into VBA     123Hiding warning messages     124Storing SQL statements in variables     125Creating Tables from VBA     128Creating new tables from existing tables     128Creating a new, empty table from VBA     129Closing and deleting tables through VBA     130Adding Records to a Table     131Appending a single record with SQL     132Query to append one record     133Changing and Deleting Table Records     134Performing an Action Query on One Record     136Working with Select Queries and Recordsets     137Defining a connection     140Defining the recordset and data source     141Filling the recordset with data     142Managing recordsets     143Referring to fields in a recordset     145Closing recordsets and collections     146Putting Recordsets to Work     147Looping through Collections     147Using For Each loops     149Using shorter names for objects     152Tips on Reading and Modifying Code     154Square brackets represent names     154Other ways to refer to objects     155The continuation character     157Skipping Over Used Mailing Labels     159Looking at How SkipLabels Works     162Passing data to SkipLabels     164Declaring variables     165Copying the label report     165Getting a report's recordsource     165Creating the recordset     166Creating LabelsTempTable from MyRecordSet     166Calling a Procedure from an Event     171Applying VBA in the Real World     173Creating Your Own Dialog Boxes     175Displaying and Responding to Messages     176Asking a question     176Designing a message box     177Responding to a MsgBox button click     180Converting Forms to Dialog Boxes     182Storing dialog box settings     183Setting form properties     184Adding controls to the dialog box     187Creating Custom Combo Boxes     189Creating a Spin Box Control     195Detecting a Right-Click     198Customizing Combo Boxes and List Boxes     201Programming Combo and List Boxes     202Listing field names     204Listing text options     207Listing Table/Query field values     212Linking Lists     216Running code when a form opens     218Running code when the user makes a choice     219Linking Lists across Forms     222Updating a combo box or a list box     223Opening a form to enter a new record     225Seeing whether a form is open     226Getting forms in sync     227More Combo Box Tricks     228Using hidden values in combo and list boxes     228Giving users a quick find      232Avoiding retyping common entries     235Creating Your Own Functions     239The Role of Functions in VBA     239Creating Your Own Functions     241Passing data to a function     242Returning a value from a function     243Testing a custom function     244A Proper Case Function     245Looking at how PCase() works     247Using the PCase() function     248A Function to Print Check Amounts     251Using the NumWord function     254Looking at how NumWord() works     256Testing and Debugging Your Code     265Understanding Compilation and Runtime     266Considering Types of Program Errors     268Conquering Compile Errors     269Expected: expression     271Expected: end of statement     272Expected: list separator or )     272Dealing with Logical Errors     274Checking on variables with Debug.Print     275Slowing down code     279Getting back to normal in the Code window     282Wrestling Runtime Errors     283Responding to a runtime error      283Trapping runtime errors     285Writing your own error handlers     288Reaching Out with VBA     293Using VBA with Multiple Databases     295Client-Server Microsoft Access     296Importing from External Databases     302Linking to External Data through Code     304Avoiding Multiple Tables and Links     305Creating Recordsets from External Tables     308Importing, Exporting, or Linking to Anything     309Using a macro to write the code     309Quick and easy import/export/link     312Integrating with Other Office Applications     315Accessing the Object Library     315Exploring a program's object model     317Meet the Application object     318Connecting to other programs     319Sending E-Mail via Outlook     320Sending Data to Microsoft Word     325Creating the Word template     325Creating the Access form     327Writing the merge code     328Interacting with Microsoft Excel     334Creating the worksheet     335Creating a query and a form     336Writing the Excel code      337Copying a table or query to a worksheet     342Running Excel macros from Access     346The Part of Tens     349Ten Commandments of Writing VBA     351Thou Shalt Not Harbor Strange Beliefs about Microsoft Access     351Thou Shalt Not Use VBA Statements in Vain     351Remember to Keep Holy the VBA Syntax     352Honor Thy Parens and Quotation Marks     353Thou Shalt Not Guess     354Thou Shalt Not Commit Help Adultery     354Thou Shalt Steal Whenever Possible     355Thou Shalt Not Bear False Witness against Thy Object Browser     355Thou Shalt Not Covet Thy Neighbor's Knowledge     356Thou Shalt Not Scream     356Top Ten Nerdy VBA Tricks     357Open a Form from VBA     357See Whether a Form Is Already Open     358Refer to an Open Form     358Move the Cursor to a Control     359Change the Contents of a Control     360Update a List Box or Combo Box     360Show a Custom Message     361Ask the User a Question     362Print a Report     363Get to Know the DoCmd Object      364Index     367