Access Cookbook

Preface;
What This Book Is About;
Who This Book Is For;
What You Need to Use This Book;
How This Book Is Organized;
What We Left Out;
Conventions Used in This Book;
Comments and Questions;
Acknowledgments;
Chapter 1: Queries;
1.1 Specify Query Criteria at Runtime;
1.2 Using a Form-Based Parameter Query;
1.3 Limit the Items in One Combo Box Based on the Selected Item in Another;
1.4 Make Formatted Date Columns Sort Correctly in a Crosstab Query;
1.5 Group Mailing Labels by Address;
1.6 Use a Field in One Table to Update a Field in Another Table;
1.7 Use a VBA Variable to Filter a Query;
1.8 Use a Query to Retrieve a Random Set of Rows;
1.9 Create a Query That Will Show Aging of Receivables;
1.10 Create a Join That’s Based on a Comparison Other than Equality;
1.11 Create a Query to Combine Data from Two Tables with Similar Structures;
1.12 Create a Combo Box That Allows a User to Select N/A;
1.13 Use a Query to Show the Relationship Between Employees and Supervisors;
1.14 Create a Query That Uses Case-Sensitive Criteria;
1.15 Use a Query to Create a New Table Complete with Indexes;
1.16 Save My Queries in a Table for Better Programmatic Access and Security;
1.17 Create a Recordset Based on a Parameter Query from VBA Code;
Chapter 2: Forms;
2.1 Make Custom Templates for Forms and Reports;
2.2 Highlight the Current Field in Data-Entry Forms;
2.3 Restrict the User to a Single Row on a Form;
2.4 Use an Option Group to Collect and Display Textual Information;
2.5 Display Multiple Pages of Information on One Form;
2.6 Provide Record Navigation Buttons on a Form;
2.7 Size a Form’s Controls to Match the Form’s Size;
2.8 Make a Simple “Searching” List Box;
2.9 Create a Replacement for Access’s InputBox;
2.10 Store the Sizes and Locations of Forms;
2.11 Open Multiple Instances of a Form;
Chapter 3: Reports;
3.1 Create a Report with Line Numbers;
3.2 Print the Value of a Parameter on a Report;
3.3 Create a Report with Multiple Columns;
3.4 Print a Message on a Report if Certain Conditions Are Met;
3.5 Create a Page-Range Indicator on Each Page;
3.6 Create a Simple Bar Graph on a Report;
3.7 Create a Page Total;
3.8 Avoid Unwanted Blank Rows on Mailing Labels;
3.9 Suppress Printing a Report if There Are No Records to Print;
3.10 Print Different Headers or Footers on Odd and Even Pages;
3.11 Make a Vertical Line the Same Height as a CanGrow/CanShrink Control;
3.12 Alternate Gray Bars on My Reports;
3.13 Print Only Records Matching a Form’s Filter;
3.14 Keep a Report from Breaking at an Inappropriate Place;
3.15 Customize a Report’s Grouping and Sorting at Runtime;
Chapter 4: Developing and Distributing Applications;
4.1 Convert Queries into Embedded SQL Statements;
4.2 Build an Object Inventory;
4.3 Verify That Objects Use Consistent Settings;
4.4 Hide Access Screen Activity;
4.5 Find out What Language Version of Access Is Installed;
4.6 Internationalize Text in Your Applications;
4.7 Change and Reset the Access Caption Bar;
4.8 Use the Windows File Open/Save Common Dialogs;
4.9 Clean Test Data out of a Database When You’re Ready to Ship It;
4.10 Secure Your Access Database;
Chapter 5: Taming Your Printer;
5.1 Retrieve a List of All the Installed Output Devices;
5.2 Set and Retrieve the Name of the Default Output Device;
5.3 Programmatically Change Margin and Column Settings for Reports;
5.4 Programmatically Change Printer Options;
5.5 Programmatically Control the Paper Source;
5.6 Retrieve Information About a Report or Form’s Selected Printer;
5.7 Choose an Output Device at Runtime;
5.8 Find Which Reports Are Not Set to Print to the Default Printer;
Chapter 6: Managing Data;
6.1 Save with Each Record the Name of the Last Person Who Edited It and the Date and Time;
6.2 Determine if You’re on a New Record in a Form;
6.3 Find All Records with Names That Sound Alike;
6.4 Find the Median Value for a Field;
6.5 Quickly Find a Record in a Linked Table;
6.6 Get a Complete List of Field Properties from a Table or Query;
6.7 Create and Use Flexible AutoNumber Fields;
6.8 Back Up Selected Objects to Another Database;
Chapter 7: Exploring VBA in Microsoft Access;
7.1 Build Up String References with Embedded Quotes;
7.2 Create a Global Procedure Stack;
7.3 Create an Execution Time Profiler;
7.4 Multitask Your Access Basic Code;
7.5 Programmatically Add Items to a List or Combo Box;
7.6 Pass a Variable Number of Parameters to a Procedure;
7.7 Sort an Array in Access Basic;
7.8 Fill a List Box with a List of Files;
7.9 Handle Object Properties, in General;
7.10 Detect Whether an Object Exists;
Chapter 8: Optimizing Your Application;
8.1 Accelerate the Load Time of Forms;
8.2 Make Slow Forms Run Faster;
8.3 Make Combo Boxes Load Faster;
8.4 Use Rushmore to Speed Up Queries;
8.5 Accelerate VBA Code;
8.6 Test the Comparative Benefits of Various Optimization Techniques;
8.7 Accelerate Multiuser Applications;
8.8 Accelerate Client/Server Applications;
Chapter 9: Making the Most of Your User Interface;
9.1 Create Context-Sensitive Keyboard Shortcuts;
9.2 Create a Form with No Menu or Toolbar;
9.3 Create a Geographical Map Interface;
9.4 Mark a Record on a Form and Return to It Later;
9.5 Carry Data Forward from Record to Record;
9.6 Create a Combo Box That Accepts New Entries;
9.7 Create Animated Buttons;
9.8 Create an Expanding Dialog;
9.9 Use an ActiveX Control;
9.10 Create a Generic, Reusable Status Meter;
Chapter 10: Multiuser Applications;
10.1 Properly Secure Your Database;
10.2 Maintain Multiple Synchronized Copies of the Same Database;
10.3 Create a Transaction Log;
10.4 Send Messages to Other Users Without Using Email;
10.5 Programmatically Track Users and Groups;
10.6 Adjust an Application Based on Who’s Logged In;
10.7 List All Users with Blank Passwords;
10.8 Track Which Users Have a Shared Database Open;
10.9 Determine if a Record Is Locked and by Whom;
10.10 Set a Maximum Locking Interval for a Record;
Chapter 11: The Windows API;
11.1 Remove a Form’s System Menu and Maximize/Minimize Buttons;
11.2 Flash a Window’s Titlebar or Icon;
11.3 Classify Keypresses in a Language-Independent Manner;
11.4 Restrict Mouse Movement to a Specific Region;
11.5 Run Another Program and Pause Until It’s Done;
11.6 Exit Windows Under Program Control;
11.7 Run the Application Associated with a Data File;
11.8 Check to See if an Application Is Already Running;
11.9 Retrieve a List of All Top-Level Windows;
11.10 Close a Running Windows Application;
11.11 Set File Date and Time Stamps;
11.12 Retrieve Information About Available Drives;
11.13 Collect and Display Information on the System and the Access Installation;
11.14 Create and Cancel Network Connections Programmatically;
Chapter 12: Automation;
12.1 Play an Embedded Sound File from Within an Application;
12.2 Print an Access Report from Excel;
12.3 Use Excel’s Functions from Within Access;
12.4 Perform a Mail Merge from Access to Word;
12.5 Add an Item to the Startup Group;
12.6 Send Access Data to Excel and Create an Excel Chart;
12.7 Create a PowerPoint Presentation from Access Data;
12.8 Add a Contact and Send Email Through Outlook;
Chapter 13: Using Data Access Pages;
13.1 Replace Navigation Button Images with Your Own Images;
13.2 Use Labels or Other Controls for Record Navigation;
13.3 Change the Text Displayed with a Navigation Control;
13.4 Create a DAP That Allows You to Update Data;
13.5 Create One File to Store Connection Information for All DAPs in an Application;
13.6 Programmatically Change the Connection String for All Pages in a Database;
13.7 Change the Default Settings for New DAPs;
13.8 Use Parameters Set in One DAP to Open Another;
Chapter 14: Working with SQL Server Data;
14.1 Dynamically Link SQL Server Tables at Runtime;
14.2 Dynamically Connect to SQL Server from an ADP;
14.3 Share an ADP from a Shared Network Folder;
14.4 Fill the Drop-Down Lists When Using ServerFilterByForm in an ADP;
14.5 Pass Parameters to Stored Procedures from Pass-Through Queries in an MDB;
14.6 Pass Parameters to Stored Procedures from an ADP;
14.7 Use Controls as Parameters for the Row Source of Combo and List Boxes in an ADP;
14.8 Reference Data from More than One SQL Server Database in an ADP;
14.9 Use Views to Update Data in an ADP When Users Don’t Have Permissions on Tables;
Colophon;