| |
| |
| Introduction | |
| |
| |
| Who This Book Is For | |
| |
| |
| What's in This Book | |
| |
| |
| Conventions Used in This Book | |
| |
| |
| The Sample Code for This Book | |
| |
| |
| Contacting the Authors | |
| |
| |
| |
| Laying the Foundation | |
| |
| |
| |
| Why Access? Why VBA? | |
| |
| |
| Understanding the Place of Access in Office | |
| |
| |
| Understanding Access Programming Choices | |
| |
| |
| Case Study | |
| |
| |
| |
| Getting Around in the Visual Basic Editor | |
| |
| |
| Your First Glance at the Visual Basic Editor | |
| |
| |
| Introducing the VBA Modules | |
| |
| |
| Entering and Running VBA Code | |
| |
| |
| Getting Help on Code | |
| |
| |
| Establishing Good Habits in Coding | |
| |
| |
| |
| Using Variables, Constants, and Data Types | |
| |
| |
| Declaring Variables and Constants | |
| |
| |
| VBA Data Types | |
| |
| |
| Referencing Syntax | |
| |
| |
| |
| Using Procedures | |
| |
| |
| Understanding Procedure Types | |
| |
| |
| Declaring Procedures as Public or Private | |
| |
| |
| Passing Arguments | |
| |
| |
| Giving a Function a Data Type | |
| |
| |
| Implementing Error Handling | |
| |
| |
| Debugging Code | |
| |
| |
| |
| Choosing the Right VBA Function | |
| |
| |
| Introducing VBA's Built-in Functions | |
| |
| |
| Converting Data Types with VBA Functions | |
| |
| |
| Working with Date Functions | |
| |
| |
| Using Mathematical and Financial Functions | |
| |
| |
| Using String Functions | |
| |
| |
| Using the Format Function | |
| |
| |
| Using the Is Functions for Flawless Processing | |
| |
| |
| Interacting Functions | |
| |
| |
| Case Study | |
| |
| |
| |
| Using Flow-of-Control Statements | |
| |
| |
| Branching and Looping | |
| |
| |
| Using If...Then...Else | |
| |
| |
| Using Select Case | |
| |
| |
| Using For...Next | |
| |
| |
| Using Do Loops | |
| |
| |
| Using Go To | |
| |
| |
| Case Study | |
| |
| |
| |
| Working with Arrays | |
| |
| |
| Introducing Array Variables | |
| |
| |
| Declaring an Array Variable | |
| |
| |
| Understanding the Array's Index | |
| |
| |
| Working with Array Elements | |
| |
| |
| Arrays with Multiple Dimensions | |
| |
| |
| Expanding to Dynamic Arrays | |
| |
| |
| |
| Understanding Objects | |
| |
| |
| Introducing Objects | |
| |
| |
| Reading and Setting Properties | |
| |
| |
| Invoking Methods | |
| |
| |
| Working with Collections | |
| |
| |
| Working with an Object Model | |
| |
| |
| Creating Your Own Objects | |
| |
| |
| Working with Events | |
| |
| |
| Case Study | |
| |
| |
| |
| Understanding Scope and Lifetime | |
| |
| |
| What's Scope? | |
| |
| |
| Measuring the Lifetime of a Variable or Constant | |
| |
| |
| Using Static Variables | |
| |
| |
| |
| Working with the Access User Interface | |
| |
| |
| |
| Working with Forms | |
| |
| |
| Opening and Closing Forms | |
| |
| |
| The Form Module and Event Handling | |
| |
| |
| Performing Common Tasks | |
| |
| |
| Handling Errors at the Form Level | |
| |
| |
| Working with Multiple Form Instances | |
| |
| |
| Case Study | |
| |
| |
| |
| Analyzing the Access Event Model | |
| |
| |
| Responding to Events | |
| |
| |
| The Event Sequence for Controls | |
| |
| |
| The Event Sequence for Forms | |
| |
| |
| The Event Sequence for Reports | |
| |
| |
| Canceling Events | |
| |
| |
| Case Study | |
| |
| |
| |
| Working with List and Combo Boxes | |
| |
| |
| Populating a List Control | |
| |
| |
| Adding to the List-or Not | |
| |
| |
| Working with Multiselect Controls | |
| |
| |
| Considering Callback Functions | |
| |
| |
| Case Study | |
| |
| |
| |
| Working with Other Controls | |
| |
| |
| Working with Text Boxes | |
| |
| |
| Using Controls in Option Groups | |
| |
| |
| Working with Subforms | |
| |
| |
| Working with the Tag Property | |
| |
| |
| Case Study | |
| |
| |
| |
| Working with Reports | |
| |
| |
| Introducing the Report Module and Events | |
| |
| |
| Opening and Closing Reports | |
| |
| |
| Passing Arguments Using OpenArgs | |
| |
| |
| Populating the Report | |
| |
| |
| Handling Report-Level Errors | |
| |
| |
| Using VBA to Determine Group Properties | |
| |
| |
| Case Study | |
| |
| |
| |
| Working with the Application Collections | |
| |
| |
| Investigating the Application Collections | |
| |
| |
| Retrieving Lists of Objects | |
| |
| |
| Working with Object Properties | |
| |
| |
| Programmatically Determining Dependencies | |
| |
| |
| Case Study | |
| |
| |
| |
| Working with Access Data | |
| |
| |
| |
| Retrieving Data with ADO | |
| |
| |
| What's ADO and Why Do You Need It? | |
| |
| |
| Using the ADO Connection Object | |
| |
| |
| Working with Command Objects | |
| |
| |
| Understanding the Different Types of Recordsets | |
| |
| |
| Creating and Opening a Recordset | |
| |
| |
| Filtering Recordsets | |
| |
| |
| Using the Recordset Property | |
| |
| |
| Case Study | |
| |
| |
| |
| Manipulating Data with ADO | |
| |
| |
| Moving Through a Recordset | |
| |
| |
| Finding Data in a Recordset | |
| |
| |
| Adding Data Using a Recordset | |
| |
| |
| Deleting Data in a Recordset | |
| |
| |
| Updating Data in a Recordset | |
| |
| |
| Using Transactions to Commit Groups of Records-or Not | |
| |
| |
| Case Study | |
| |
| |
| |
| Creating Objects with ADOX | |
| |
| |
| What Is ADOX? | |
| |
| |
| Creating Tables | |
| |
| |
| Securing Objects | |
| |
| |
| Case Study | |
| |
| |
| |
| Performing Advanced Data Operations | |
| |
| |
| Coding for Concurrency | |
| |
| |
| Retrieving a User Recordset | |
| |
| |
| Using Other Schema Recordsets | |
| |
| |
| Case Study | |
| |
| |
| |
| Using Advanced VBA Techniques in Access | |
| |
| |
| |
| Working with Data Files | |
| |
| |
| Understanding File I/O | |
| |
| |
| Opening Files | |
| |
| |
| Reading from Files | |
| |
| |
| Writing to Files | |
| |
| |
| Printing to Files | |
| |
| |
| Case Study | |
| |
| |
| |
| Automating Other Applications | |
| |
| |
| Understanding Automation | |
| |
| |
| Setting Object References | |
| |
| |
| Creating Objects in an Automation Server | |
| |
| |
| Talking to Excel from Access | |
| |
| |
| Talking to Word from Access | |
| |
| |
| Case Study | |
| |
| |
| |
| Working with XML Files | |
| |
| |
| An Introduction to XML | |
| |
| |
| Using ExportXML | |
| |
| |
| Using ImportXML | |
| |
| |
| Case Study | |
| |
| |
| |
| Using the Windows API | |
| |
| |
| Declaring API Calls | |
| |
| |
| Using API Calls | |
| |
| |
| API Calls That You Can Use From Access | |
| |
| |
| Knowing When to Use the Windows API | |
| |
| |
| |
| Appendix | |
| |
| |
| |
| Review of Access SQL | |
| |
| |
| An Introduction to SQL | |
| |
| |
| SQL Structure and Syntax | |
| |
| |
| Retrieving with SQL SELECT | |
| |
| |
| Modifying with SQL UPDATE | |
| |
| |
| Deleting with SQL DELETE | |
| |
| |
| Appending With SQL's INSERT INTO | |
| |
| |
| Making Tables With SQL SELECT INTO | |
| |
| |
| Creating a Crosstab Query with SQL TRANSFORM | |
| |
| |
| Index | |