Microsoft Office Access 2007 VBA

Scott B. Diamond has been an information technology geek for more than 20 years. He has spent much of that time designing databases on various platforms. He started using Microsoft Access with Office 97 and has mastered all the subsequent versions. Besides developing database applications for the company where he's employed as an applications administrator, Scott also does freelance work, developing Access applications and consulting. He has always maintained that he's lucky his vocation is also his avocation, so he spends some of his free time helping people on web-based Q&A boards such as utteraccess.com (the premier support site for Access). He recently received Microsoft's MVP award for Access in acknowledgment of his contribution to the Access community. Scott, an avid bicyclist, lives on Long Island, New York, with his wife and daughter. You can reach Scott at AccessVBA@diamondassoc.com or visit his website, www.diamondassoc.com. Brent Spaulding started writing applications about 20 years ago, generally focusing on data and data analysis. He has designed systems that have a wide range of focus: gymnastics class management, product assembly analysis, equipment fault logging, and manufacturing management systems. He has used Microsoft Access since version 2.0 and looks forward to using Access well into the future. In July 2007 Brent, who is employed in the automotive industry, received the Microsoft MVP award for Access, which recognizes his talent and contribution to the Access community. He spends much of his personal time learning and helping others on websites such as utteraccess.com, where he is known as datAdrenaline. Brent lives in southern Indiana with his wife and children.

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