Home Page Icon
Home Page
Table of Contents for
Access Cookbook
Close
Access Cookbook
by Paul Litwin, Ken Getz, Andy Baron
Access Cookbook
Access Cookbook
Preface
What This Book Is About
Promotes Creative Use of the Product
Uses the Tools at Hand
Question-and-Answer Format
Who This Book Is For
What You Need to Use This Book
How This Book Is Organized
What We Left Out
How Do I Set Control Properties?
How Do I Create a New Module?
How Do I Import an Object?
How Do I Create an Event Macro?
How Do I Create an Event Procedure?
How Do I Place Code in a Form or Report’s Module?
How Do I Know What to Do with Code Examples?
Conventions Used in This Book
Comments and Questions
Acknowledgments
1. Queries
Specify Query Criteria at Runtime
Problem
Solution
Discussion
Using a Form-Based Parameter Query
Problem
Solution
Discussion
Limit the Items in One Combo Box Based on the Selected Item in Another
Problem
Solution
Discussion
Make Formatted Date Columns Sort Correctly in a Crosstab Query
Problem
Solution
Discussion
Group Mailing Labels by Address
Problem
Solution
Discussion
Use a Field in One Table to Update a Field in Another Table
Problem
Solution
Discussion
Use a VBA Variable to Filter a Query
Problem
Solution
Discussion
Use a Query to Retrieve a Random Set of Rows
Problem
Solution
Discussion
Create a Query That Will Show Aging of Receivables
Problem
Solution
Discussion
Create a Join That’s Based on a Comparison Other than Equality
Problem
Solution
Discussion
Create a Query to Combine Data from Two Tables with Similar Structures
Problem
Solution
Discussion
Create a Combo Box That Allows a User to Select N/A
Problem
Solution
Discussion
Use a Query to Show the Relationship Between Employees and Supervisors
Problem
Solution
Discussion
Create a Query That Uses Case-Sensitive Criteria
Problem
Solution
Discussion
Use a Query to Create a New Table Complete with Indexes
Problem
Solution
Discussion
Save My Queries in a Table for Better Programmatic Access and Security
Problem
Solution
Discussion
Create a Recordset Based on a Parameter Query from VBA Code
Problem
Solution
Discussion
2. Forms
Make Custom Templates for Forms and Reports
Problem
Solution
Discussion
Highlight the Current Field in Data-Entry Forms
Problem
Solution
Discussion
Restrict the User to a Single Row on a Form
Problem
Solution
Discussion
Use an Option Group to Collect and Display Textual Information
Problem
Solution
Discussion
Display Multiple Pages of Information on One Form
Problem
Solution
Discussion
Provide Record Navigation Buttons on a Form
Problem
Solution
Discussion
Size a Form’s Controls to Match the Form’s Size
Problem
Solution
Discussion
Make a Simple “Searching” List Box
Problem
Solution
Discussion
Create a Replacement for Access’s InputBox
Problem
Solution
Discussion
Using optional parameters
Creating pop-up forms
Initializing pop-up forms
Programmatically accessing online help
Miscellaneous comments
Store the Sizes and Locations of Forms
Problem
Solution
Discussion
Open Multiple Instances of a Form
Problem
Solution
Discussion
3. Reports
Create a Report with Line Numbers
Problem
Solution
Discussion
Print the Value of a Parameter on a Report
Problem
Solution
Discussion
Create a Report with Multiple Columns
Problem
Solution
Discussion
Print a Message on a Report if Certain Conditions Are Met
Problem
Solution
Discussion
Create a Page-Range Indicator on Each Page
Problem
Solution
Discussion
Create a Simple Bar Graph on a Report
Problem
Solution
Discussion
Create a Page Total
Problem
Solution
Discussion
Avoid Unwanted Blank Rows on Mailing Labels
Problem
Solution
Discussion
Suppress Printing a Report if There Are No Records to Print
Problem
Solution
Discussion
Print Different Headers or Footers on Odd and Even Pages
Problem
Solution
Discussion
Make a Vertical Line the Same Height as a CanGrow/CanShrink Control
Problem
Solution
Discussion
Alternate Gray Bars on My Reports
Problem
Solution
Discussion
Print Only Records Matching a Form’s Filter
Problem
Solution
Discussion
Keep a Report from Breaking at an Inappropriate Place
Problem
Solution
Discussion
Customize a Report’s Grouping and Sorting at Runtime
Problem
Solution
Discussion
4. Developing and Distributing Applications
Convert Queries into Embedded SQL Statements
Problem
Solution
Discussion
Build an Object Inventory
Problem
Solution
Discussion
Documenting all the containers
Creating the inventory table
Documenting each container
Avoiding errors
Comments
Verify That Objects Use Consistent Settings
Problem
Solution
Discussion
Creating the temporary tables and query
Getting ready to document items
Visiting all the objects
Recording property information
Comments
Hide Access Screen Activity
Problem
Solution
Discussion
Hiding reports in design view
Beware the pitfalls!
Find out What Language Version of Access Is Installed
Problem
Solution
Discussion
Internationalize Text in Your Applications
Problem
Solution
Discussion
Change and Reset the Access Caption Bar
Problem
Solution
Discussion
Use the Windows File Open/Save Common Dialogs
Problem
Solution
Discussion
Clean Test Data out of a Database When You’re Ready to Ship It
Problem
Solution
Discussion
Secure Your Access Database
Problem
Solution
Discussion
5. Taming Your Printer
Retrieve a List of All the Installed Output Devices
Problem
Solution
Discussion
Adding items to a list box
Set and Retrieve the Name of the Default Output Device
Problem
Solution
Discussion
Programmatically Change Margin and Column Settings for Reports
Problem
Solution
Discussion
Programmatically Change Printer Options
Problem
Solution
Discussion
Programmatically Control the Paper Source
Problem
Solution
Discussion
Retrieve Information About a Report or Form’s Selected Printer
Problem
Solution
Choose an Output Device at Runtime
Problem
Solution
Discussion
Find Which Reports Are Not Set to Print to the Default Printer
Problem
Solution
Discussion
6. Managing Data
Save with Each Record the Name of the Last Person Who Edited It and the Date and Time
Problem
Solution
Discussion
Determine if You’re on a New Record in a Form
Problem
Solution
Discussion
Find All Records with Names That Sound Alike
Problem
Solution
Discussion
Find the Median Value for a Field
Problem
Solution
Discussion
Quickly Find a Record in a Linked Table
Problem
Solution
Discussion
Get a Complete List of Field Properties from a Table or Query
Problem
Solution
Discussion
Create and Use Flexible AutoNumber Fields
Problem
Solution
Discussion
Back Up Selected Objects to Another Database
Problem
Solution
Discussion
The MultiSelect property
Filling the lboObjects list box
The backup process
Comments
7. Exploring VBA in Microsoft Access
Build Up String References with Embedded Quotes
Problem
Solution
Discussion
Create a Global Procedure Stack
Problem
Solution
Discussion
Create an Execution Time Profiler
Problem
Solution
Steps
Discussion
Multitask Your Access Basic Code
Problem
Solution
Discussion
Programmatically Add Items to a List or Combo Box
Problem
Solution
Filling a list box by modifying the RowSource property
Filling a list box by creating a list-filling callback function
Discussion
Modifying the RowSource property
Creating a list-filling callback function
Pass a Variable Number of Parameters to a Procedure
Problem
Solution
Discussion
Sort an Array in Access Basic
Problem
Solution
Discussion
Fill a List Box with a List of Files
Problem
Solution
Discussion
Handle Object Properties, in General
Problem
Solution
Discussion
Detect Whether an Object Exists
Problem
Solution
Discussion
8. Optimizing Your Application
Accelerate the Load Time of Forms
Problem
Solution
Discussion
Make Slow Forms Run Faster
Problem
Solution
Graphic and memo controls
Combo and list box controls
Discussion
Make Combo Boxes Load Faster
Problem
Solution
Discussion
Use Rushmore to Speed Up Queries
Problem
Solution
Discussion
Accelerate VBA Code
Problem
Solution
Discussion
Test the Comparative Benefits of Various Optimization Techniques
Problem
Solution
Discussion
Accelerate Multiuser Applications
Problem
Solution
Discussion
Accelerate Client/Server Applications
Problem
Solution
Discussion
9. Making the Most of Your User Interface
Create Context-Sensitive Keyboard Shortcuts
Problem
Solution
Discussion
Create a Form with No Menu or Toolbar
Problem
Solution
Discussion
Create a Geographical Map Interface
Problem
Solution
Discussion
Mark a Record on a Form and Return to It Later
Problem
Solution
Discussion
Carry Data Forward from Record to Record
Problem
Solution
Discussion
Create a Combo Box That Accepts New Entries
Problem
Solution
Discussion
Create Animated Buttons
Problem
Solution
Two-state buttons
Continuously animated buttons
Discussion
Create an Expanding Dialog
Problem
Technique
Discussion
Use an ActiveX Control
Problem
Solution
Add a bound Calendar control to your form
Create a generic unbound pop-up calendar form
Discussion
Create a Generic, Reusable Status Meter
Problem
Solution
Create a generic status meter
Use the generic status meter in your application
Discussion
10. Multiuser Applications
Properly Secure Your Database
Problem
Solution
Make a security plan
Secure your database
Work with the secured database
Discussion
Maintain Multiple Synchronized Copies of the Same Database
Problem
Solution
Replicating a database
Synchronizing replicas
Resolving conflicts
Discussion
Create a Transaction Log
Problem
Solution
Discussion
Send Messages to Other Users Without Using Email
Problem
Solution
Discussion
Programmatically Track Users and Groups
Problem
Solution
Discussion
Adjust an Application Based on Who’s Logged In
Problem
Solution
Discussion
List All Users with Blank Passwords
Problem
Solution
Discussion
Track Which Users Have a Shared Database Open
Problem
Solution
Discussion
Determine if a Record Is Locked and by Whom
Problem
Solution
Discussion
Set a Maximum Locking Interval for a Record
Problem
Solution
Discussion
11. The Windows API
Remove a Form’s System Menu and Maximize/Minimize Buttons
Problem
Solution
Discussion
Flash a Window’s Titlebar or Icon
Problem
Solution
Discussion
Classify Keypresses in a Language-Independent Manner
Problem
Solution
Discussion
Restrict Mouse Movement to a Specific Region
Problem
Solution
Discussion
Run Another Program and Pause Until It’s Done
Problem
Solution
Discussion
Exit Windows Under Program Control
Problem
Solution
Discussion
Run the Application Associated with a Data File
Problem
Solution
Discussion
Check to See if an Application Is Already Running
Problem
Solution
Discussion
Retrieve a List of All Top-Level Windows
Problem
Solution
Discussion
Close a Running Windows Application
Problem
Solution
Discussion
Set File Date and Time Stamps
Problem
Solution
Discussion
Retrieve Information About Available Drives
Problem
Solution
Discussion
Collect and Display Information on the System and the Access Installation
Problem
Solution
Discussion
Create and Cancel Network Connections Programmatically
Problem
Solution
Discussion
Retrieving information
Adding and canceling connections using common dialogs
Adding and canceling connections with no user intervention
12. Automation
Play an Embedded Sound File from Within an Application
Problem
Solution
Discussion
Print an Access Report from Excel
Problem
Solution
Discussion
Use Excel’s Functions from Within Access
Problem
Solution
Discussion
Setting up communication with Excel
Calling simple Excel functions
Calling Excel functions using ranges
Calling Excel functions using arrays
Closing Excel
Comments
Perform a Mail Merge from Access to Word
Problem
Solution
Discussion
Starting the connection with Word for Windows
Performing the mail merge
Finishing the mail merge
Add an Item to the Startup Group
Problem
Solution
Using the sample forms
Using DDE with the Windows shell
Discussion
Retrieving information from the Windows shell
Executing tasks
Using the wrapper procedures
Comments
Send Access Data to Excel and Create an Excel Chart
Problem
Solution
Discussion
Create a PowerPoint Presentation from Access Data
Problem
Solution
Discussion
Starting and stopping PowerPoint
Creating the presentation
Creating each slide
Creating the text
Comments
Add a Contact and Send Email Through Outlook
Problem
Solution
Discussion
13. Using Data Access Pages
Replace Navigation Button Images with Your Own Images
Problem
Solution
Discussion
Use Labels or Other Controls for Record Navigation
Problem
Solution
Change the Text Displayed with a Navigation Control
Problem
Solution
Create a DAP That Allows You to Update Data
Problem
Solution
Create One File to Store Connection Information for All DAPs in an Application
Problem
Solution
Programmatically Change the Connection String for All Pages in a Database
Problem
Solution
Discussion
Change the Default Settings for New DAPs
Problem
Solution
Discussion
Use Parameters Set in One DAP to Open Another
Problem
Solution
Discussion
14. Working with SQL Server Data
Dynamically Link SQL Server Tables at Runtime
Problem
Solution
Discussion
Dynamically Connect to SQL Server from an ADP
Problem
Solution
Discussion
Share an ADP from a Shared Network Folder
Problem
Solution
Discussion
Fill the Drop-Down Lists When Using ServerFilterByForm in an ADP
Problem
Solution
Discussion
Pass Parameters to Stored Procedures from Pass-Through Queries in an MDB
Problem
Solution
Discussion
Pass Parameters to Stored Procedures from an ADP
Problem
Solution
Discussion
Use Controls as Parameters for the Row Source of Combo and List Boxes in an ADP
Problem
Solution
Discussion
Reference Data from More than One SQL Server Database in an ADP
Problem
Solution
Discussion
Use Views to Update Data in an ADP When Users Don’t Have Permissions on Tables
Problem
Solution
Discussion
Index
Colophon
Search in book...
Toggle Font Controls
Playlists
Add To
Create new playlist
Name your new playlist
Playlist description (optional)
Cancel
Create playlist
Sign In
Email address
Password
Forgot Password?
Create account
Login
or
Continue with Facebook
Continue with Google
Sign Up
Full Name
Email address
Confirm Email Address
Password
Login
Create account
or
Continue with Facebook
Continue with Google
Prev
Previous Chapter
Cover
Next
Next Chapter
Preface
Access Cookbook
Andy Baron
Ken Getz
Paul Litwin
Editor
Ron Petrusha
Editor
John Osborn
Copyright © 2002 O'Reilly Media, Inc.
Add Highlight
No Comment
..................Content has been hidden....................
You can't read the all page of ebook, please click
here
login for view all page.
Day Mode
Cloud Mode
Night Mode
Reset