Access Cookbook

Ken Getz is a senior consultant with MCW Technologies and splits his time between programming, writing, and training. He specializes in tools and applications written in Visual Studio .NET and Visual Basic. Ken is co-author of best-selling several books, including Access 2002 Developer's Handbooks with Paul Litwin and Mike Gunderloy, Visual Basic Language Developer's Handbook with Mike Gilbert, and VBA Developer's Handbook with Mike Gilbert (Sybex). He co-wrote several training courses for Application Developer's Training Company, including VB.NET, ASP.NET, Access 2000 and 97, Visual Basic 6, and Visual Basic 5 seminars. He has also recorded video training for AppDev covering VB.NET, ASP.NET, VB6, Access 2000, and Access 97. Ken is a frequent speaker at technical conferences and spoken often at Microsoft's Tech-Ed conference. Ken's also a technical editor for Access-VB-SQL Advisor magazine and a columnist for Informant Publications' asp.netPRO magazine. Andy Baron is a senior consultant at MCW Technologies, a Microsoft MVP (Most Valuable Professional) since 1995, and a contributing editor for Advisor Media and Pinnacle Publications. Andy is co-author of the Microsoft Access Developers Guide to SQL Server (Sams), and he writes and presents courseware for Application Developers Training Co. Paul Litwin is the president of Litwin Consulting, providing development, mentoring, and training in ASP, ASP.NET, Visual Basic, SQL Server, XML, Microsoft Access, and related technologies. Paul was the founding editor of the Smart Access Newsletter and recently became the editor-in-chief of asp.netPRO Magazine. He is the author of a number of books, articles, and training materials on Access, VBA, Web development, ASP, and ASP.NET. Paul is one of the founders of Deep Training, a developer-owned training company providing training on Microsoft.NET. He is the conference chair of Microsoft ASP.NET Connections and Office Developer Connections and speaks regularly at other industry events, including Microsoft TechEd and Microsoft Office Deployment and Development Conference.

Preface; 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; 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; 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 row/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; 4. 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; 5. Printers; 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 Prog