Home Page Icon
Home Page
Table of Contents for
5. Programming Access
Close
5. Programming Access
by Matthew MacDonald
Access 2013: The Missing Manual
The Missing Credits
About the Author
About the Creative Team
Acknowledgements
The Missing Manual Series
Introduction
What You Can Do with Access
The Two Sides of Access
Access vs. Excel
Access vs. SQL Server
The Access Ribbon
Using the Ribbon with the Keyboard
The Quick Access Toolbar
Backstage View
The Changes in Access 2013
About This Book
About the Outline
About→These→Arrows
Contextual Tabs
Drop-Down Buttons
Backstage View
Ordinary Menus
About Shortcut Keys
About the Online Resources
Missing CD
Registration
Feedback
Errata
Safari® Books Online
1. Storing Information in Tables
1. Creating Your First Database
Understanding Access Databases
Starting a Database
Building Your First Table
Creating a Simple Table
Editing a Table
Edit Mode
Datasheet Shortcut Keys
Cut, Copy, and Paste
Saving Databases
Making Backups
Saving a Database with a Different Name
Saving a Database in a Different Format
Shrinking a Database
Opening Databases
Designating a Database as Trusted
Opening More Than One Database at Once
Opening a Database Created in an Older Version of Access
The Navigation Pane
Browsing Tables with the Navigation Pane
Managing Database Objects
2. Building Smarter Tables
Understanding Data Types
Design View
Organizing and Describing Your Fields
How Updates Work in Design View
Access Data Types
Short Text
Text Length
Long Text
Formatted Text
Number
Number Size
Number Formatting
Currency
Date/Time
Custom Date Formats
Yes/No
Hyperlink
Attachment
AutoNumber
Using AutoNumbers without Revealing the Size of Your Table
Using Replication IDs
Calculated
The Primary Key
Creating Your Own Primary Key Field
Six Principles of Database Design
1. Choose Good Field Names
2. Break Down Your Information
3. Include All the Details in One Place
4. Avoid Duplicating Information
5. Avoid Redundant Information
6. Include an ID Field
3. Mastering the Datasheet: Sorting, Searching, and Filtering
Datasheet Customization
Formatting the Datasheet
Rearranging Columns
Resizing Rows and Columns
Hiding Columns
Freezing Columns
Datasheet Navigation
Sorting
Sorting on Multiple Fields
Filtering
Quick Filters
Filter by Selection
Filter by Condition
Searching
Advanced Editing
The Spell Checker
Spell-Checking Options
AutoCorrect
Special Characters
Printing the Datasheet
Print Preview
Moving Around the Print Preview
Changing the Page Layout
Fine-Tuning a Printout
4. Blocking Bad Data
Data Integrity Basics
Preventing Blank Fields
Blank Values and Empty Text
Setting Default Values
Preventing Duplicate Values with Indexes
Multifield Indexes
Input Masks
Using a Readymade Mask
Creating Your Own Mask
Validation Rules
Applying a Field Validation Rule
Writing a Field Validation Rule
Validating Numbers
Validating Dates
Validating Text
Combining Validation Conditions
Creating a Table Validation Rule
Lookups
Creating a Simple Lookup with Fixed Values
Adding New Values to Your Lookup List
5. Linking Tables with Relationships
Relationship Basics
Redundant Data vs. Related Data
Matching Fields: The Relationship Link
Linking with the ID Column
The Parent-Child Relationship
Using a Relationship
Defining a Relationship
Editing Relationships
Referential Integrity
Blank Values for Unlinked Records
Cascading Deletes
Cascading Updates
Navigating a Relationship
Lookups with Related Tables
More Exotic Relationships
One-to-One Relationship
Many-to-Many Relationship
Junction Tables
Multi-Value Fields
Relationship Practice
The Music School
Identifying the Tables
Identifying the Relationships
Getting More Detailed
The Chocolate Store
The Product Catalog and Customer List
Ordering Products
2. Manipulating Data with Queries
6. Queries That Select Records
Query Basics
Creating Queries
Creating a Query in Design View
Building Filter Expressions
Getting the Top Records
Creating a Simple Query with the Query Wizard
Understanding the SQL View
Analyzing a Query
Creating a Union Query
Queries and Related Tables
Joining Tables in a Query
Outer Joins
Finding Unmatched Records
Multiple Joins
7. Essential Query Tricks
Calculated Fields
Defining a Calculated Field
Simple Math with Numeric Fields
Date Fields
Order of Operations
Expressions with Text
Query Functions
Using a Function
Nested Functions
The Expression Builder
Formatting Numbers
More Mathematical Functions
Text Functions
Date Functions
Dealing with Blank Values (Nulls)
Query Parameters
8. Queries That Summarize Data
Totals Queries
Grouping in a Totals Query
Joins in a Totals Query
Crosstab Queries
Creating a Crosstab Query with the Wizard
Creating a Crosstab Query from Scratch
9. Queries That Change Tables
Understanding Action Queries
Testing Action Queries (Carefully)
The Action Query Family
Update Queries
Creating an Update Query
Append Queries
Creating an Append (or Make-Table) Query
Getting AutoNumbers to Start at Values Other Than 1
Delete Queries
Creating a Delete Query
Deleting Linked Records
Tutorial: Flagging Out-of-Stock Orders
Finding Out-of-Stock Items
Putting the Orders on Hold
3. Printing Reports
10. Creating Reports
Report Basics
Creating a One-Click Report
Building a Report in Layout View
The Many Views of a Report
Rearranging a Report
Printing, Previewing, and Exporting a Report
Previewing a Report
Export Formats
Exporting a Report to a PDF
Exporting a Report to a Different Format
Formatting a Report
Applying a Theme
Formatting Columns and Column Headers
Formatting Numeric Fields
Alternating Row Formatting
Gridlines
Borders
Margin and Padding
Background Image
Conditional Formatting
Data Bars
Filtering and Sorting a Report
Filtering a Report
Sorting a Report
11. Designing Advanced Reports
Improving Reports in Design View
The Design View Sections
Understanding Controls
Moving Fields Out of a Layout
Adding More Controls
Creating a Report from Scratch (in Design View)
The Report Wizard
The Label Wizard
Fine-Tuning Reports with Properties
Modifying Common Properties
Expressions
Grouping
Report Grouping
Fine-Tuning with the “Group, Sort, and Total” Pane
Sort by
Group on
With a on Top / From Smallest to Largest
By Entire Value
With … Totaled
With Title
With a Header Section / With a Footer Section
Keep Group Together on One Page
Multiple Groups
4. Building a User Interface with Forms
12. Creating Simple Forms
Creating Forms
Building a Simple Form
Using AutoNumber Fields in Forms
Showing Pictures from a Table
Using Forms
Finding and Editing a Record
Adding a Record
Deleting a Record
Printing Records
Sorting and Filtering in a Form
Sorting a Form
Filtering a Form
Using the “Filter by Form” Feature
Saving Filters for the Future
Creating Fancy Forms
Manipulating Layouts
Inserting Columns and Rows
Splitting and Merging Cells
Liberating Controls from Layouts
Using Tabular Layouts
Showing Multiple Records in any Form
Split Forms
More Useful Form Properties
The Form Wizard
13. Designing Advanced Forms
Customizing Forms in Design View
Form Sections: The Different Parts of Your Form
Adding Controls to Your Form
The Control Gallery: A Quick Tour
Arranging Controls on Your Form
Aligning Controls
Sizing Controls
Spacing Controls
Controls that Overlap
Anchoring: Automatically Resizing Controls
Making Controls as Wide as the Form
Making a Single Control as Large as Possible
Tab Order: Making Keyboard Navigation Easier
Taking Control of Controls
Locking Down Fields
Prevent Errors with Validation
Performing Calculations with Expressions
Organizing with Tab Controls
Going Places with Links
Showing Web Pages with the Web Browser
Navigating with Lists
Performing Actions with Command Buttons
Forms and Linked Tables
Table Relationships and Simple Forms
The Subform Control
Creating Customized Subforms
14. Building a Navigation System
Mastering the Navigation Pane
Configuring the Navigation List
Better Filtering
Hiding Objects
Using Custom Groups
Searching the Navigation List
Building Forms with Navigation Smarts
Custom Menu Forms
Designating a Startup Form
Compound Forms
Showing All Your Forms in a List
Navigation Forms
Creating a Single-Level Navigation Form
Creating a Two-Level Navigation Form
Fine-Tuning Your Navigation Buttons
Linking to Related Data
Showing Linked Records in Separate Forms
Showing More Detailed Reports with Links
5. Programming Access
15. Automating Tasks with Macros
Macro Essentials
Creating a Macro
Running a Macro
Editing a Macro
Finding Actions in the Action Catalog
Collapsing, Expanding, and Grouping Macro Actions
Solving Macro Problems
Macros and Security
Unsafe Macro Actions
Trusted Databases
Temporary Trust
The Trust Center
Setting Up a Trusted Location
Three Macro Recipes
Find a Record
Print a Report
Email Your Data
Managing Macros
Submacros
Assigning a Macro to a Keystroke
Configuring a Startup Macro
16. Connecting Macros to Forms and Tables
Macro-Powered Forms
Understanding Events
Attaching a Macro to an Event
Reading Arguments from a Form
Changing Form Properties
Conditional Macros
Building a Condition
Validating Data with Conditions
More Complex Conditional Macros
Data Macros
Table Events
Validation with Before Events
Keeping a Change Log
Debugging Data Macros
An Advanced Data Macro: Synchronizing Data
17. Automating Tasks with Visual Basic
The Visual Basic Editor
Adding a New Module
Writing the Simplest Possible Code Routine
Putting Code in a Form
Responding to a Form Event
Calling the Code in a Module
Reading and Writing the Fields on a Form
Understanding Objects
Properties
Methods
Events
Using Objects
Indicating That a Record Has Changed
Creating a Mouseover Effect
18. Writing Smarter Code
Exploring the VB Language
Storing Information in Variables
Making Decisions
Repeating Actions with a Loop
Creating Custom Functions
Putting It All Together: A Function for Testing Credit Cards
Dealing with Trouble
Debugging
Error Handling
Deeper into Objects
The DoCmd Object
Converting a Macro to VB Code
Using VB to Run a Better Business
Keeping a Running Total
Getting Price Information
Adding a New Product During an Order
Managing Order Fulfillment
Updating Stock Numbers
6. Going Large: Access Databases for Many Users
19. Sharing a Database on Your Network
Opening Up Your Database to the World
How Access Sharing Works
Preparing Your Database
Understanding Split Databases
Splitting a Database with the Wizard
How Linked Tables Work
Manually Splitting a Database
Locking Down Your Front End
Sharing a Database with People Who Don’t Own Access
Playing Well with Others
Seeing Changes as They Happen
Dealing with Editing Conflicts
Using Locks to Stop Overlapping Edits
Opening a Database in Exclusive Mode
Data Corruption
Diagnosing (and Fixing) Corrupt Databases
Preventing Corruption
Securing Your Database
Password-Protecting Your Database
Passwords and Split Databases
Using Windows File Security
20. Building an Access Web App
Assessing Web Apps
Preparing for Web Apps
Building a Simple Web App
Creating a Web App in Access
Creating a Web App in SharePoint
Adding a Table
Entering Data in a Table
Back to the Home Tab
Running Your Web App
Editing Data in a Web App
Understanding the Web App Navigation System
Customizing Views
Modifying a View
Rearranging Controls
Configuring Controls
Changing the List
Adding and Removing Controls
Creating More Advanced Views
Adding a New View
Using a View to Create a Welcome Page
Views That Use Queries
Views That Use Related Tables
Adding Child Records
Adding Parent Records
Tutorial: Creating a Web App from an Access Database
Creating a Web App Based on a Desktop Database
Dealing with a Many-to-Many Relationship
Creating a Query to Get Related Data
Creating a View for Your Query
Showing Your View in a Popup
21. Linking Access to SQL Server
How Access and SQL Server Can Work Together
Should You Switch to SQL Server?
How Access and SQL Server Work Together
A Cheaper SQL Server
Getting Started with SQL Server Express
Preparing for SQL Server Express
Downloading SQL Server Express
Installing SQL Server Express
Putting SQL Server on the Network
Building a SQL Server Database
Creating a New Database
Adding a Table
Creating Relationships Between Tables
Editing SQL Server Tables in Access
Setting Up a Connection to SQL Server
Managing Linked Tables
Migrating an Access Database to SQL Server
Installing SSMA
Transferring an Access Database to SQL Server
22. Linking Access to SharePoint
Understanding SharePoint
What You Can Do in SharePoint
Adding an App to a SharePoint Site
SharePoint and Access
Creating a List in SharePoint
Exporting a Table to SharePoint
Importing a List in Access
Moving a Whole Database to SharePoint
Editing Your SharePoint Data in Access
23. Importing and Exporting Data
Case for Importing and Exporting
Understanding Exports
Understanding Imports
Using the Clipboard
Copying a Table from Access to Somewhere Else
Copying Cells from Excel into Access
Import and Export Operations
Importable File Types
Importing Data
Importing from an Excel File
Importing from a Text File
Exportable File Types
Exporting Data
Reusing Import and Export Settings
Access and XML
What Is XML, Really?
Three Rules of XML
The Prolog
Elements
Nesting
XML Files and Schemas
The Access XML Story
Exporting to an XML File
Importing from an XML File
7. Appendix
A. Customizing Access
Adding Your Favorites to the QAT
Adding Buttons
Customizing Specific Databases
Personalizing the Ribbon
Changing Existing Tabs
Creating Your Own Tab
Saving and Reusing Your Custom Ribbon
Index
About the Author
Copyright
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
14. Building a Navigation System
Next
Next Chapter
15. Automating Tasks with Macros
Part 5. Programming Access
Chapter 15
Chapter 16
Chapter 17
Chapter 18
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