Microsoft Office Access 2007 VBA

by Diamond, Scott B.; Spaulding, Brent

• ISBN13: 9780132714136
• ISBN10: 0132714132
• Edition: 1st
• Copyright: 2003-11-26
• Publisher: Que Publishing

Summary

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

 

Author Biography

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 [e-mail] 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.

 

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