book

Fixing Access Annoyances

by Evan Callahan, Phil Mitchell

February 2006

Intermediate to advanced

380 pages

12h 17m

English

O'Reilly Media, Inc.

Read now

Unlock full access

Copyright
Dedication
Introduction
How to Use This BookAccess VersionsMSKB 123456Conventions Used in This BookUsing Code ExamplesI Did Exactly What You Said, and It Still Doesn’t Work!Safari® EnabledAcknowledgments
0. Access Basics
0.1. The Database Window0.2. Wizards0.3. Design View and User Views0.4. Setting Properties0.5. Bound Versus Unbound Objects0.6. Multi-User Databases and Split Design0.7. How to Create an Event Procedure0.8. How to Add Code to Your Application0.9. DAO Versus ADO
1. General Annoyances
1.1. CUSTOMIZING ACCESS1.1.1. Access’s Bad Defaults1.1.1.1. Global Defaults1.1.1.2. Specific Defaults1.1.2. Alt-Tab Clutter1.1.3. A Better Alt-Tab1.1.4. The Recently Used File List1.1.5. Grayed-Out Menu Items1.1.5.1. Import Spreadsheet Wizard: Data Type box1.1.5.2. Import Spreadsheet Wizard: “In an Existing Table” option1.1.5.3. Tools → Database Utilities: Linked Table Manager, Convert Database, and Upsizing Wizard1.1.5.4. New Record(*)Button on a Form, or Insert → New Record for a Table1.1.5.5. Tools → Database Utilities → Convert Database → To Access 2000 File Format1.1.5.6. Edit Relationships Dialog (Tools → Relationships): Enforce Referential Integrity1.1.5.7. Format → Conditional Formatting (Design View for a form)1.1.5.8. Query → Parameters1.1.5.9. Tools → Database Utilities → Make MDE File or Make ADE File1.1.5.10. Change Owner Button in Tools → Security → User and Group Permissions1.1.6. Define Your Own Defaults for Forms, Reports, and Controls1.1.7. Defaults for Tables, Queries, and Datasheets1.1.8. Hidden (but Indispensable) Keyboard Shortcuts1.1.9. Create Keyboard Shortcuts1.1.10. Toggle Between Design View and Object View1.1.11. Type Loooooong Lines in Small Places1.1.12. Access Changes My Data1.1.13. Help That Isn’t/Is Helpful1.1.13.1. Newsgroups1.1.13.2. Utter Access1.1.13.3. Expert Sites1.1.13.4. The Microsoft Knowledge Base1.1.13.5. Microsoft Developer Network (MSDN)1.1.14. Give Me Full Menus!1.1.15. Missing or Mangled Toolbars and Menus1.1.15.1. Toolbar/Menu Is Missing1.1.15.2. Toolbar/Menu Has Been Altered1.1.15.3. Toolbar/Menu Has Been Moved or Merged into Another One1.1.15.4. Toolbar Is Disabled at Startup1.1.16. Missing Objects (Forms, Reports, Queries…)1.1.17. Scroll Wheel Confusion1.1.18. Turn Off Dire Hyperlink Warnings1.1.19. Find/Replace for Database Objects1.1.20. Save Custom Form Placement1.1.21. Data Entry Without Access1.1.22. Getting Lost in Datasheet View1.2. ACCESS RUNS AMOK1.2.1. Access Is Buggy and Unstable1.2.2. Flaky or Corrupted Database1.2.2.1. Preparation1.2.2.2. Use the Built-in Compact/Repair Utility1.2.2.3. Export to a New Database1.2.2.4. Problem-Specific Suggestions1.2.2.5. Professional Help1.2.3. That Darn Corruption1.2.3.1. Network Problems1.2.3.2. System Crashes1.2.3.3. Access Bugs1.2.3.4. Application Bugs1.2.3.5. User Error1.2.4. Missing Wizards or Import/Export/Link Options1.2.4.1. Missing or Broken Wizards1.2.4.2. Missing Import/Export/Link Options1.2.5. Agonies of a Sick Installation1.3. ERROR MESSAGES1.3.1. #Name? and #Error?1.3.1.1. Misspelled or Nonexistent Name1.3.1.2. Missing Equals Sign1.3.1.3. Name Is Out of Scope1.3.1.4. Incorrect Expression Syntax1.3.1.5. Use of Visual Basic Constants1.3.1.6. Empty Query or Subform1.3.1.7. Parameter Query Embedded in Expression1.3.1.8. Wrong Syntax for Subform Reference1.3.1.9. Circular References1.3.2. #Num!, #Div/0!, #Deleted, and #Locked1.3.3. Enter Parameter Value1.3.4. No Access License1.3.5. User-Defined Type Not Defined1.3.6. Missing References1.3.7. Disk or Network Errors1.3.8. Database Is Read-Only or File Is Already in Use
2. Performance, Versions, Security, and Deployment
2.1. PERFORMANCE2.1.1. Database Bloat from Images or OLE Objects2.1.2. Why Is My Database So Slow?2.1.2.1. Name AutoCorrect and Subdatasheet Name2.1.2.2. Queries2.1.2.3. Reports2.1.2.4. Forms2.1.2.5. Functions2.1.3. Why Is My Split Database So Slow?2.1.3.1. Maintain a Persistent Connection to the Backend2.1.3.2. Make the Backend Easy to Get To2.1.3.3. Check Your Anti-Virus Software2.1.4. XP Home Is Way Slow2.1.5. Access with SQL Server Is Still Slow2.2. VERSIONS, UPGRADES, AND CONVERSION2.2.1. Access Works on One PC but Not Another2.2.2. Access/Windows Compatibility Issues2.2.3. Converting Old Databases2.2.4. Running the Same Database in Multiple Versions of Access2.2.5. Running Different Versions of Access on the Same PC2.2.6. Running the Right Version of Access2.2.7. This Upgrade Is Killing Me!2.2.8. Many Offices, Many Library Incompatibilities2.3. SECURITY2.3.1. Access Security Is Weak2.3.2. Upgrading Secured Databases2.3.3. Database Lockout2.3.3.1. Simple Security2.3.3.2. User-Level Security2.3.3.3. Missing or Corrupted Workgroup File2.3.3.4. Third-Party Products/Services2.3.4. Administering User-Level Security Correctly2.3.4.1. Create a New Workgroup File2.3.4.2. Create a New Database Administrator2.3.4.3. Create a New, Secured Database2.3.4.4. Create Custom Groups2.3.4.5. Assign Permissions to Groups2.3.4.6. Create Users2.3.4.7. Restore the Default Workgroup (Optional)2.3.5. Undo Botched Security2.3.6. Frontend Secure, Backend Unprotected2.3.7. First Login Fails After Securing Database2.3.8. Too Many Logins2.3.9. Splitting a Secured Database2.3.10. Security Fails on Network Deployment2.3.11. “Unsafe Expressions” Warning2.3.12. Creating Digital Signatures2.4. ENTERPRISE DEPLOYMENT AND NETWORKED DATABASES2.4.1. Keeping Access Running Smoothly2.4.2. Automating Maintenance Chores2.4.3. Best Practices for Access on Networks2.4.3.1. Minimize Network Connections2.4.3.2. Optimize Your Design2.4.3.3. Hardware Considerations2.4.3.4. Oplocks2.4.4. Getting Around Jet’s 2-GB Limit2.4.5. Avoiding Manual DSN Setup for ODBC2.4.6. VB Code Fails After Split2.4.7. Merging Two Frontends2.4.8. Can’t Get Replication to Work2.4.9. Replication Fails on Frontend Objects2.4.10. Corrupted Replicas
3. Data, Tables, and Database Design
3.1. DATABASE DESIGN AND DATA INTEGRITY3.1.1. Table Design 1013.1.2. Relationship Angst3.1.3. I Don’t Want to Design a Database from Scratch3.1.4. Bad Field Names3.1.5. Flaws in the Decimal Data Type3.1.6. AutoNumber Nightmares3.1.7. Can’t Create a Relationship3.1.8. Can’t Delete Records3.1.9. Default Values Don’t Apply to Existing Records3.1.10. Simple Validation Rules3.1.10.1. The Like Operator3.1.10.2. The Between Operator3.1.10.3. The In Operator3.1.10.4. Record-Level Rules3.1.11. Complex Validation Rules3.1.12. Subtypes and Supertypes3.1.13. Duplicate Records3.1.14. Relationships Window Ghosts3.1.15. Relationships Window Layout Distress3.1.16. Attack of the Nonexistent Tables3.2. IMPORTING, LINKING, AND EXPORTING DATA3.2.1. Errors with Imported Data3.2.2. Linked Spreadsheet Woes3.2.3. Data Incorrectly Imported as Dates3.2.4. Obscure Excel Import Errors3.2.5. Unhelpful Error Messages During Import3.2.6. Can’t Import Word Tables3.2.7. Copy/Paste to Excel Is Broken3.2.8. Exporting Reports Produces Weird Numbers3.2.9. Hidden Apostrophes in Exported Data3.2.10. Exported Numeric Data Is Truncated3.2.11. Exported Text Data Is Truncated3.2.12. Miscellaneous Export Annoyances3.2.13. Putting Data on the Web3.2.13.1. Static and Read-Only3.2.13.2. Dynamic and Read-Only3.2.13.3. Dynamic and Editable3.2.14. Automating Import/Export3.2.15. Exporting Data on the Fly
4. Queries
4.1. QUERY BASICS4.1.1. Query a Single Table4.1.2. Query Multiple Tables4.1.3. Meaningless Column Names4.1.4. Create a Calculated Field4.1.5. Create a Totals Query4.1.6. Find the Properties Sheet for a Query4.1.7. Divide and Conquer4.1.8. Spurious Joins4.1.9. Limit the Number of Records Returned4.1.10. Avoid Duplicates in a Query4.1.11. Find Duplicate Records4.1.12. Count Yes/No Answers4.1.13. Jump to SQL View4.1.14. Speed Up Slow Queries4.2. QUERY MISFIRES4.2.1. Data Is Missing from a Multi-Table Query4.2.2. Query Has No Data or Has Wrong Data4.2.3. “Aggregate Function” Error4.2.4. Totals Query Gives Incorrect Result4.2.5. Sort Order Is Out of Order4.2.6. Left Join Doesn’t Work4.2.7. “Join Expression Not Supported” and “Ambiguous Outer Joins” Errors4.2.8. Input Mask Nixes Queries4.3. PARAMETER AND CROSSTAB QUERIES4.3.1. Parameter Queries with Wildcards4.3.2. Parameter Queries and Blank Responses4.3.3. User-Friendly Parameter Queries4.3.4. Parameter Queries That Accept Lists4.3.5. Parameters in Crosstab Queries4.3.6. Sorting Crosstab Rows Based on Totals4.3.7. Crosstab Queries with Multiple Values4.4. TECHNIQUES FOR DIFFICULT QUERIES4.4.1. Comparing Different Rows4.4.2. Working with Hierarchies4.4.3. Working with Ranges4.4.4. Full Outer Joins4.4.5. Divide and Conquer with Subqueries4.4.6. Finding Rows That Don’t Exist4.4.7. Working with Calendar Dates4.4.8. Missing SQL
5. Forms
5.1. WORKING IN DESIGN VIEW5.1.1. Activating the Wizards5.1.2. Edit Many Controls at Once5.1.3. Leaving the Properties Sheet Open5.1.4. Where Are the Form’s Properties?5.1.5. Attached Labels5.1.6. Fix Combo and List Box Names5.1.7. Subform Is Blank in Design View5.1.8. Form Opens Slowly in Design View5.2. FORM DESIGN5.2.1. Building the Right Form5.2.1.1. Unrelated Table5.2.1.2. One-to-Many5.2.1.3. One-to-Many-to-Many5.2.1.4. Many-to-One5.2.1.5. Many-to-Many5.2.2. Synchronizing Subforms5.2.3. Synchronizing Two Forms5.2.4. Create Dialog Box Input Forms5.2.5. Display Subform Values on the Main Form5.2.6. Hide an Empty Subform5.2.7. Reusing Subforms5.3. CREATE FORMS YOUR USERS WILL LOVE5.3.1. Simplify Data Entry5.3.1.1. Autofill Dates5.3.1.2. Autofill Related Fields5.3.1.3. Define the Tab Order5.3.1.4. Distinguish Noneditable Fields5.3.1.5. Keyboard Shortcuts for Find Boxes5.3.2. Open a Form to a New Record5.3.3. Refresh Data Automatically5.3.4. Hide Foreign Keys5.3.5. Find Records Faster5.3.6. Handle Items Not in a Combo Box5.3.7. One Form, Many Screen Resolutions5.3.8. Option Groups Don’t Allow Text Values5.3.9. Can’t Change Column Headings for Datasheet View5.3.10. Set Focus Doesn’t Work5.3.11. Enable Null Values in a Combo Box5.3.12. Fix Scrolling in Combo Boxes5.3.13. Use Arrow Keys in Combo Boxes5.3.14. Speed Up Slow Combo Boxes5.3.14.1. Restrict with a Separate Control5.3.14.2. Limit Rows with Type-Ahead5.3.15. The Limits of Conditional Formatting5.3.16. Form Is Blank in Form View5.3.17. Edit User-Entered Data5.3.18. Can’t Save Changes to Data5.3.19. Some Forms Shouldn’t Be Editable by Default5.3.20. Too Many Controls on One Page5.3.21. Carry Data Forward to the Next Record
6. Reports, Mailing Labels, and Charts
6.1. GENERAL REPORT ANNOYANCES6.1.1. Report Wizard Isn’t Working Its Magic6.1.2. Build a Report from Scratch6.1.3. Report Preview Is Too Small6.1.4. Unexpected Parameter Boxes6.1.5. Calculated Field Shows #Error?6.1.6. Truncated Memo Field6.1.7. Reports with No Data6.1.8. Error 2501: The OpenReport Action Was Canceled6.1.9. Filter with Parameters6.1.10. Number Records Consecutively6.1.11. Export to Word/RTF6.1.12. Give Up and Try Excel Instead6.2. PAGE LAYOUT AND PRINTING6.2.1. Too Much Blank Space6.2.2. Every Other Page Is Blank6.2.3. Blank Page at End of Report6.2.4. Column Layout Issues6.2.5. At Least One Printer Required6.2.6. Using the Default Printer6.3. SORTING, GROUPING, AND SUBREPORTS6.3.1. Records Aren’t Sorted Like the Underlying Query6.3.2. Orphaned Group Header6.3.3. Subreport Loses Its Headings6.3.4. Total Not Allowed in Page Footer6.3.5. Crosstab Headings Don’t Match6.3.5.1. Fixed Column Headings6.3.5.2. Dynamic Column Headings6.4. MAILING LABELS6.4.1. Off to See the Wizard6.4.2. Custom Label Templates6.4.3. Too Much Label, Not Enough Margin6.4.4. Eliminate Blank Lines, and Other Label Tweaks6.4.5. Prompt for Trim Function6.4.6. Zip+4 Codes Run Together6.4.7. More Than One Label for Each Record6.4.8. Printing Partial Sheets of Labels6.5. CHARTS AND GRAPHS6.5.1. Where’s Charting?6.5.2. Chart Only Shows Sample Data6.5.3. Chart Changes Are Gone6.5.4. Incorrect Data in Chart6.5.5. Chart Is Blank6.5.6. Fixed-Scale Charts6.5.7. Control Charts in Code6.5.8. Link a Chart from Excel

