Business Solutions   Microsoft® Office Access 2007 VBA   Develop your Access 2007 VBA expertise instantly with proven techniques   Microsoft Office Access 2007 VBA builds on the skills you’ve already developed in creating database applications and helps you take them to the next level—using Visual Basic for Applications (VBA) to accomplish things you once performed manually. To facilitate this lofty goal, Access includes the VBA programming language. Even if you’ve never programmed, this book will help you learn how to leverage the power of VBA to make your work with Access more efficient than ever before. Microsoft Office Access 2007 VBA is for professionals who use Microsoft Access frequently in their daily work. You have serious work to get done and you can’t spend all day reading a computer book. This book teaches you the essential skills you need to automate your databases as quickly as possible. Although written for Access 2007, the techniques and concepts covered will work in most versions of Microsoft Access.   Highlights of This Book Include           •        Navigating within the Visual Basic Editor           •        Using variables, constants, and data types           •        Employing built-in functions           •        Creating procedures           •        Understanding object-and event-driven coding           •        Working with arrays           •        Understanding scope           •        Working with forms           •        Using selection controls           •        Creating reports           •        Exploring menus, navigation, and ribbons           •        Using object models           •        Working with data           •        Defining database schema           •        Using the Windows API           •        Working with XML files           •        Exploring Access SQL   On the Website Download database files used in the book at www.quepublishing.com.   Category     Office Applications Covers         Visual Basic for Applications  User Level    Intermediate - Advanced   Scott B. Diamond is a seasoned database designer and Microsoft Access 2007 MVP. During the last 20+ years, he has designed databases on a wide range of platforms, including dBASE, FoxPro, SQL/DS, Lotus Approach, Lotus Notes, and, for the past 10 years, Microsoft Access. Scott has worked as a consultant, both in-house and freelance, and as a support professional at firms that are among the leaders in their industries. Scott spends some of his free time answering questions at the premier site for Access support: http://www.utteraccess.com.   Brent Spaulding started writing applications about 20 years ago and has utilized Microsoft Access since version 2.0. He looks forward to using Access well into the future. In July 2007, he received the Microsoft MVP award for Access, which recognizes his talent and contributions to the Access community.   Front cover bullets: Edit and debug your code Use looping and conditional statements Understand the Access object- and event-driven architecture Automate data entry Learn how to use variables for dynamic automation Create user-friendly applications for others Create custom functions and objects Customize the user interface Manipulate data and objects with code

