Building Microsoft Access Applications eBook

712 Pages·2004·38.6 MB·English

A01T620393_image.fm Page 1 Monday, December 20, 2004 3:20 PM
PUBLISHED BY
Microsoft Press
A Division of Microsoft Corporation
One Microsoft Way
Redmond, Washington 98052-6399
Copyright © 2005 by John L. Viescas
All rights reserved. No part of the contents of this book may be reproduced or transmitted in any form or
by any means without the written permission of the publisher.
Library of Congress Control Number [Pending]
Printed and bound in the United States of America.
1 2 3 4 5 6 7 8 9 QWT 9 8 7 6 5 4
Distributed in Canada by H.B. Fenn and Company Ltd.
A CIP catalogue record for this book is available from the British Library.
Microsoft Press books are available through booksellers and distributors worldwide. For further
information about international editions, contact your local Microsoft Corporation office or contact
Microsoft Press International directly at fax [phone]. Visit our Web site at www.microsoft.com/
learning/. Send comments to [email protected].
Microsoft, ActiveX, FrontPage, Georgia, Microsoft Press, Outlook, PivotChart, PivotTable, PowerPoint,
Tahoma, Verdana, Visual Basic, and Windows are either registered trademarks or trademarks of
Microsoft Corporation in the United States and/or other countries.
The example companies, organizations, products, domain names, e-mail addresses, logos, people, places,
and events depicted herein are fictitious. No association with any real company, organization, product,
domain name, e-mail address, logo, person, place, or event is intended or should be inferred.
Acquisitions Editor: Alex Blanton and Hilary Long
Project Editor: Kristine Haugseth
Production Manager: Curtis Philips, Publishing.com
Technical Editor: Steve Saunders
Copy Editor: Andrea Fox
Indexer: Rebecca Plunkett
Body Part No. X10-87117
620393.book Page iii Friday, December 17, 2004 6:28 PM
For Suzanne. You know I wouldn’t be able to do this without you.
620393.book Page iv Friday, December 17, 2004 6:28 PM
620393.book Page v Friday, December 17, 2004 6:28 PM
Contents at a Glance
Designing Your Application
Part I
1 Designing Your Tables. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3
2 Creating the User Interface . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 37
3 Designing a Client/Server Application. . . . . . . . . . . . . . . . . . . . . . . . . . . . 57
Building a Membership Tracking Application
Part II
4 The Membership Tracking Application . . . . . . . . . . . . . . . . . . . . . . . . . .115
5 Verifying Names . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .121
6 Tracking Member Activities. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .129
7 Tracking Member Status. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .137
8 Sending Out Notices. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .149
Creating an Inventory Management Application
Part III
9 The Inventory Management Application. . . . . . . . . . . . . . . . . . . . . . . . .173
10 Ordering Items from Inventory. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .183
11 Generating Purchase Orders . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .215
12 Posting Received Items into Inventory . . . . . . . . . . . . . . . . . . . . . . . . . .241
13 Creating Customer Invoices. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .281
14 Designing Sales and Inventory Reports. . . . . . . . . . . . . . . . . . . . . . . . . .297
Implementing a Customer Support Application
Part IV
15 The Customer Support Application . . . . . . . . . . . . . . . . . . . . . . . . . . . . .319
16 Organizing Customer Information. . . . . . . . . . . . . . . . . . . . . . . . . . . . . .327
17 Capturing Support Cases . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .349
18 Tracking Reminders. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .373
19 Reporting and Analyzing Support Cases. . . . . . . . . . . . . . . . . . . . . . . . .391
Creating a Registration Management Application
Part V
20 The Registration Management Application . . . . . . . . . . . . . . . . . . . . . .413
21 Scheduling Courses. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .423
22 Scheduling Students. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .465
v
620393.book Page vi Friday, December 17, 2004 6:28 PM
vi Contents at a Glance
23 Printing and E-Mailing Schedules. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .501
24 Producing Student Invoices. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .529
Appendixes
Part VI
A Recommended Reading. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .551
B Sample Database Schemas. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .555
C Function Reference . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .581
D Understanding SQL. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .587
E Implementing Generic Features . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .633
620393.book Page vii Friday, December 17, 2004 6:28 PM
Table of Contents
Acknowledgments . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xvii
About the CD. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .xix
What’s on the CD . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .xix
Sample Applications. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .xix
Using the CD. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .xxi
System Requirements. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .xxi
Support Information . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxii
Introduction. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .xxiii
Who Can Use This Book . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .xxiii
What’s in This Book . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .xxiv
Conventions Used in This Book and the Sample Databases . . . . . . . . . . . . . . . . . . . . xxvii
SQL Syntax Conventions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxvii
Object and Variable Naming Conventions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .xxviii
Naming Conventions Used for Access Objects. . . . . . . . . . . . . . . . . . . . . . . .xxviii
Naming Conventions for Access Form and Control Names . . . . . . . . . . . . . .xxix
Naming Conventions for Variables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxx
Designing Your Application
Part I
1 Designing Your Tables. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3
Avoiding Common Problems. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .4
Problem #1: Creating Compound Fields . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .4
Problem #2: Including Repeating Groups . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .5
Problem #3: Putting Multiple Subjects in One Table . . . . . . . . . . . . . . . . . . . . . .7
Problem #4: Oversimplifying Your Tables. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .8
Problem #5: Segmenting Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .10
Problem #6: Storing Calculated Values. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .10
Problem #7: Defining Field Lookup Properties. . . . . . . . . . . . . . . . . . . . . . . . . .11
Problem #8: Defining Validation Rules Without Validation Text. . . . . . . . . . . .14
Problem #9: Incorrectly Using Data Types. . . . . . . . . . . . . . . . . . . . . . . . . . . . . .15
Problem #10: Naming Fields Incorrectly . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .22
What do you think of this book? Microsoft is interested in hearing your feedback about this publication so we can
We want to hear from you! continually improve our books and learning resources for you. To participate in a brief
online survey, please visit: www.microsoft.com/learning/booksurvey/
vii
620393.book Page viii Friday, December 17, 2004 6:28 PM
viii Table of Contents
Using Good Design Techniques. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23
Avoid Allowing Both Null and Zero Length Values in Text. . . . . . . . . . . . . . . . 23
Use a Validation Rule Instead of Required = Yes . . . . . . . . . . . . . . . . . . . . . . . . 24
Never Define Subdatasheets for Tables (or Queries) . . . . . . . . . . . . . . . . . . . . . 25
Know That Referential Integrity Creates Extra Indexes . . . . . . . . . . . . . . . . . . . 25
Avoid Artificial Primary Keys in Tables. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 26
Turn Off Track Name AutoCorrect . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 30
Use the Description Property Wisely . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 32
Create Meaningful Captions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 32
Understand the Interaction Between Format and Input Mask. . . . . . . . . . . . . 32
The Bottom Line: Know Thy Data and Its Usage. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 35
2 Creating the User Interface . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 37
Designing Look and Feel . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 37
Using Colors. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 38
Selecting Fonts. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 44
Including Visual Clues. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 46
Dealing with Windows XP Themes in Access 2003 . . . . . . . . . . . . . . . . . . . . . . 47
Using Graphics. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 49
Architectural Issues . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 49
Sizing Forms. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 49
Planning Navigation. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 50
Creating Custom Menus and Toolbars. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 53
The Bottom Line: Know Your Users and Their Needs. . . . . . . . . . . . . . . . . . . . . . . . . . . 55
3 Designing a Client/Server Application. . . . . . . . . . . . . . . . . . . . . . . . . . . . 57
Understanding Access Client/Server Architectures . . . . . . . . . . . . . . . . . . . . . . . . . . . . 57
Programming for Client/Server Architecture . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 59
Filtering Forms and Reports. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 60
Filtering Lookup Values . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 63
Understanding Additional Optimizations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 73
Validating Links on Startup . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 80
Pushing the Envelope . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 85
Editing Data Locally. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 86
Using and Refreshing Local Lookup Tables. . . . . . . . . . . . . . . . . . . . . . . . . . . . 104
The Bottom Line: Keep the Wire “Cool”. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 110
620393.book Page ix Friday, December 17, 2004 6:28 PM
Table of Contents ix
Building a Membership Tracking Application
Part II
4 The Membership Tracking Application . . . . . . . . . . . . . . . . . . . . . . . . . .115
Understanding the Membership Tracking Application . . . . . . . . . . . . . . . . . . . . . . . .116
Identifying Tasks and Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .116
Understanding Business Rules . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .117
Designing the Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .119
5 Verifying Names . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .121
Detecting Duplicate Names . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .121
Ensuring Names Are in Proper Case . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .125
6 Tracking Member Activities. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .129
Logging Activities. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .129
Reporting Activities . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .132
7 Tracking Member Status. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .137
Entering Dues Rates and Payments. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .137
Detecting Dues Not Paid. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .142
Reporting Dues Status. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .145
8 Sending Out Notices. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .149
Printing Notices . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .149
Printing Dues Expiration Letters. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .149
Printing Meeting Announcements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .154
Sending Notices by E-Mail . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .157
Sending Dues Expiration Notices by E-Mail. . . . . . . . . . . . . . . . . . . . . . . . . . . .158
Sending a Meeting Announcement by E-Mail . . . . . . . . . . . . . . . . . . . . . . . . .164
Creating an Inventory Management Application
Part III
9 The Inventory Management Application. . . . . . . . . . . . . . . . . . . . . . . . .173
Understanding the Inventory Management Application. . . . . . . . . . . . . . . . . . . . . . .174
Identifying Tasks and Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .174
Understanding Business Rules . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .176
Designing the Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .179
620393.book Page x Friday, December 17, 2004 6:28 PM
x Table of Contents
10 Ordering Items from Inventory. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .183
Understanding the Tables Involved. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 183
Validating a Product Order Transaction. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 186
Preventing Users from Editing Processed Records. . . . . . . . . . . . . . . . . . . . . . 186
Validating the Price. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 189
Validating the Product Quantity. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 190
Validating a Product Request Before Saving It. . . . . . . . . . . . . . . . . . . . . . . . . 196
Verifying Rows to Be Deleted . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 198
Allocating Available Inventory. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 200
Returning and Ordering from the User’s Perspective . . . . . . . . . . . . . . . . . . . 200
Running the Allocation Process. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 201
Understanding the AllocateProducts Procedure . . . . . . . . . . . . . . . . . . . . . . . 203
11 Generating Purchase Orders . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .215
Understanding the Tables Involved. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 215
Validating a Purchase Order Transaction Row. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 217
Preventing Users from Editing Processed Rows . . . . . . . . . . . . . . . . . . . . . . . . 218
Validating the Quantity Requested. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 222
Validating a Product Request Before Saving It. . . . . . . . . . . . . . . . . . . . . . . . . 226
Verifying Rows to Be Deleted . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 228
Synchronizing Inserted Requests. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 229
Creating Purchase Orders for Customer Orders. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 231
Ordering for Stock. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 237
12 Posting Received Items into Inventory . . . . . . . . . . . . . . . . . . . . . . . . . .241
Entering Vendor Invoices . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 241
Controlling What the User Can Edit. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 244
Verifying Vendor ID Changes. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 247
Validating Final Status. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 248
Fixing a Changed Invoice Number. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 249
Validating an Invoice Before Saving It . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 251
Updating the Displayed Total . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 252
Controlling Invoice Deletion . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 254
Validating Changes in Related Purchase Order Products Rows . . . . . . . . . . . 255
Posting to Inventory and Customer Orders. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 263
Triggering the Posting Process . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 265
Posting Received Products. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 266