7. Expressions, Macros, Code Modules, and Custom Controls
7.1. EXPRESSIONS7.1.1. .Dot, !Bang, and [Bracket]7.1.2. Expressions That Go Blank7.1.3. Tangled Up in Null7.1.4. Debugging Expressions7.1.4.1. Rule 1: Simplify, Simplify7.1.4.2. Rule 2: Check the Documentation7.1.4.3. Rule 3: Examine Your Data7.1.5. Mysterious Syntax Errors7.1.6. “Quotes”, #Quotes#, and More #%&@!! “"""Quotes"""”7.1.7. Like, In, and Between Operators7.1.8. “Like” Operator Changes to “Alike”7.1.9. Expression-Building Blues7.1.10. Dollar$ Sign$ Functions7.1.11. [Brackets] Versus “Quotes”7.1.12. Last Name, First7.1.13. Dates! Dates! Dates!7.1.13.1. Define a Date Range (Previous Month, Last Two Weeks, and so on)7.1.13.2. Find a Specific Date (Last Day of Month, First Day of Previous Month, and so on)7.1.13.3. Find the Elapsed Time7.1.13.4. Find Overlapping Date Ranges7.1.14. Compare Dates, Ignore Times7.1.15. Working Days Not Working7.1.16. Refer to Subform Properties7.2. MACROS AND CODE MODULES7.2.1. Find a Macro Action7.2.2. Tame the Visual Basic Editor7.2.2.1. Navigating Code and Modules7.2.2.2. Getting Help7.2.2.3. Debugging7.2.3. Find a Visual Basic Function7.2.4. Pick the Right Event7.2.5. Apostrophe Errors7.2.6. Disable Confirmation Dialogs7.2.7. Pause Macro or Code7.2.8. Better Error Handling7.2.8.1. Error Arises in Access7.2.8.2. Error Arises in Custom VB Code7.2.9. Save a Record7.2.10. Lost Data7.2.11. Mysterious Syntax Errors, Part Deux7.3. CUSTOM CONTROLS AND EXTERNAL APPLICATIONS7.3.1. Mysteries of ActiveX Controls7.3.2. ActiveX Control Is Missing Events7.3.3. Calendar Controls7.3.4. File Choosers7.3.4.1. Using the Windows API7.3.4.2. Using the FileDialog Object7.3.4.3. Using the Common Dialog Control7.3.5. Use Excel Functions7.3.6. Create Email Links7.3.7. Send Email from Access
A. Appendix
A.1. Installation ChecklistA.1.1. Keep Windows Up-to-DateA.1.2. Keep Office Up-to-DateA.1.3. Keep Jet 4.0 Up-to-DateA.1.4. Install All Access FeaturesA.1.5. Re-Register DLLsA.1.6. Install ODBC DriversA.2. Access NewsgroupsA.3. Visual Basic FunctionsA.4. Macro ActionsA.5. Events
Glossary
About the Authors
About the Authors
Colophon
Copyright

