Access Cookbook

Preface

1. Queries
Specify Query Criteria at Runtime
Using a Form-Based Parameter Query
Limit the Items in One Combo Box Based on the Selected Item in Another
Make Formatted Date Columns Sort Correctly in a Crosstab Query
Group Mailing Labels by Address
Use a Field in One Table to Update a Field in Another Table
Use a VBA Variable to Filter a Query
Use a Query to Retrieve a Random Set of Rows
Create a Query That Will Show Aging of Receivables
Create a Join That's Based on a Comparison Other than Equality
Create a Query to Combine Data from Two Tables with Similar Structures
Create a Combo Box That Allows a User to Select N/A
Use a Query to Show the Relationship Between Employees and Supervisors
Create a Query That Uses Case-Sensitive Criteria
Use a Query to Create a New Table Complete with Indexes
Save My Queries in a Table for Better Programmatic Access and Security
Create a Recordset Based on a Parameter Query from VBA Code

2. Forms
Make Custom Templates for Forms and Reports
Highlight the Current Field in Data-Entry Forms
Restrict the User to a Single Row on a Form
Use an Option Group to Collect and Display Textual Information
Display Multiple Pages of Information on One Form
Provide Record Navigation Buttons on a Form
Size a Form's Controls to Match the Form's Size
Make a Simple \'Searching\' List Box
Create a Replacement for Access's InputBox
Store the Sizes and Locations of Forms
Open Multiple Instances of a Form

3. Reports
Create a Report with Line Numbers
Print the Value of a Parameter on a Report
Create a Report with Multiple Columns
Print a Message on a Report if Certain Conditions Are Met
Create a Page-Range Indicator on Each Page
Create a Simple Bar Graph on a Report
Create a Page Total
Avoid Unwanted Blank Rows on Mailing Labels
Suppress Printing a Report if There Are No Records to Print
Print Different Headers or Footers on Odd and Even Pages
Make a Vertical Line the Same Height as a CanGrow/CanShrink Control
Alternate Gray Bars on My Reports
Print Only Records Matching a Form's Filter
Keep a Report from Breaking at an Inappropriate Place
Customize a Report's Grouping and Sorting at Runtime

4. Developing and Distributing Applications
Convert Queries into Embedded SQL Statements
Build an Object Inventory
Verify That Objects Use Consistent Settings
Hide Access Screen Activity
Find out What Language Version of Access Is Installed
Internationalize Text in Your Applications
Change and Reset the Access Caption Bar
Use the Windows File Open/Save Common Dialogs
Clean Test Data out of a Database When You're Ready to Ship It
Secure Your Access Database

5. Taming Your Printer
Retrieve a List of All the Installed Output Devices
Set and Retrieve the Name of the Default Output Device
Programmatically Change Margin and Column Settings for Reports
Programmatically Change Printer Options
Programmatically Control the Paper Source
Retrieve Information About a Report or Form's Selected Printer
Choose an Output Device at Runtime
Find Which Reports Are Not Set to Print to the Default Printer

6. Managing Data
Save with Each Record the Name of the Last Person Who Edited It and the Date and Time
Determine if You're on a New Record in a Form
Find All Records with Names That Sound Alike
Find the Median Value for a Field
Quickly Find a Record in a Linked Table
Get a Complete List of Field Properties from a Table or Query
Create and Use Flexible AutoNumber Fields
Back Up Selected Objects to Another Database

7. Exploring VBA in Microsoft Access
Build Up String References with Embedded Quotes
Create a Global Procedure Stack
Create an Execution Time Profiler
Multitask Your Access Basic Code
Programmatically Add Items to a List or Combo Box
Pass a Variable Number of Parameters to a Procedure
Sort an Array in Access Basic
Fill a List Box with a List of Files
Handle Object Properties, in General
Detect Whether an Object Exists

8. Optimizing Your Application
Accelerate the Load Time of Forms
Make Slow Forms Run Faster
Make Combo Boxes Load Faster
Use Rushmore to Speed Up Queries
Accelerate VBA Code
Test the Comparative Benefits of Various Optimization Techniques
Accelerate Multiuser Applications
Accelerate Client/Server Applications

9. Making the Most of Your User Interface
Create Context-Sensitive Keyboard Shortcuts
Create a Form with No Menu or Toolbar
Create a Geographical Map Interface
Mark a Record on a Form and Return to It Later
Carry Data Forward from Record to Record
Create a Combo Box That Accepts New Entries
Create Animated Buttons
Create an Expanding Dialog
Use an ActiveX Control
Create a Generic, Reusable Status Meter

10. Multiuser Applications
Properly Secure Your Database
Maintain Multiple Synchronized Copies of the Same Database
Create a Transaction Log
Send Messages to Other Users Without Using Email
Programmatically Track Users and Groups
Adjust an Application Based on Who's Logged In
List All Users with Blank Passwords
Track Which Users Have a Shared Database Open
Determine if a Record Is Locked and by Whom
Set a Maximum Locking Interval for a Record

11. The Windows API
Remove a Form's System Menu and Maximize/Minimize Buttons
Flash a Window's Titlebar or Icon
Classify Keypresses in a Language-Independent Manner
Restrict Mouse Movement to a Specific Region
Run Another Program and Pause Until It's Done
Exit Windows Under Program Control
Run the Application Associated with a Data File
Check to See if an Application Is Already Running
Retrieve a List of All Top-Level Windows
Close a Running Windows Application
Set File Date and Time Stamps
Retrieve Information About Available Drives
Collect and Display Information on the System and the Access Installation
Create and Cancel Network Connections Programmatically

12. Automation
Play an Embedded Sound File from Within an Application
Print an Access Report from Excel
Use Excel's Functions from Within Access
Perform a Mail Merge from Access to Word
Add an Item to the Startup Group
Send Access Data to Excel and Create an Excel Chart
Create a PowerPoint Presentation from Access Data
Add a Contact and Send Email Through Outlook

13. Using Data Access Pages
Replace Navigation Button Images with Your Own Images
Use Labels or Other Controls for Record Navigation
Change the Text Displayed with a Navigation Control
Create a DAP That Allows You to Update Data
Create One File to Store Connection Information for All DAPs in an Application
Programmatically Change the Connection String for All Pages in a Database
Change the Default Settings for New DAPs
Use Parameters Set in One DAP to Open Another

14. Working with SQL Server Data
Dynamically Link SQL Server Tables at Runtime
Dynamically Connect to SQL Server from an ADP
Share an ADP from a Shared Network Folder
Fill the Drop-Down Lists When Using ServerFilterByForm in an ADP
Pass Parameters to Stored Procedures from Pass-Through Queries in an MDB
Pass Parameters to Stored Procedures from an ADP
Use Controls as Parameters for the Row Source of Combo and List Boxes in an ADP
Reference Data from More than One SQL Server Database in an ADP
Use Views to Update Data in an ADP When Users Don't Have Permissions on Tables

Index