Microsoft Access VBA programming for the absolute beginner

382 Pages·2007·5.08 MB·English

by  Michael (Michael Vine) Vine

Microsoft
®
Access VBA
™
Programming
for the Absolute
Beginner
Third Edition
MICHAEL VINE
© 2007 Thomson Course Technology, a division of Thomson Learning Publisher and General
Inc. All rights reserved. No part of this book may be reproduced or trans- Manager, Thomson Course
mitted in any form or by any means, electronic or mechanical, including Technology PTR:
photocopying, recording, or by any information storage or retrieval Stacy L. Hiquet
system without written permission from Thomson Course Technology
Associate Director of
PTR, except for the inclusion of brief quotations in a review.
Marketing:
The Thomson Course Technology PTR logo and related trade dress are Sarah O’Donnell
trademarks of Thomson Course Technology, a division of Thomson
Manager of Editorial
Learning Inc., and may not be used without written permission.
Services:
Microsoft, Access, and VBA are either registered trademarks or trade- Heather Talbot
marks of Microsoft Corporation in the United States and/or other
Marketing Manager:
countries.
Mark Hughes
All other trademarks are the property of their respective owners.
Acquisitions Editor:
Important: Thomson Course Technology PTR cannot provide software Mitzi Koontz
support. Please contact the appropriate software manufacturer’s
Marketing Assistant:
technical support line or Web site for assistance.
Adena Flitt
Thomson Course Technology PTR and the author have attempted
Project Editor:
throughout this book to distinguish proprietary trademarks from de-
Jenny Davidson
scriptive terms by following the capitalization style used by the
manufacturer. Technical Reviewer:
Keith Davenport
Information contained in this book has been obtained by Thomson
Course Technology PTR from sources believed to be reliable. However, PTR Editorial Services
because of the possibility of human or mechanical error by our sources, Coordinator:
Thomson Course Technology PTR, or others, the Publisher does not Erin Johnson
guarantee the accuracy, adequacy, or completeness of any information
and is not responsible for any errors or omissions or the results obtained Interior Layout Tech:
from use of such information. Readers should be particularly aware of Digital Publishing Solutions
the fact that the Internet is an ever-changing entity. Some facts may have
Cover Designer:
changed since this book went to press.
Mike Tanamachi
Educational facilities, companies, and organizations interested in
Indexer:
multiple copies or licensing of this book should contact the Publisher
Katherine Stimson
for quantity discount information. Training manuals, CD-ROMs, and
portions of this book are also available individually or can be tailored Proofreader:
for specific needs. Kim V. Benbow
ISBN-10: 1-59863-393-7
ISBN-13: 978-1-59863-393-1
eISBN-10: 1-59863-754-1
Library of Congress Catalog Card Number: 2007923301
Printed in the United States of America
07 08 09 10 11 TW 10 9 8 7 6 5 4 3 2 1
Thomson Course Technology PTR,
a division of Thomson Learning Inc.
25 Thomson Place
Boston, MA 02210
http://www.courseptr.com
To Sheila: 143
A
CKNOWLEDGMENTS
W
riting any book is not easy, especially a technical programming book. It
takes many great, patient, and talented people to write, edit, design, mar-
ket, finance, and produce a book. Without the assistance of Mitzi Koontz,
Jenny Davidson, and Keith Davenport, it would be impossible for me to share with
you my knowledge of programming in such a professional and fun manner.
A A
BOUT THE UTHOR
M
ichael Vine has taught computer programming, web design, and database
classes at Indiana University/Purdue University in Indianapolis, IN, and at
MTI College of Business and Technology in Sacramento, CA. Michael has
over 13 years’ experience in the information technology profession. He currently
works full time in a Fortune 100 company as an IT Project Manager overseeing the
development of enterprise data warehouses.
This page intentionally left blank
Contents
CHAPTER 1 An Invitation to Access 2007....................................1
What Is Microsoft Access?.....................................................................................................1
Microsoft Access 2007 Limitations.....................................................................................2
Microsoft Office Suites...........................................................................................................3
System Requirements............................................................................................................4
Working with Older Database Formats.............................................................................6
What’s New in Access 2007...................................................................................................6
User Interface...................................................................................................................7
Templates..........................................................................................................................9
Datasheet View..............................................................................................................11
Layout View....................................................................................................................12
Calendar..........................................................................................................................13
Rich Text..........................................................................................................................13
Split Forms......................................................................................................................14
Multivalued Fields........................................................................................................14
Data Types.......................................................................................................................15
File Format......................................................................................................................16
Help..................................................................................................................................16
Summary.................................................................................................................................17
CHAPTER 2 Access Essentials......................................................19
Database Normalization.....................................................................................................19
1st Normal Form............................................................................................................21
2nd Normal Form...........................................................................................................22
3rd Normal Form............................................................................................................23
Creating a New Access 2007 Database.............................................................................25
Tables and Fields...................................................................................................................26
Table Relationships..............................................................................................................31
Forms........................................................................................................................................35
Common Controls........................................................................................................37
Hungarian Notation.....................................................................................................42
Queries.....................................................................................................................................43
viii Microsoft Access VBA Programming for the Absolute Beginner
Summary.................................................................................................................................46
Programming Challenges...................................................................................................48
CHAPTER 3 Introduction to Access VBA..................................49
The Event-Driven Paradigm................................................................................................49
Object-Based Programming................................................................................................50
The VBA IDE............................................................................................................................51
Introduction to Event Procedures............................................................................53
Introduction to VBA Statements...............................................................................55
Accessing Objects and Their Properties..........................................................................56
The Forms Collection...................................................................................................57
The Me Keyword............................................................................................................58
Assignment Statements..............................................................................................59
Command and Label Objects.....................................................................................60
Getting User Input with Text Boxes.........................................................................65
Variables and Beginning Data Types...............................................................................67
Variable Naming Conventions..................................................................................70
Variable Scope................................................................................................................71
Option Statements........................................................................................................72
VBA Arithmetic and Order of Operations......................................................................73
Chapter Program: Fruit Stand...........................................................................................74
Summary.................................................................................................................................79
Programming Challenges...................................................................................................80
CHAPTER 4 Conditions.................................................................81
If Blocks...................................................................................................................................81
Nested If Blocks.............................................................................................................83
Compound If Blocks.....................................................................................................84
Select Case Structures..........................................................................................................87
Dialog Boxes...........................................................................................................................88
Message Box....................................................................................................................88
Input Box.........................................................................................................................91
Common Controls Continued...........................................................................................93
Option Group.................................................................................................................93
Option Buttons..............................................................................................................95
Check Boxes....................................................................................................................98
Toggle Buttons...............................................................................................................99
Chapter Program: Hangman............................................................................................101
Summary...............................................................................................................................107
Programming Challenges.................................................................................................108
Contents ix
CHAPTER 5 Looping Structures................................................109
Introduction to Looping Structures..............................................................................109
Do While.......................................................................................................................111
Do Until.........................................................................................................................112
Loop While....................................................................................................................113
Loop Until.....................................................................................................................114
For...................................................................................................................................114
List and Combo Boxes........................................................................................................116
Adding Items................................................................................................................117
Removing Items..........................................................................................................121
Managing Columns....................................................................................................122
Random Numbers...............................................................................................................124
Chapter Program: Math Quiz..........................................................................................126
Summary...............................................................................................................................129
Programming Challenges.................................................................................................130
CHAPTER 6 Common Formatting and Conversion
Functions..................................................................131
String-Based Functions......................................................................................................131
UCase..............................................................................................................................132
LCase..............................................................................................................................133
Len..................................................................................................................................133
StrComp........................................................................................................................134
Right...............................................................................................................................136
Left..................................................................................................................................137
Mid..................................................................................................................................137
InStr................................................................................................................................138
Date and Time Functions.................................................................................................139
Date................................................................................................................................139
Day..................................................................................................................................140
WeekDay.......................................................................................................................140
Month............................................................................................................................140
Year.................................................................................................................................140
Time................................................................................................................................140
Second............................................................................................................................141
Minute...........................................................................................................................141
Hour...............................................................................................................................141
Now.................................................................................................................................142
Conversion Functions........................................................................................................142
Val...................................................................................................................................142
Str....................................................................................................................................142