Content preview from Fixing Access Annoyances

Chapter 6. Reports, Mailing Labels, and Charts

It should go without saying that the information you get out of your database is only as useful as the information you put into it. However, we’ve noticed that when people are planning a database, they often fail to budget adequate time for creating reports. While Access’s reporting features are powerful, complex issues lurk behind every decision—and it’s just plain difficult to make your reports look right, too. If your experience is anything like ours, you will spend more time designing reports (previewing and tweaking and previewing again) than you ever intended. And you will be annoyed.

This chapter starts by dissecting general report annoyances—problems that almost everyone will have encountered by the time they’ve created a few reports. Since most Access users turn first to the Report Wizard, we’ll start there—and once we’ve covered the basics, we’ll move on to parameters, memo fields, and other general annoyances. Then we’ll delve into more specialized issues, in the “Page Layout and Printing” and “Sorting, Grouping, and Subreports” sections. Finally, we’ll tackle the knotty problems people encounter in two particularly aggravating areas: mailing labels and charts.

GENERAL REPORT ANNOYANCES

Report Wizard Isn’t Working Its Magic

THE ANNOYANCE: The Report Wizard asks me a lot of questions I don’t know how to answer. Worse, no matter what I do, I can’t get it to create the report I want.

THE FIX: The Report Wizard is a fairly flexible ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.

Read now

Unlock full access

More than 5,000 organizations count on O’Reilly

O’Reilly covers everything we've got, with content to help us build a world-class technology community, upgrade the capabilities and competencies of our teams, and improve overall team performance as well as their engagement.

Julian F.

Head of Cybersecurity

I wanted to learn C and C++, but it didn't click for me until I picked up an O'Reilly book. When I went on the O’Reilly platform, I was astonished to find all the books there, plus live events and sandboxes so you could play around with the technology.

Addison B.

Field Engineer

I’ve been on the O’Reilly platform for more than eight years. I use a couple of learning platforms, but I'm on O'Reilly more than anybody else. When you're there, you start learning. I'm never disappointed.

Amir M.

Data Platform Tech Lead

I'm always learning. So when I got on to O'Reilly, I was like a kid in a candy store. There are playlists. There are answers. There's on-demand training. It's worth its weight in gold, in terms of what it allows me to do.

Mark W.

Embedded Software Engineer

Publisher Resources

ISBN: 059600852XErrata Page

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design

Business

Soft Skills

Fixing Access Annoyances

by Evan Callahan, Phil Mitchell

Chapter 6. Reports, Mailing Labels, and Charts