Access 2002 Vba Handbook

by Susann Novalis; Dana Jones

• ISBN13: 9780782140132
• ISBN10: 0782140130
• Edition: CD
• Format: Paperback
• Copyright: 2001-11-01
• Publisher: JOHN WILEY & SONS

Summary

How Far Can You Go with Access 2002? The answer depends on your knowledge of VBA programming. If you really want to get the most out of Access, the Access 2002 VBA Handbook is essential reading. If you are an experienced user of Access, you get the step-by-step instruction you need to be dramatically more productive. And if you are a more experienced developer, you get the advanced VBA programming skills you need to build the Access applications your users require. Coverage includes * Running queries to find specific records * Automating complex and time-consuming procedures * Importing data from existing data sources * Synchronizing forms and tables * Understanding the ADO and DAO models * Understanding the essentials of Visual Basic for Applications (VBA) * Customizing the Access user interface * Creating procedures to change Access's default error-handling processes * Accessing data using OLE DB and ODBC * Creating VBA procedures for navigating a database or project * Writing VBA procedures using object properties and methods * Creating relationships between forms * Executing commands using SQL statements in VBA code * Using ActiveX controls in your VBA procedures

Author Biography

Susann Novalis, Ph.D., is a Professor of Mathematics and Associate Dean of the College of Science and Engineering at San Francisco State University. She is the author of Access 2000 VBA Handbook and Access 97 Macro & VBA Handbook, both from Sybex. She has also published articles in Access Visual Basic Advisor and Internet Advisor. Dana Jones is a consultant with MCW Technologies, a Microsoft Solution Provider, and has written for SmartAccess.

Table of Contents