Table of Contents:
  Introduction Part I The Building Blocks Chapter 1 Advantages of Access and VBA Understanding Where Access Fits in Office Understanding Access Programming Choices     Macros     Using SQL     Using VBA Chapter 2 Using the Visual Basic Editor First Look at the Visual Basic Editor Explaining VBA Modules Entering and Running Code     Debugging Code     Saving Code Getting Help on Code     Coding Shortcuts Good Coding Habits     Using a Naming Convention     Indenting     Documenting Chapter 3 Using Variables, Constants, and Data Types Declaring Variables and Constants     Declaring Variables     Using Option Explicit     Naming Variables     Constants     Declaring Constants VBA Data Types Referencing Syntax Case Study:Using Form References Chapter 4 Using Built-In Functions What Are Functions? Converting Data Types     Converting to a Boolean Data Type     Converting to a Date Data Type     Converting to an Integer Data Type     Converting to a String Data Type     Converting to a Variant Data Type     Converting Null Values Working with Date Functions     Returning the Current Date     Performing Date Arithmetic     Determining the Difference Between Two Dates     Extracting Parts of Dates     Creating Dates from the Individual Parts     Creating Dates from String Values     Extracting a Specific Date or Time Portion     A Conversion and Date Example Using Mathematical Functions     The Abs Function     The Int Function     The Rnd Function     A Mathematical Functions Example Using Financial Functions     The Ddb Function     The FV Function     The Pmt Function     The Rate Function     A Financial Functions Example Manipulating Text Strings     The Asc Function     The Chr Function     The Case Functions     The Len Function     The Left, Right, and Mid Functions     The Replace Function     The Split Function     The Trim Functions Formatting Values     Applying User-Defined Formats Domain Aggregate Functions     The DLookup Function     The DCount Function     The DMax/DMin Functions Using the Is Functions Interaction     The MsgBox Function     The InputBox Function Case Study:Add Work Days Chapter 5 Building Procedures Types of Procedures     Subroutines     Functions     Assigning a Data Type to a Function     Public Versus Private Passing Arguments     Using Optional Arguments and Default Values     Passing Arguments By Reference     Passing Arguments By Value Error Handling     Using On Error Resume Next     Using On Error Goto Chapter 6 Conditional and Looping Statements Introducing Flow of Control Statements Using If...Then...Else     A Simple If Statement     More Complex Conditions     Including an Else Clause     Including an ElseIf Clause Using Select Case Using For...Next     Using the Step Clause     Other Ways to Set the Counter     Nesting For...Next Loops     Aborting a For...Next Loop Using Do Loops     A Simple Do Loop     Do Loop Flavors     Aborting a Do Loop Using GoTo Case Study: Calculating Bonuses Chapter 7 Working with Arrays Introducing Arrays Declaring a Fixed-Size Array Understanding an Array’s Index     Using Option Base Working with Array Elements     Assigning Array Elements     Using Array Element Values Arrays with Multiple Dimensions Expanding to Dynamic Arrays     About ReDim     Erase Statement Chapter 8 Object and Event-Driven Coding Understanding Objects     Creating Objects in Code     Reading and Setting Object Properties Invoking Methods Using Collections Working with an Object Model     Using the Object Model     Using References     The Object Browser Creating Objects Working with Events Chapter 9 Understanding Scope and Lifetime Scope Explained     Procedure-Level Variables     Module-Level Variables and Constants     Public Variables and Constants Measuring the Lifetime of a Variable or Constant     The Lifetime of a Procedure-Level Variable     The Lifetime of a Module-Level Variable     The Lifetime of a Public Variable Using Static Variables Case Study:Tracking the Current User Part II Working Within the User Interface Chapter 10 Working with Forms Opening and Closing Forms     Opening a Form     Passing Arguments Using OpenArgs     Closing a Form The Form Module Form and Control Properties Form Events Case Study:Adding to a Combo Box Chapter 11 More on Event-Driven Coding Responding to Events The Event Sequence for Controls     Focus Events     Data Events     Control Specific Events The Event Sequence for Forms     Navigation Events     Data Events     Behind the Scenes: Data Buffers The Event Sequence for Reports Cancelling Events Case Study:Validating Data Chapter 12 Working with Selection Controls Selection Controls Populating a List Control     A Filtering List Control Adding to the List—Or Not     Updating a Table/Query List Working with Option Groups Working with MultiSelect Controls     Determining What Is and Isn’t Selected Case Study: Selecting Multiple Items Chapter 13 Working with Other Controls Working with Text Boxes     Key Properties of Text Boxes     Tracking the Focus Working with Check Boxes, Radio Buttons, or Toggle Buttons Working with Subforms Working with the Tag Property Case Study: An Audit Trail Chapter 14 Working with Reports An introduction to the Report Module and Events Opening and Closing Reports     Opening a Report     Closing a Report Passing Argument Using OpenArgs Populating the Report     Applying a Filter and Sort Order Handling Report-Level Errors     What to Do When There Is No Data Working with Subreports Case-Study: Product Catalog Chapter 15 Menus, Navigation, and Ribbons Introducing Menus Creating Form-Based Menus Managing the Navigation Pane Using Custom Ribbons Chapter 16 Application Collections Understanding Application Collections Retrieving Lists of Objects Working with Object Properties Programmatically Determining Dependencies Case Study:Version Control Part III Working with Data Chapter 17 Object Models for Working with Data What They Are and Why We Need Them Data Access Objects ActiveX Data Objects ActiveX Data Objects Extensions for Data Definition Object Model Selection Chapter 18 Creating Schema Overview Creating Databases     Using the DAO Object Model     Using the ADOX Object Model Creating Tables     Using the DAO Object Model     Using the ADOX Object Model Creating Fields     Using the DAO Object Model     Using the ADOX Object Model Creating Indexes     Using the DAO Object Model     Using the ADOX Object Model Creating Relationships     Using the DAO Object Model     Using the ADOX Object Model Creating Queries     Using the DAO Object Model     Using the ADOX Object Model Case Study: Updating an Existing Database Installation Chapter 19 Data Manipulation Connecting to a Data Source     Using the DAO Object Model     Using the ADO Object Model Opening a Recordset     Using the DAO Object Model     Using the ADO Object Model Inserting Data     DAO’S Execute Method     ADO’s Execute Method     DAO’S AddNew Method     ADO’S AddNew Method Finding Data     Limiting Records Retrieved     DAO’s FindFirst, FindNext, FindLast, and FindPrevious Methods     DAO’s Seek Method     Using DAO’s Filter Method     Using ADO’s Find Method     Using ADO’s Seek Method     Using ADO’s Filter Property Updating Data Deleting Data     DAO’S Delete Method for a Recordset Object     ADO’s Delete Method for a Recordset Object Case Study: Backing Up Data Chapter 20 Advanced Data Operations Creating Linked Tables Data Definition Language Schema Recordsets Subqueries Part IV Advanced VBA Chapter 21 Working with Other Data Files Understanding File I/O Opening Files     About mode     About access     About locking     Demonstrating Opening a File Reading from Files     Using Input     Using Line Input #     Using Input # Writing to Files Printing to Files Case Study:Using .ini Files Chapter 22 Working with Other Applications Understanding Automation Setting Object References Creating Objects     Using CreateObject     Using GetObject     Using Early Binding Working with Automation Servers     Talking To Excel     Talking to Word Case Study:Using Excel Charts Chapter 23 Working with XML Files Understanding XML Using ExportXML     An Example of Exporting     Exporting a Web-Ready File     Exporting Related Data Using ImportXML     An Import Example Chapter 24 Using the Windows API Declaring API Calls Using API Calls API Calls You Can Use from Access     Check Whether an Application Is Loaded     Capture the Network Login ID     Retrieving the Name of the Program Associated with a Data File Knowing When to Use the Windows API Case Study: Capturing a Filename to Use for Processing Appendix A Review of Access SQL Introduction to SQL SQL Structure and Syntax The SELECT Statement     The SQL Predicates     The SQL FROM Clause     The SQL WHERE Clause     The SQL ORDER BY Clause     The SQL GROUP BY Clause     The SQL HAVING Clause The INSERT Statement The UPDATE Statement The SELECT INTO Statement The DELETE Statement Crosstabs   0789737318    TOC    10/31/2007