|
Microsoft Office Access 2007 VBA Business Solutions
Business Solutions
Microsoft(R) 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
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
|