Introduction	xxv
Automating a Database without Programming	1	(72)
Using the Task Pane	3	(1)
Using the Database Wizard	4	(18)
Creating Navigation Paths with Switchboards	5	(4)
Exploring the Application	9	(4)
Examining the Wizard's Work	13	(9)
Using the Command Button Wizard	22	(3)
Using Hyperlinks to Navigate	25	(9)
Storing Hyperlinks as Data in a Table	27	(2)
Using a Hyperlink as an Unbound Form Control	29	(5)
Using the Combo Box Wizard	34	(3)
Using the Switchboard Manager	37	(3)
Using the Microsoft Exchange/Outlook Wizard	40	(8)
Using the Link Spreadsheet and Link Text Wizards	48	(6)
Controlling the User Interface	54	(16)
Setting Startup Properties	54	(4)
Protecting Your Application with a Password	58	(2)
Creating Custom Menus and Toolbars	60	(9)
Reviewing Our Automated Access Application	69	(1)
Beyond Wizards and Helpers	70	(2)
Summary	72	(1)
Getting Started with Objects and Events	73	(34)
Naming Objects	74	(6)
Self-Documenting Names	75	(2)
Names in VBA Programming	77	(2)
What the User Sees	79	(1)
Name Changes	80	(1)
Describing an Object's Properties	81	(8)
Design-Time Properties	81	(2)
Run-Time Properties in VBA Programming	83	(1)
Read-Only Properties	84	(1)
Other Property Sheets	84	(5)
Manipulating Objects	89	(4)
The Lifetime of Objects	90	(1)
Using Macros	90	(2)
Using VBA Procedures	92	(1)
Access Events	93	(9)
The Access Programming Model	94	(1)
Sequences of Events	95	(5)
Canceling Default Behavior	100	(2)
Getting Hands-On Experience with Events	102	(4)
Summary	106	(1)
Introducing the Access Object Model	107	(40)
Relating Objects to Each Other	108	(5)
Similar Objects	108	(2)
Objects Containing Other Objects	110	(3)
Introducing the Architecture of Access	113	(5)
The Application Layer	113	(1)
The Jet Database Engine	114	(1)
The Microsoft SQL Server 2000 Desktop Engine	115	(1)
Object Hierarchies	115	(3)
Understanding the Access Application Object Model	118	(13)
The Application Object	118	(1)
The Forms, Reports, DataAccessPages, and Controls Collection Objects	119	(1)
The Form, Report, and DataAccessPage Objects	120	(3)
The Control Object	123	(6)
The Screen Object	129	(1)
The Access Visual Basic Object Model	129	(2)
Referring to Objects and Properties by Name	131	(10)
Referring to an Object by Name	131	(1)
Referring to a Form or Report	132	(1)
Referring to Form and Report Properties	133	(1)
Using the Immediate Window to Evaluate an Object Property	133	(1)
Referring to a Control	134	(1)
Referring to a Control's Properties	135	(1)
Properties That Represent Other Objects	136	(1)
Referring to a Subform	136	(3)
Referring to Controls on the Active Form or Report	139	(1)
Using the Screen Object to Refer to the Active Object	139	(1)
Referring to a Field	140	(1)
Using the Expression Builder to Create References	141	(4)
Summary	145	(2)
Communicating with Forms	147	(44)
Relating Forms and Controls to Data	149	(11)
Retrieving a Recordset	150	(3)
The Record Source for a Form or Report	153	(1)
The Control Source for a Control	154	(1)
Controls without a ControlSource Property	155	(2)
The One Form-One Record Source Model	157	(1)
AutoLookup Queries	157	(1)
Subdatasheets	158	(2)
Communicating between Controls and Fields	160	(19)
Using Calculated Query Fields	162	(1)
Using Calculated Form Controls	163	(10)
Using an Unbound Control as a Variable	173	(1)
Saving a Calculated Result to the Database	174	(1)
Using a Combo Box or List Box to Look Up Information	174	(5)
Synchronizing Forms	179	(10)
Using the Form Wizard to Synchronize Two Forms	180	(3)
Using the Form/Subform Technique to Synchronize Forms	183	(6)
Summary	189	(2)
VBA Programming Essentials	191	(50)
Basic Programming Concepts	192	(1)
Objects, Properties, and Methods	193	(8)
Setting Properties	194	(2)
Getting Properties	196	(2)
Calling Methods	198	(3)
VBA-Only Form and Report Features	201	(7)
Understanding Record Sources and Recordsets	201	(1)
Creating a Second Current Record Pointer	202	(3)
Using Bookmarks	205	(2)
Using Me to Refer to a Form or Report	207	(1)
References to Objects in Collections	208	(3)
Using Index by Name	209	(1)
Using Index by Variable	209	(1)
Using Index by Position	210	(1)
Using Index by Number	210	(1)
The Access Application Object Hierarchy	211	(24)
The Application Object	211	(1)
The Collection Objects	211	(4)
The Form Object	215	(1)
The Report Object	215	(1)
The Module Object	215	(5)
Control Objects	220	(7)
The Screen Object	227	(1)
The DoCmd Object	227	(3)
The Properties Collection	230	(1)
The Property Object	231	(1)
The References Collection	231	(1)
The Reference Object	232	(1)
The Access VBA Objects	233	(2)
Using the Object Browser	235	(4)
Summary	239	(2)
Understanding the ADO Object Model	241	(48)
Access Database Management Services	242	(2)
The ActiveX Data Objects (ADO) Hierarchy	244	(2)
The ADO Extensions for DDL and Security (ADOX) Hierarchy	246	(1)
ADO Object References	246	(4)
Using Default Collections	249	(1)
Using the CurrentProject Property	249	(1)
Two Recordsets Opened Simultaneously	250	(3)
New Data Access Objects	253	(9)
Creating Databases	253	(1)
Creating Tables	254	(1)
Creating Indexes	255	(2)
Creating Queries	257	(1)
Creating Recordsets	258	(4)
ADO Recordset Object Manipulation	262	(10)
Using Physical Navigation	262	(3)
Using Logical Navigation	265	(3)
Adding, Editing, and Deleting Records	268	(3)
Cloning Recordsets	271	(1)
The ADO Object Model	272	(10)
The Connection Object	272	(2)
The Command Object	274	(1)
The Parameter Object	274	(1)
The Recordset Object	275	(4)
The Field Object	279	(2)
The Error Object	281	(1)
The Property Object	281	(1)
The Record Object	282	(1)
The Stream Object	282	(1)
The ADOX Object Model	282	(5)
The Catalog Object	282	(1)
The Table Object	283	(1)
The Column Object	283	(1)
The Index Object	283	(1)
The Key Object	284	(1)
The Group Object	285	(1)
The User Object	286	(1)
The Procedure Object	286	(1)
The Command Object	287	(1)
The View Object	287	(1)
Summary	287	(2)
Writing Procedures	289	(56)
VBA Statements and Procedures	290	(1)
Data Types	291	(10)
Fundamental Data Types	291	(2)
The Variant Data Type	293	(6)
The Object Data Type	299	(2)
Procedures and Modules	301	(16)
Types of Procedures	301	(3)
Procedure Calls	304	(1)
Modules	304	(2)
Procedure References	306	(1)
Public versus Private Procedures	307	(1)
Simple Examples of Procedures	308	(9)
The Access VBA Programming Environment	317	(14)
Displaying a Module in Module View	317	(4)
Working in Module View	321	(4)
Setting Visual Basic Editor Options	325	(2)
Following Good Programming Style	327	(1)
Using the Access VBA Compiler	328	(1)
Saving a Database without Source Code	329	(2)
Methods for Running Procedures	331	(11)
Running Function Procedures	331	(9)
Running Sub Procedures	340	(2)
Summary	342	(3)
Using Variables	345	(52)
Using Variables in Procedures	346	(9)
Using Variables to Create Reusable Code	347	(4)
Using Variables for Faster Code	351	(3)
How Procedures Use Variables	354	(1)
Declaring Variables	355	(5)
Naming Constants and Variables	356	(1)
Specifying a Data Type	356	(1)
Understanding the Life Cycle of a Variable	357	(3)
Using Procedure-Level Variables	360	(17)
Declaring a Variable within the Procedure	360	(1)
Declaring a Variable in the Argument List	361	(1)
Understanding the Visibility of Procedure-Level Variables	362	(2)
Changing the Lifetime of Procedure-Level Variables	364	(2)
Passing Data to a Procedure	366	(4)
Passing Arguments to a Procedure	370	(6)
Passing Data to an Event Procedure	376	(1)
Using Module-Level Variables	377	(5)
Understanding the Visibility of Module-Level Variables	380	(1)
Exploring the Lifetime of Module-Level Variables	380	(2)
Using Constants	382	(4)
Using Intrinsic Constants	382	(3)
Creating Your Own Constants	385	(1)
Using Arrays	386	(7)
Creating Fixed-Size Arrays	387	(3)
Creating Dynamic Arrays	390	(2)
Using Arrays as Arguments	392	(1)
Creating Your Own Data Types	393	(2)
Summary	395	(2)
Controlling Execution	397	(32)
Understanding Control Structures	398	(1)
Making Decisions with Conditions	398	(8)
Using the If...Then Structure	399	(1)
Using the If...Then...Else Structure	400	(4)
Using the Select Case Structure	404	(2)
Using Loops for Repetitive Operations	406	(11)
Using the For...Next Structure	407	(2)
Using the For Each...Next Structure	409	(3)
Using the Do...Loop Structure	412	(4)
Looping through a Recordset	416	(1)
Nesting Control Structures	417	(1)
Abbreviating Object References	418	(2)
Introducing Some Useful Statements and Functions	420	(7)
Using Toggle Statements	420	(1)
Using Exit Statements	421	(1)
Using the Timer Function	421	(1)
Using the DoEvents Function	422	(1)
Using the MsgBox and InputBox Functions	423	(1)
Using the SysCmd Function	424	(3)
Summary	427	(2)
Dealing with Errors in VBA	429	(34)
Avoidable and Unavoidable Errors	430	(3)
Avoidable Errors	430	(1)
Unavoidable Errors	431	(2)
The VBA Compiler	433	(5)
Automatic Compiling	434	(3)
Explicit Compiling	437	(1)
Decompiling	438	(1)
Troubleshooting Tools	438	(11)
Entering and Leaving Break Mode	438	(2)
Testing in Break Mode	440	(1)
Reinitializing Code	441	(1)
Setting and Removing Breakpoints	441	(1)
Stepping through Code	442	(1)
Setting the Next Statement	443	(1)
Executing a Group of Statements	443	(1)
Viewing Current Values in the Module Window	443	(1)
Using the Locals Window in Break Mode	444	(1)
Using the Watches Window	445	(1)
Using Quick Watch in Break Mode	446	(2)
Using the Call Stack Dialog	448	(1)
Error Handling	449	(11)
Trappable Errors	449	(1)
Custom Error Messages	450	(1)
Database Engine and Interface Errors	450	(3)
VBA Errors	453	(7)
Errors in Called Procedures	460	(1)
Summary	460	(3)
Navigating with Access VBA	463	(50)
Navigating the Form and Its Recordset	464	(4)
Creating a Sub Procedure to Walk the Recordset	465	(2)
Creating a Function Procedure to Walk the Recordset	467	(1)
Navigating the Interface	468	(13)
Navigating Forms	468	(8)
Navigating between Controls	476	(5)
Navigating among Records of a Form	481	(8)
Creating Custom Navigation Buttons	482	(1)
Dealing with Run-Time Errors	483	(6)
Finding a Specific Record	489	(5)
Using the FindRecord Method of the DoCmd Object	490	(1)
Using the ApplyFilter Method of the DoCmd Object	490	(1)
Using the Recordsetclone	491	(2)
Undoing the Search	493	(1)
Working with the Data in Tables	494	(17)
Creating Recordset Variables	495	(3)
Navigating Recordsets	498	(3)
Finding a Specific Record	501	(6)
Exploring Clones	507	(1)
Reading Table Data into an Array	508	(3)
Summary	511	(2)
Maintaining Data with Access VBA	513	(28)
Maintaining Data Using Forms	514	(20)
Toggling a Form between Review and Data-Entry Modes	514	(2)
Validating Data	516	(5)
Adding Command Buttons for Data-Entry Operations	521	(8)
Carrying Values Forward to a New Record	529	(5)
Editing Data in a Recordset	534	(5)
Changing a Record	534	(2)
Adding a Record	536	(2)
Deleting a Record	538	(1)
Summary	539	(2)
Working with Groups of Records Using Access VBA	541	(56)
Sorting Records in a Form or Report	542	(8)
Putting Records in Ascending or Descending Order	543	(3)
Sorting by Any Column	546	(2)
Performing a Two-Tier Sort	548	(2)
Selecting Groups of Records in a Form or Report	550	(21)
Opening a Form or Report with Selected Records	551	(3)
Changing the Selection in an Open Form or Report	554	(5)
Finding a Group of Records Using Query By Form	559	(4)
Using a Multiselect List Box to Filter Records	563	(8)
Using SQL Techniques	571	(20)
Understanding the Vocabulary and Grammar of SQL	573	(3)
Using Stored Queries versus SQL Statements: Performance Considerations	576	(1)
Creating New Stored Queries in VBA Procedures	576	(4)
Running Stored Select Queries	580	(1)
Running a SQL Statement	581	(1)
Sorting and Filtering a Recordset	582	(3)
Running Stored Action Queries	585	(3)
Running a SQL Statement for an Action or Data-Definition Query	588	(3)
Using Transactions	591	(3)
Summary	594	(3)
Creating and Modifying Database Objects	597	(46)
Understanding How Access and the Database Engine Create Objects	598	(3)
Creating Data Access Objects	601	(18)
Using Navigational Techniques to Create Tables	603	(7)
Using SQL Techniques to Create Tables	610	(4)
Creating Forms, Reports, and Controls	614	(3)
Creating Modules and Event Procedures	617	(2)
Deleting a Database Window Object	619	(1)
Creating Custom Properties	619	(12)
The AccessObject Object	620	(5)
Creating Custom Properties for Forms, Reports, and Controls	625	(5)
Creating Custom Methods for a Form or Report	630	(1)
Displaying Multiple Instances of a Form	631	(9)
Using the Collection Object	633	(4)
Creating Multiple Instances	637	(1)
Including Multiple Instances in an Application	638	(2)
Summary	640	(3)
Expanding Access	643	(42)
Converting Macros to VBA Procedures	644	(9)
Why Convert Macros?	645	(2)
Converting Event Macros to Event Procedures	647	(3)
Converting Macros to Function Procedures	650	(3)
Understanding Library Databases	653	(4)
Creating a Library Database	653	(1)
Creating a Reference to a Library Database	654	(2)
Editing Library Code	656	(1)
Understanding Dynamic-Link Libraries	657	(4)
Using a Type Library	657	(1)
Using a Declare Statement	658	(1)
Using the Windows API DLLs	659	(2)
Using ActiveX Controls	661	(10)
Installing and Registering an ActiveX Control	662	(2)
Inserting an ActiveX Control	664	(3)
Setting ActiveX Control Properties	667	(2)
Using Events	669	(2)
Using Automation	671	(13)
Understanding Automation	672	(1)
Learning about an Application's Objects	673	(2)
Using the Information in the Object Library	675	(1)
Using Automation Objects	676	(8)
Summary	684	(1)
Appendix A Glossary	685	(12)
Appendix B The Data Access Object (DAO) Model	697	(110)
The Data Access Object (DAO) Hierarchy	698	(4)
Types of DAO Objects	701	(1)
Types of DAO Properties	701	(1)
Data Access Object References	702	(3)
Using Default Collections	703	(1)
Using the CurrentDB Function	704	(1)
Two Databases Opened Simultaneously	705	(2)
New Data Access Objects	707	(6)
Creating Databases	707	(1)
Creating Tables	708	(1)
Creating QueryDefs	709	(1)
Creating DAO Recordsets	710	(3)
DAO Recordset Object Manipulation	713	(9)
Using Physical Navigation	714	(2)
Using Logical Navigation	716	(2)
Adding, Editing, and Deleting Records	718	(3)
Using Clones	721	(1)
The DAO Model	722	(23)
The DBEngine Object	722	(2)
The Collection Objects	724	(1)
The Workspace Object	725	(1)
The Database Object	726	(2)
The TableDef Object	728	(1)
The Field Object	729	(3)
The Index Object	732	(2)
The Relation Object	734	(2)
The Recordset Object	736	(4)
The QueryDef Object	740	(1)
The Parameter Object	741	(1)
The Error Object	741	(1)
The Property Object	742	(1)
The Container Object	742	(1)
The Document Object	743	(1)
The User Object	744	(1)
The Group Object	744	(1)
Object Naming Conventions	745	(1)
DAO Object Tags	745	(1)
Using DAO Intrinsic Constants	745	(1)
Navigating a DAO Recordset with VBA	745	(30)
Navigating the Form and Its Recordset	746	(2)
Navigating among Records of a Form	748	(8)
Finding a Specific Record	756	(5)
Working with the Data in Tables	761	(11)
Exploring Clones	772	(1)
Reading Table Data into an Array	773	(2)
Editing Data in a Recordset	775	(16)
Changing a Record	775	(2)
Adding a Record	777	(2)
Deleting a Record	779	(1)
Creating New Stored Queries in VBA Procedures	780	(4)
Running Stored Select Queries	784	(2)
Running a SQL Statement	786	(1)
Sorting and Filtering a Recordset	786	(3)
Running Stored Action Queries and SQL Statements	789	(2)
Creating Data Access Objects	791	(12)
Using Navigational Techniques to Create Tables	792	(7)
Using SQL Techniques to Create Tables	799	(4)
Summary	803	(4)
Index	807