Microsoft Office Access 2007 VBA

by Diamond, Scott B.; Spaulding, Brent

• ISBN13: 9780789737311
• ISBN10: 0789737310
• Edition: 1st
• Format: Paperback
• Copyright: 2007-11-27
• Publisher: Que Publishing

Summary

An indispensible, well-organized reference and guide to developing Access applications and automating daily tasks.

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

The Building Blocks

Advantages of Access and VBA Understanding Where Access Fits in Office Understanding Access Programming Choices

Macros

Using SQL

Using VBA

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

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

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

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

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

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

Object and Event-Driven Coding Understanding Objects

Creating Objects in Code

Reading and Set

Table of Contents provided by Publisher. All Rights Reserved.

Excerpts

= 0) {slash = '\\';} else {slash = '/';}openLoc = figLoc.substring(0, figLoc.lastIndexOf(slash) + 1);while (pPage.substring(0,3) == '../') {openLoc = openLoc.substring(0, openLoc.lastIndexOf(slash, openLoc.length - 2)+ 1);pPage = pPage.substring(3, pPage.length + 1);}popUpWin =window.open('','popWin','resizable=1,scrollbars=1,location=0,toolbar=0,width=525,height=394');figDoc = popUpWin.document;zhtm= ' ' + pPage + ' ';zhtm += ' ';zhtm += ' ';zhtm += ' ';zhtm += '' + pPage.substring(pPage.lastIndexOf('/') + 1, pPage.length) + '';zhtm += ' ';figDoc.write(zhtm);figDoc.close();}// modified 3.1.99 RWE v4.1 --> Introduction Introduction What's in the Book This Book's Special Features The Examples Used in the BookSo, you've been using Access for a little while. Now that you have used Access to build databases for yourself, and maybe some friends and/or colleagues, you are ready for the next step: developing automated database applications. If you want quicker, easier, and more accurate data entry; faster searching; better reporting; the ability to manipulate data behind the scenes; and much more, this book is for you.WithMicrosoft Office Access 2007 VBAwe show you how to unleash the power of Access using Visual Basic for Applications (VBA). VBA is a superset of the Visual Basic programming language that combines Visual Basic command syntax and a rich assortment of functions with the capability to control objects in your application (hence the "A" in "VBA").This book shows you VBA in action with real-world examples. We introduce you to programming and its use within Access and hold your hand every step of the way. With the information provided in this book you will explore commands, functions, properties, and methods and how to use them to make your applications dance to your tune and jump through hoops. What's in the BookThis book isn't meant to be read from cover to cover, although you may find that you can't put it down! Instead, most of the chapters are set up as self-contained units that you can dip into and extract whatever nuggets of information you need at will. If you're a relatively new Access user, I suggest starting with the first one or two chapters in each of the book's four main parts to ensure you have a solid foundation in the fundamentals of working with data in Access tables by using queries, forms, and reports.The book is divided into four main parts. To give you the big picture before diving in, here's a summary of what you find in each part:Part I, "The Building Blocks"--The nine chapters in Part I i