Access Cookbook

by Getz, Ken; Litwin, Paul; Baron, Andy

• ISBN13: 9780596000844
• ISBN10: 0596000847
• Format: Paperback
• Copyright: 2002-02-01
• Publisher: Oreilly & Associates Inc

Summary

"Access Cookbook" fills a real market need among Access developers for a convenient source of coding solutions they can turn to when they run into common and uncommon problems. The code RrecipesS offer instant gratification--readers simply cut and paste from the book's downloadable code into the application that needs it.

Table of Contents

Preface	xi
Queries	1	(60)
Specify Query Criteria at Runtime	2	(2)
Using a Form-Based Parameter Query	4	(3)
Limit the Items in One Combo Box Based on the Selected Item in Another	7	(4)
Make Formatted Date Columns Sort Correctly in a Crosstab Query	11	(3)
Group Mailing Labels by Address	14	(4)
Use a Field in One Table to Update a Field in Another Table	18	(2)
Use a VBA Variable to Filter a Query	20	(5)
Use a Query to Retrieve a Random set of Rows	25	(3)
Create a Query That Will Show Aging of Receivables	28	(4)
Create a Join That's Based on a Comparison Other than Equality	32	(3)
Create a Query to Combine Data from Two Tables with Similar Structures	35	(3)
Create a Combo Box That Allows a User to Select N/A	38	(4)
Use a Query to Show the Relationship Between Employees and Supervisors	42	(5)
Create a Query That Uses Case-Sensitive Criteria	47	(4)
Use a Query to Create a New Table Complete with Indexes	51	(3)
Save My Queries in a Table for Better Programmatic Access and Security	54	(2)
Create a Recordset Based on a Parameter Query from VBA Code	56	(5)
Forms	61	(48)
Make Custom Templates for Forms and Reports	61	(4)
Highlight the Current Field in Data-Entry Forms	65	(4)
Restrict the User to a Single Row on a Form	69	(2)
Use an Option Group to Collect and Display Textual Information	71	(4)
Display Multiple Pages of Information on One Form	75	(3)
Provide Record Navigation Buttons on a Form	78	(6)
Size a Form's Controls to Match the Form's Size	84	(5)
Make a Simple ``Searching'' List Box	89	(4)
Create a Replacement for Access's InputBox	93	(7)
Store the Sizes and Locations of Forms	100	(4)
Open Multiple Instances of a Form	104	(5)
Reports	109	(60)
Create a Report with Line Numbers	110	(1)
Print the Value of a Parameter on a Report	111	(4)
Create a Report with Multiple Columns	115	(4)
Print a Message on a Report if Certain Conditions Are Met	119	(4)
Create a Page-Range Indicator on Each Page	123	(2)
Create a Simple Bar Graph on a Report	125	(4)
Create a Page Total	129	(3)
Avoid Unwanted Blank Rows on Mailing Labels	132	(4)
Suppress Printing a Report if There Are No Records to Print	136	(3)
Print Different Headers or Footers on Odd and Even Pages	139	(3)
Make a Vertical Line the Same Height as a CanGrow/CanShrink Control	142	(4)
Alternate Gray Bars on My Reports	146	(3)
Print Only Records Matching a Form's Filter	149	(5)
Keep a Report from Breaking at an Inappropriate Place	154	(4)
Customize a Report's Grouping and Sorting at Runtime	158	(11)
Developing and Distributing Applications	169	(54)
Convert Queries into Embedded SQL Statements	169	(3)
Build an Object Inventory	172	(7)
Verify That Objects Use Consistent Settings	179	(10)
Hide Access Screen Acitivity	189	(5)
Find out What Language Version of Access Is Installed	194	(3)
Internationalize Text in Your Applications	197	(6)
Change and Reset the Access Caption Bar	203	(4)
Use the Windows File Open/Save Common Dialogs	207	(7)
Clean Test Data out of a Database When You're Ready to Ship It	214	(3)
Secure Your Access Database	217	(6)
Taming Your Printer	223	(24)
Retrieve a List of All the Installed Output Devices	224	(3)
Set and Retrieve the Name of the Default Output Device	227	(2)
Programmatically Change Margin and Column Settings for Reports	229	(4)
Programmatically Change Printer Options	233	(4)
Programmatically Control the Paper Source	237	(2)
Retrieve Information About a Report or Form's Selected Printer	239	(2)
Choose an Output Device at Runtime	241	(3)
Find Which Reports Are Not Set to Print to the Default Printer	244	(3)
Managing Data	247	(41)
Save with Each Record the Name of the Last Person Who Edited It and the Date and Time	247	(5)
Determine if You're on a New Record in a Form	252	(3)
Find All Records with Names that Sound Alike	255	(4)
Find the Median Value for a Field	259	(6)
Quickly Find a Record in a Linked Table	265	(3)
Get a Complete List of Field Properties from a Table or Query	268	(5)
Create and Use Flexible AutoNumber Fields	273	(5)
Back Up Selected Objects to Another Database	278	(10)
Exploring VBA in Microsoft Access	288	(53)
Build Up String References with Embedded Quotes	289	(5)
Create a Global Procedure Stack	294	(5)
Create an Execution Time Profiler	299	(5)
Multitask Your Access Basic Code	304	(4)
Programmatically Add Items to a List or Combo Box	308	(9)
Pass a Variable Number of Parameters to a Procedure	317	(5)
Sort an Array in Access Basic	322	(4)
Fill a List Box with a List of Files	326	(4)
Handle Object Properties, in General	330	(6)
Detect Whether an Object Exists	336	(5)
Optimizing Your Application	341	(38)
Accelerate the Load Time of Forms	341	(8)
Make Slow Forms Run Faster	349	(9)
Make Combo Boxes Load Faster	358	(1)
Use Rushmore to Speed Up Queries	359	(4)
Accelerate VBA Code	363	(3)
Test the Comparative Benefits of Various Optimization Techniques	366	(3)
Accelerate Multiuser Applications	369	(6)
Accelerate Client/Server Applications	375	(4)
Making the Most of Your User Interface	379	(53)
Create Context-Sensitive Keyboard Shortcuts	379	(6)
Create a Form with No Menu or Toolbar	385	(3)
Create a Geographical Map Interface	388	(3)
Mark a Record on a Form and Return to It Later	391	(7)
Carry Data Forward from Record to Record	398	(4)
Create a Combo Box That Accepts New Entries	402	(5)
Create Animated Buttons	407	(6)
Create an Expanding Dialog	413	(4)
Use an ActiveX Control	417	(9)
Create a Generic, Reusable Status Meter	426	(6)
Multiuser Applications	432	(53)
Properly Secure Your Database	432	(9)
Maintain Multiple Synchronized Copies of the Same Database	441	(6)
Create a Transaction Log	447	(3)
Send Messages to Other Users Without Using Email	450	(8)
Programmatically Track Users and Groups	458	(6)
Adjust an Application Based on Who's Logged In	464	(3)
List All Users with Blank Passwords	467	(5)
Track Which Users Have a Shared Database Open	472	(2)
Determine if a Record Is Locked and by Whom	474	(4)
Set a Maximum Locking Interval for a Record	478	(7)
The Windows API	485	(58)
Remove a Form's System Menu and Maximize/Minimize Buttons	486	(4)
Flash a Window's Titlebar or Icon	490	(3)
Classify Keypresses in a Language-Independent Manner	493	(3)
Restrict Mouse Movement to a Specific Region	496	(3)
Run Another Program and Pause Until It's Done	499	(3)
Exit Windows Under Program Control	502	(2)
Run the Application Associated with a Data File	504	(6)
Check to See if an Application Is Already Running	510	(3)
Retrieve a List of All Top-Level Windows	513	(3)
Close a Running Windows Application	516	(3)
Set File Date and Time Stamps	519	(3)
Retrieve Information About Available Drives	522	(4)
Collect and Display Information on the System and the Access Installation	526	(8)
Create and Cancel Network Connections Programmatically	534	(9)
Automation	543	(62)
Play an Embedded Sound File from Within an Application	543	(5)
Print an Access Report from Excel	548	(6)
Use Excel's Functions from Within Access	554	(9)
Perform a Mail Merge from Access to Word	563	(4)
Add an Item to the Startup Group	567	(11)
Send Access Data to Excel and Create an Excel Chart	578	(6)
Create a PowerPoint Presentation from Access Data	584	(14)
Add a Contact and Send Email Through Outlook	598	(7)
Using Data Access Pages	605	(28)
Replace Navigation Button Images with Your Own Images	606	(2)
Use Labels or Other Controls for Record Navigation	608	(2)
Change the Text Displayed with a Navigation Control	610	(3)
Create a DAP That Allows You to Update Data	613	(2)
Create One File to Store Connection Information for All DAPs in an Application	615	(5)
Programmatically Change the Connection String for All Pages in a Database	620	(5)
Change the Default Settings for New DAPs	625	(2)
Use Parameters Set in One DAP to Open Another	627	(6)
Working with SQL Server Data	633	(28)
Dynamically Link SQL Server Tables at Runtime	633	(6)
Dynamically Connect to SQL Server from an ADP	639	(3)
Share an ADP from a Shared Network Folder	642	(1)
Fill the Drop-Down Lists When Using ServerFilterByForm in an ADP	643	(4)
Pass Parameters to Stored Procedures from Pass-Through Queries in an MDB	647	(3)
Pass Parameters to Stored Procedures from an ADP	650	(3)
Use Controls as Parameters for the Row Source of Combo and List Boxes in an ADP	653	(3)
Reference Data from More than One SQL Server Database in an ADP	656	(2)
Use Views to Update Data in an ADP When Users Don't Have Permissions on Tables	658	(3)
Index	661