Access 2007-2013
Access 2000-2003
Access Seminars
TechHelp Support
Tips & Tricks
Access Forum
Course Index
Topic Glossary
Insider Circle
 
Home   News   Tips   Glossary   Templates   Forums   Help   Logon   Order   Contact Us  
 
NEW Release: Access Expert 26    dismiss
 
 
Microsoft Access
Searchable Topic Index
 
You can use this index to see what topics are covered in each class. If you are looking for a particular course topic, you can use your browser's search function to find that topic. You can use the Find feature in your browser by pressing CTRL-F or clicking on Edit > Find.
 
Access 2007-2013
Access 2010 Beginner 1

00. Introduction (10:17)

01. Database Terminology (11:56)
What is a database?
Progression from paper
Benefits of Access
Parts of an Access database
Table
Fields / Columns
Records / Rows
Query
Form
Report
Macro
Module

02. Planning Your Database (9:04)
Consider your needs
What tables do you need
What fields are in each table
No order info in customer table
Separate tables
Draw your forms out
Gather your printed reports

03. The Access Interface (11:02)
Starting Access
Create a new blank database
Parts of the screen
Title bar
Window control buttons
The Ribbon
Collapse the Ribbon
Quick Access Toolbar
Navigation Pane
Status Bar
Object Pane

04. Customer Table, Part 1 (10:52)
Create Table
Datasheet View
Table Design
Field Name
Data Type
Description
Field naming conventions
Text
Memo
Numbers
Date/Time
Currency
Yes/No
AutoNumber
OLE Object
Attachment
Hyperlink
Calculated Field
Lookup Wizard

05. Customer Table, Part 2 (12:14)
Text or Number?
Long Integer
Double
Why use an ID field?
How to move a field in the table
Save your table
Table naming conventions
Primary Key

06. Entering Data, Part 1 (11:09)
Typing sample data
CustomerID (New)
Resize a column
Two-character states
Only digits in phone numbers
Percentages are fractions of 1
Date or Time or Both
##### in a field
Date picker
mm/dd defaults to current year
Two-digit year defaults 00-29
Spacebar to check box
Horizontal scroll bar
SHIFT-F2 to open Zoom window
Switching back to Design View

06. Entering Data, Part 1 (11:09)
Typing sample data
CustomerID (New)
Resize a column
Two-character states
Only digits in phone numbers
Percentages are fractions of 1
Date or Time or Both
##### in a field
Date picker
mm/dd defaults to current year
Two-digit year defaults 00-29
Spacebar to check box
Horizontal scroll bar
SHIFT-F2 to open Zoom window
Switching back to Design View

07. Entering Data, Part 2 (9:10)
No data is better than bad data
Copy sample data from web site
Selecting an entire record
Paste data into your table
Delete a record
AutoNumbers are NOT reused
Download copy of database
Security Warning
Enable Content
Do you have to save records?
Pencil indicates editing record
Dirty records
Saving layout changes
Moving a column in datasheet view

08. Sorting & Filtering (7:18)
Sorting a single column of data
Text, numeric, date sorts
Remove Sort button
Filtering records
Show just customers from NY
Remove Filter button
Printing a table
File > Print
Unfiltered
Why Queries are better

09. Customer Queries (15:16)
Create > Query Design
Show Table Window
Adding a table to your query
Drag fields to the query columns
Run the query
Design View
Add field with a double-click
Sort a field
Multiple field sort
Sort by Last Name then First Name
Sort order left to right
Moving a query column
Saving a query (CTRL-S)
Adding a Criteria
Show only customers from NY
File > Save Object As
Opening multiple queries
Switching between tabs
Change "NY" to "PA"
Can I edit data in a query?
Must I make a query for each state?
What is the "*" asterisk for?

10. Customer Form (14:21)
Why use forms?
What are forms good for?
Create a simple customer form
Different ways to build forms
Single Form
Label and Text Box
Form View
Layout View
Resizing Text Boxes
Layout View uses Rows & Columns
Save Form
Delete Form Title & Logo
Open in Form View
Editing Data
Using Navigation Buttons
Pencil indicates Dirty record
Record Selector
Delete a Record
Create a Multiple Items form
Continuous Forms
Create a Split Form

11. Customer Reports (14:08)
Create a simple customer report
Types of reports
Modify report title
Print Preview
Zoom Controls
Paper Size
Margins
Send as Email Attachment
Save as PDF
Layout View
Report View
Build report from a query
Report Wizard for Single Report
Delete a report
Mailing Labels
Label Wizard
Setup a Prototype Label

12. Review (5:33)

Access 2010 Beginner 2

00. Introduction (10:10)

01. Getting Started (12:26)
Download sample database
UNZIP the database file
Enable Content button
Setting up a TRUSTED location
Database Shortcut on Desktop
Quick Review of Level 1

02. Form Design 1 (12:13)
Building forms from scratch
Form design
Add existing fields
Field list
Switch to Form View
Switch to Design View
Adding fields to form
Deleting a field
Click and drag method
Add by double-click method
Add multiple fields
Shift-click method
Ctrl-click method
Move text box without label
Move label without text box
Delete just the label
Resize text box or label
Edit the text of a label

03. Form Design 2 (12:52)
The Grid dots
Sizing objects on the Grid
Select multiple objects
Resize, move multiple objects
Close the Field List
Resize the Form itself
Save form

04. Form Formatting 1 (10:17)
Change Form background color
Textbox background color
Label foreground color
Select using the rulerbar
Layout View
Align Left, Center, Right
Ctrl-Click to select objects

05. Form Formatting 2 (8:42)
Themes
Standard Color vs Theme Color
More Colors
Full Color Palette
Shape Outline
Line Color
Line Thickness
Line Type
Property Sheet Overview
Special Effect Property
Shadowed
Change Font

06. Questions From Students (10:26)
What is an LACCDB File?
Show Hidden Windows Files
Overlapping Windows vs. Tabs
Pin Recent Database Files
Add data from 2 tables to a form
Change check box background color

07. Review (6:01)

Access 2010 Beginner 3

0. Intro (6:53)

1. Field Properties 1 (14:40)
Design View
Field Size
AutoNumber Field Size
What is a Replication ID
Text Field Size
Number Field Size
Byte
Integer
Long Integer
Single
Double
Decimal
Warning: data may be lost

2. Field Properties 2 (5:13)
Caption
Changes default form label
Changes column header in datasheet
Don't confuse with Description

3. Field Properties 3 (16:02)
Format Property
Number Formats
General
Currency
Euro
Fixed
Standard
Percent
Scientific
With percent 1 = 100%
Decimal Places
Rounded values displayed
Actual values stored
Currency Formats
Format might hide data
mm dd yyyy
hh nn ss
F1 for Access Help
List of Date Format Codes
Text Format
Phone Numbers
@@@-@@@-@@@@
Yes/No Format

4. Field Properties 4 (5:25)
Input Mask Phone Number
Placeholder characters
Store symbols in the mask?
Enter data
Warning message on invalid data
Date/Time Input Masks
No Wizard for numbers/currency
Help for Input Masks

5. Field Properties 5 (9:47)
Default Value
State of NY
NumEmployees 1
DiscountRate .05
Automatically set current date
Date() Function
Now() Function includes time
Yes/No Default Values

6. Field Properties 6 (16:16)
Validation Rule
Inequality Symbols
NULL Values not the same as ZERO
Existing data violates rule
Validation Text
ESC escape to cancel editing
Range of values
AND OR
BETWEEN keyword
Date Values #1/1/1980#
Date() Function in Validation Rule
Functions in Validation Text

7. Review (4:47)

Access 2010 Beginner 4

00. Introduction (6:07)

01. Field Properties 7 (10:42)
Required
No data is better than bad data
Data integrity rules
Existing data violates the new setting
Null Values vs. Empty String
Zero Length Text
Allow Zero Length
Access automatically trims spaces

02. Field Properties 8 (9:18)
Text Align
Left, Right, General, Distribute
Text Format
Plain Text
Rich Text
HTML Encoding
Change table, change form
Append Only Misleading
Show Column History

03. Field Properties 9 (14:44)
SmartTags - brief discussion
Unicode Compression - brief discussion
IME Mode - brief discussion
IME Sentence Mode - brief discussion
Show Date Picker
AutoNumber Increment or Random
Artificially inflating AutoNumbers
Indexing
Duplicates OK
No Duplicates
Which fields to index
When and when not to index

04. Database Maintenance (7:57)
Compact & Repair
2 GB Database Size Limit
Backing Up Your Database

05. Find & Replace (15:18)
Find, Replace
Look In Current Field
Current Document
Match Whole Field
Any Part of Field
Start of Field
Search: All, Up, Down
Match Case
Search Fields as Formatted
Search Box at Bottom of Table/Form
Replace v. Replace All
Backup Your Table First
How to Copy/Paste Table
Rename Table
Goto Button
First, Previous, Next, Last, New
Navigation Buttons
Select & Select All
CTRL-A

06. Questions From Students (9:59)
Can I get an AutoNumber back?
Compact & Repair trick
Can I create a custom AutoNumber?
Attach Label to Text Box
Associate Label with a Control
Add a Field to Table
Must add field to form manually
Renamed table fields usually rename in form
Deleted fields in table cause error

07. Review (4:44)

Access 2010 Beginner 5

00. Introduction (6:49)

01. Query Criteria 1 (16:30)
Multiple OR Conditions
Delete a query
Rename a query
"NY" OR "PA"
"NY" OR "PA" OR "TX"
IN ("NY","PA","TX","CA")
Criteria OR Rows
From NY and are Active
AND condition across columns
AND across, OR down
(State=NY) OR (State=PA AND Active=TRUE)
Customers with Credit Limit over $1000
From NY with $1000 or anybody with $1500
AND OR inside of a single critera
BETWEEN keyword

02. Query Criteria 2 (15:40)
NOT Keyword
NOT "NY"
Inequalitites Review
NOT "NY" AND NOT "OH"
"NY" AND "PA"
AND limits query results
OR expands query results
Show box
Hide fields from query
Prevent odd names like "Field1"
Move a query column
Dates in pound symbols
<#1/1/2005#
Recent customers
BETWEEN two dates
d/m/y or m/d/y regional settings
Whether to include endpoints
BETWEEN #1/1/05# AND #12/31/05#
Dates with times can be tricky
>=#1/1/05# AND <#1/1/06#
<Date()
Customers before 30 days ago
<Date()-30
<Now()
Became customers yesterday
>=Date()-1 AND <Date()

03. Query Criteria 3 (8:47)
Is Null
Is Not Null
ZipCode LIKE "142*"
State LIKE "N*"
Email LIKE "*amicron*"
Date LIKE "*/2012"
Date LIKE "5/*/2012"

04. Parameter Queries (11:45)
Separate query for each state
[Enter the state]
Missing field names from tables
Parameters with other criteria
Multiple parameters
Between [Start Date] and [End Date]
Like [Company Name]
Teach your users about wildcards
XYZ*
Like "*" & [Company Name] & "*"
Will cover String Concatenation Later
Build report based on this query

05. Questions from Students (7:55)
Top X Values
Top X% Values
Bottom X Values
Drop-Down List in Query Columns
Change Field
Why the Run Button for Queries
Format Query Field Output
Property Sheet for Query Fields

06. Review (6:40)

Access 2010 Beginner 6

00. Intro (6:49)

01. Address Info (16:25)
Customers with full address query
Is Not Null AND
Report Property Sheet
Change mailing label report RecordSource
Unexpected Enter Parameter Value
Report Too Wide
Some data may not be displayed
Not enough horizontal space on the page
Query of customers missing any address info
Is Null OR
Create Quick Report
Report Layout View
Resizing Columns
Moving Fields
Moving Page Number Block

02. Employee Table (13:05)
Distinct Separate Data Means New Table
"EmployeeID" vs. just "ID"
Copy Fields from Customer Table
Insert Rows in Table Design View
Social Security Number Input Mask
Picture
Attachment - Brief Discussion Of
OLE Object - What it is
Editing a hyperlink

03. Employee Form (13:53)
Create Form in Design View
Bring over fields for Employee Form
Copy Address Fields from CustomerF
CTRL-A To Select All Objects
Align to Grid
Size to Grid
Set Form Background Color
Format Painter
Double-Click to Keep Active
Resize a Group of Fields

04. Inserting Pictures (9:59)
Copy Image from Web Site to Desktop
JPG and GIF may show icon only
Convert to BMP using Microsoft Paint
Click and Drag to OLE Object
Stretch, Clip, Zoom

05. Employee ID Report (11:44)
Label Wizard
Manually Resizing Labels
Adding Employee Picture
Transparent Border
Bound Object Frame
Unbound Object Frame
Image Control
Insert a Static Company Logo
Editing Pictures in Place

06. Review (5:53)

Access 2010 Beginner 7

00. Introduction (7:09)

01. Navigation Forms (16:46)
Getting around in your database
Switchboards, discussion of
Navigation Form Pros and Cons
Create Simple Horizontal Nav Form
Add Forms to Nav Form
Add Reports to Nav Form
Rename Tabs
Change Form Caption
Rearrange / Move Tabs
Delete Tab
Horizontal Tabs, 2 Levels
Multi-Level Menu Tabs
Tabs by Object Type
Tabs by Data Type
Changing Colors
Display Navigation Form on Startup
Hide Navigation Pane on Startup
Bypassing Startup Options
Nesting Navigation Forms, discussion of

02. Main Menu Form 1 (12:01)
Create a Blank Unbound Form
Command Button Wizard
Form Operations > Open Form
Button Text (Caption)
Open Form Show All Records
Make Forms Remember Startup Position
Command Button Wizard not starting
Use Control Wizards Option
Why Use Command Buttons
Button to Close Form

03. Main Menu Form 2 (18:07)
Change Form Background Color
Form Properties
Form Caption Property
Form Property Sheet
Turn Off Record Selectors
Turn Off Navigation Buttons
Turn Off Scroll Bars
Main Menu Label
Formatting Label Options
Shape Outline
Special Effect
Shadowed Labels
Use Labels to Group Buttons
Position > Send to Back
Report Operations > Preview Report
Copy Logo to Main Menu Form

04. Customer List Form 1 (11:54)
Sorted List of Customers
NOT Using a Multiple Items Form
Create a Sorted Query for Customers
Form Design View with Blank Form
Setting a Form's Record Source Property
Query as a Record Source
Show Only Fields from Current Record Source
Form Properties
Default View > Continuous Forms

05. Customer List Form 2 (18:59)
Rearrange Fields into Columns
Put Labels in a Row Over Text Boxes
Form Header & Footer
Page Header & Footer, discussion of
Move Labels to Form Header
Access Should Save Form Position and Size
Command Button to Open Customer Form
Open the Form and Find Specific Data to Display
Which Fields Contain Matching Data
Making a Button Transparent
Turn off Navigation Buttons
Turn off Record Selectors
Set Scroll Bars to Vertical Only
Allow Additions
Allow Deletions
Allow Edits

06. Review (5:46)

Access 2010 Beginner 8

00. Intro (6:51)

01. Value List Combo Box (15:02)
Combo Boxes provide for uniform data entry
Select an option from a list
Add LeadSource to Customer Table
Make sure LeadSource is in Customer Form
Change RecordSource to CustomerT
Add Combo Box to Customer Form
I will type in the values that I want
Store that value in this field
Selecting values
Why is it called a Combo Box?
Limit to List
Edit List Items
Adding Items to the List
Default Value
Allow Value List Edits

02. Table-Based Combo Box (12:27)
Why you should NOT use a Value List Combo
Create a LeadSource Table
Combo Box get the values from another table
Which fields do you want in combo box
What sort order do you want
Save in the LeadSource field
Row Source Type: Table/Query
List Items Edit Form
Create a LeadSource Multiple Items Form

03. Multi-Column Combo Box (8:58)
Combo Box to pick States
Create a State Table
State Abbreviation and Name
Hide Key Column
Resize Column Widths
Which column to store in CustomerT
List Width
Column Widths
List Rows

04. Tab Order (10:58)
Show the Tab Order on CustomerListF
Rearrange Controls
Tab Order button
Detail Section
Auto Order
Manually Edit Tab Order
Tab Stop Property
Cycle Property
All Records
Current Record
Fix the Customer Form Tab Order

05. List Boxes (8:55)
Preferred Shipping Method
Create ShippingT
Shipping field in CustomerT
Change To > Combo Box
Don't use Table Lookup Wizards

06. Search Combo Box (2:39)
Turn on Form Header/Footer
Combo Box Find Record

07. Review (5:54)

Access 2010 Beginner 9

00. Intro (6:26)

01. Contact Table & Form (12:48)
What is a Contact
Why not use a Memo field?
Create Contact Table
Review of Now() Function
Create Contact Form
Enter Data into Contact Form
Notice Poor Data Integrity

02. Contact Query (11:39)
Bring in All Fields "ContactT.*"
Duplicate Fields
Show Box to Hide Column
Records from a Single Date
=#11/24/12# doesn't work
Between Keyword doesn't work
Inequality works
Today's Contacts with Date()
Date()+1
Yesterdays Contacts Date()-1
[Enter Date] in multiple places
Explicit Query Parameters

03. Contact Report 1 (14:54)
Building a Report from Scratch
Page Header, Footer
Detail Section
Print Preview
Problems with Layout View
Remove Box Borders
Transparent Shape Outline
Portrait vs. Landscape View
Margins
Horizontal Line
SHIFT to keep line strait
Printing Reports

04. Contact Report 2 (13:44)
Switch back to Portrait
Can Grow, Can Shrink
Force exact size for reports
String Concatenation
FirstName & LastName in Query
Trim Function
Concatenation in Report Fields
LastName, FirstName in Report

05. Miscellaneous (17:11)
Alternate Back Color
HTML Color Codes
Light Gray Line
Line Thickness
Line Type
Section Background Colors
Page Header vs. Report Header
Page Numbering
Page [Page] [Pages]
Report Footer
Confidentiality Notice
Add Buttons to Main Menu

06. Review (6:50)

Access 2010 Expert 1

00. Intro (7:13)

01. Relational Database Concepts (17:06)
What is a Relational Database
Why are Relationships Important
Flat File Databases
Key Fields
Primary Key
Foreign Key
Types of Relationships
One-to-one Relationships
One-to-many Relationships
Many-to-one Relationships
Many-to-many Relationships
Junction Table
Normalization

02. Relational Tables (14:53)
Making our contact management relational
Inserting a CustomerID in ContactT
Removing duplicated fields
Setup a Foreign Key
Moving fields in table design
Fixing Lead Sources
Fixing Shipping Methods

03. Relational Queries (14:04)
Add CustomerT to ContactQ
Ad Hoc Query Relationships
Automatic Join Line
Delete Relationship
Manually Create Relationship
Asterisk from One Table
Join Missing Customers
Join Types
Left Join / Outer Join
Joined fields from both tables are equal
Show All Records from CustomerT
Only those records from LeadSourceT
Where the joined fields are equal
Fix ContactR Report

04. Relational Combo Boxes (19:34)
Add Combo Box to Contact Form
Combo Box Based on Table
Combo Box Based on Query
Wizard is Slightly Different
Fix Lead Source Combo
Fix Shipping Combo

05. Review (04:50)

Access 2010 Expert 2

00. Intro (7:04)

01. Normalization (21:06)
What is Normalization
First Normal Form (1NF)
Flat Tables
Atomic Fields
Repeating Groups of Data
Entities (Entity)
Second Normal Form (2NF)
Update Anomalies
Third Normal Form (3NF)
Remove Derived Fields
Calculated Values
Denormalization
Improve Performance
Storing History Data

02. Explicit Relationships (14:00)
Global Relationships
Relationships Window
Add Tables
Edit Relationships Window
SubDatasheet
Subtable
Orphaned Child Records
Edit Relationship
Referential Integrity
Unable to Delete Parent

03. Cascade Update & Delete (13:16)
Cascade Updates
Why you should never need them
Cascade Deletes
Automatic Delete of Children
Backup Your Data!
Can't Delete Tables in Relationships
Arrange Tables in Relationship Window
Hide Tables
Delete Relationships
Relationships Report
Object Dependencies
Subdatasheet Table Properties
Subdatasheet Expanded
Subdatasheet Height
Subdatasheet Name
Link Child Fields
Link Master Fields

04. Values Across Forms (23:13)
Find Contacts for Current Customer
Command Button
Open Form
Find Specific Data To Display
Save Form Position
Set Values Between Forms
Forms Collection
Forms!FormName!FieldName
Default Value
Value of a Combo Box
Combo Box Columns
Bigger Notes Field in Form Footer
Move Combo Box to Form Footer
TabStop Property
Brief Intro to Expression Builder
Dirty Records Refresh Problem

05. Review (6:11)

Access 2010 Expert 3

00. Introduction (7:38)

01. SQL Primer (11:39)
Combo Box Row Source
SELECT FROM
ORDER BY
WHERE
Change Combo Sort
Change Combo Field Order
Record Source for Form

02. Callbacks (19:26)
Modify ContactT
Default Value for Dates
Today's Date
Tomorrow's Date
Tomorrow at Noon
Tomorrow at 9 AM
Modify Contact Form
Custom Date Format
ddd mm/dd/yyyy hh:nn am/pm
Callback Query
Sort by Date
Callback Form
Add button to Main Menu
Add customer name to list
Button to open Customer Form

03. Contact Subform (16:27)
What is a Subform
Master and Sub Forms
Parent and Child Forms
Copy and modify Contact Subform
SQL Statement as Record Source
Sort by Date Reverse Order
SELECT ORDER BY DESC
Subform Object Control
Click and Drag to create Subform
Subform Object Properties
Subform Form Properties
Access Autoform with Subform
Link Master Fields
Link Child Fields

04. Form Footer Totals (23:59)
Product Category Table
Product Table
ProductSubformF
ProductCategoryF
Aggregate Functions
SUM AVG MAX MIN COUNT
Modify Embedded Macro
Change Form Name in Macro
Moving to Access 2013

05. Review (6:41)

Access 2013 Expert 4

00. Intro (7:37)

01. Extended Info Tables (19:26)
Create CustomerExtendedT
ShortText and LongText Fields in 2013
Yes/No NULL Value
Password Input Mask
Family Member Table
Relative Type Table
Force One-to-One Relationship
Indexed No Duplicates

02. Extended Customer Form 1 (23:55)
Create Extended Customer Form
Option Group
Yes as -1, No as 0
Unspecified option
Change y/n to number
Create List Box from Scratch
Create Toggle Buttons
Manipulate Toggle Colors
Lighter Darker 50%
Hover Color
Pressed Color

03. Extended Customer Form 2 (26:05)
Command Button Open Ext Form
Default Value
Forms!CustomerF!CustomerID
Locked Property
Change Form Properties
Modal, Popup
Navigation Buttons
Record Selectors
Caption & Trick
Tab Controls
Image Control
Save Path Filename in Table
Set Image Control Source
Customer With Image Query
Display Image on Report
Customer Labels with Image

04. Family Member Form (14:15)
Description Indexed No Duplicates
Relative Type Helper Form
SQL Recordsource
Family Member Form
Relative Type Combo Box
Link Master Fields
Link Child Fields
List Items Edit Form

05. Toolbox Control Overview (11:20)
Hyperlink Control
Label with Hyperlink Address
Show File Extensions
Brief Discussion Of...
Web Browser Control
Page Break Control
Chart Control
Line & Rectangle Controls
Attachment Control
ActiveX Controls

06. Review (5:12)

Access 2013 Expert 5

0. Intro (06:52)

1. Contact Letter Query (9:51)
Make Notes Field Rich Text
Set fields to Rich Text
Open Report to Specific Record
Query Criteria Forms!FormName!Field
Run Query Without Form Open
Enter Parameter Value Prompt

2. Contact Letter Report (15:16)
Format of a Printed Letter
Create Blank Report
Bring in Contact Query Fields
Remove Borders
Long Date Format
Create FullName Field in Report
String Concatenation in Text Box
Can Grow, Can Shrink
Text Box and Section Grow / Shrink
Label for Return Address
SHIFT-ENTER for New Line
Paper Size
Margins
Put Logo on for Letterhead
Line and Web Address in Page Footer

3. Print Letter Button (11:45)
Command Button
Print Preview Report
Dirty Records
Refresh Data Before Opening Report
Edit Embedded Macro
Build Event
OpenReport Command
Add New Action
RefreshRecord Command
Change Order of Macro Commands
Match Size of Text Box with Letter
Page Numbering
Page N of M

4. Export or Email Letter (18:15)
Export to Excel
Export as Text File TXT
Export as PDF or XPS
Send as Email Attachment w Outlook
Export as Word Document
Export as HTML Document
Email Report Command Button
EMailDatabaseObject Command
Object Type
Object Name
Output Format
Anatomy of a Hyperlink Field
HyperlinkPart Function

5. Customizing the Ribbon (9:06)
Using ALT Keys
Minimize the Ribbon
Collapsing the Ribbon
Customize Quick Access Toolbar
Add to Quick Access Toolbar
Access Options
Customize Ribbon
Popular Commands
Commands Not in the Ribbon
New Custom Tab
New Custom Group

6. Review (5:54)

Access 2013 Expert 6

00. Intro (7:10)

01. Collection Letters 1 (11:32)
Create OrderT Table
Create OrderCollectionLetterQ

02. Collection Letters 2 (12:17)
Copy current contact letter
OrderCollectionLetterR
Change record source
Detail section properties
Force New Page After Section
Turn off Alternate Background Color
Add order info fields to letter

03. Collection Letters 3 (13:29)
LetterT Table
LetterF Form
Enter multiple letters
Text on report set by form
BeginDate and EndDate for query
Can't edit with Date() in Control Source

04. Collection Letters 4 (8:55)
Put LetterF on Main Menu
Change date range based on letter
BeginDaysAgo, EndDaysAgo in LetterT
Set BeginDate and EndDate Automatically

05. Generic Letters (19:19)
Send letters by various criteria
Letter to Active customers
AllowEdits vs. Locked Field
CustomerActiveQ Query
LetterActiveR Report
Letters by Lead Source
LeadSourceCombo
LettersByLeadSourceQ

06. Review (5:21)

Access 2013 Expert 7

00. Intro (8:08)

01. Lookup Values in Tables (17:46)
Add Group Field to CustomerT
Lookup Data Type
I will type in the values
Limit to List
Cons of Lookups in Tables
NOT Supported by SQL Server
Allow Value List Edits
Allow Multiple Values
Row Source Item List
Semicolon Delimited Items
Two column text value list
Bound Column
Column Count
Lookup from another table
SQL Statement directly in table
Multi Valued Fields
Allow Multiple Values
Cons of MultiValue Lists
Delete Relationship to GroupT

02. Many to Many Relationships 1 (13:10)
Multiple groups per customer
Many-to-Many
Cross-reference table
Junction table
Customers to Groups
Vendors to Products
GroupXCustomerT
Group Subform on Customer Form

03. Many to Many Relationships 2 (6:25)
Group Form
Show Customers in Group
Copy CustomerGroupSubF
Create GroupCustomerSubF
SHIFT-ENTER line break in label
Groups button on Main Menu

04. Letters By Group (16:36)
IncludeInMailing Field in GroupT
GroupMailingQ
LettersByGroupR
GroupSubF
RefreshRecord in Macro
Duplicate customers in mailing
Quick overview of Aggregate Queries
Group By
Min Value

05. Post Cards (12:02)
Create Report Design
Shape Outline Transparent
Landscape v Portrait
Page Setup Dialog
Number of Columns
Row Spacing
Column Spacing
Column Size
Same as Detail
Down, then Across
Across, then Down
Margins
Resizing with the Rulerbar
Button from Letter Form

06. Vendors to Products (17:16)
Build ProductF Form
Vendor Table
VendorXProductT Junction Table
UnitCost
DateUpdated
Profit Calculation

07. Review (5:10)

Access 2013 Expert 8

00. Intro (8:22)

01. Products to Vendors (24:01)
Homework Review
Button to Open Product Form
VendorF Form
VendorProductSubF
Button to Open Vendor from Product
Button to Open Product from Vendor
Give Names to VendorCombo, ProductCombo
Query to add UnitPrice to Junction
VendorXProductWithProfitQ
Add Price and Profit to Vendor SubF

02. Order Details Table (11:42)
Create OrderDetailT
Delete AmountDue from OrderT
Possibility of breaking stuff!
Create OrderDetailQ
Pull in data from ProductT

03. Calculated Query Fields (12:29)
Calculate ExtPrice
String Concatenation Reviewed
Assignment Operators
* / + - \ ^ MOD
Integer Division
Modulus
Order of Operations
PEMDAS
Comparison Operators
< > <= >= <> =
AND OR NOT XOR
-1 or 0 for True False
Boolean Values
Format Property in Query Column
Format True/False
Format Currency

04. Sales Tax (16:32)
IF THEN Statements
IIF Function
Function Review
SUM AVG MAX MIN COUNT
DATE NOW TRIM
SalesTax IIF Function
ROUND Function
Rounding Numbers
Bankers Rounding
Traditional v Bankers Rounding
Nested Functions
Nesting Functions
Fractional Penny Problem
INT Function Round Down

05. Order Detail Subform (14:44)
Create OrderDetailF Subform
Product Combo Box
Lock UnitPrice Field
Order Relationships
Customers to Orders
Do NOT Cascade Delete
Ordsrs to Details
DO Cascade Delete

06. Order Form (22:18)
Create OrderF
Customer Combo Box
Show Name if Company Null
Is Null vs IsNull()
Add OrderDetailSubF
Auto Default CustomerID
Auto Default IsTaxable
Form Footer Calculations
Sum of ExtPrice
Sum of SalesTax
Grand Total

07. Review (5:06)

Access 2013 Expert 9

00. Intro (8:39)

01. Customer Discount Rate (20:26)
Discount Rate for Orders
Discount Rate for Order Details
Different Validation Rules
Recalculate Sales Tax
Tab Cycle Property
LineTotal

02. Multiple Sales Tax Rates (17:42)
SalesTaxRate for Customers
SalesTaxRate for Orders
SalesTaxRate for Order Details
Values Propagate Down Forms
Disable Close Button
Disable Control Box
System Default Values Table

03. Non Taxable Products (12:01)
IsTaxable for Products
Show IsTaxable in Product Combo
SQL ALIAS
IF Product Tax Exempt
Another IIF Example

04. Printable Invoice 1 (20:27)
Order Detail Subreport
Report Header / Footer
Page Header / Footer
Order Info with Customer Data
OrderQ
Customer Bill To Info
Insert Subreport

05. Printable Invoice 2 (19:58)
Company Info in Header
Missing Company Name Gap
Can Shrink Problem
Adjacent Control Problem
Overlapping Controls
TRIM Function
Open Report to Specific Record
Query Criteria
Company Return Address Info

06. Review (6:08)

Access 2013 Expert 10

00. Intro (7:02)

01. Quote or Invoice (28:09)
Add My Company Info to Defaults
Company Logo
Size to Tallest
Paid or Unpaid Text on Invoice
Refresh Dirty Record
IsQuotation Field on Order Form
Quotation or Invoice Text
Nested IIF Functions
Hide Paid Text if Quotation

02. DLOOKUP Function (12:10)
Move System Defaults to Own Form
DLOOKUP Field, Table
Get SalesTaxRate from DefaultT
Edit Invoice to use DLOOKUP

03. More with DLOOKUP (25:35)
Sales Rep for Customer
SalesRepID
ServiceTechID
Related Fields with Different Names
Sales Rep Combo Box
Service Tech Combo Box
SalesRepID on Order Form
Display Sales Rep's Phone Number
Criteria Format
"EmployeeID=" & SalesRepCombo
@@@-@@@-@@@ Phone Number Format
Strings Inside Criteria
Double Double Quotes ""
Dates in Criteria need # signs
AND Condition in Criteria
Add Rep Name and Phone to Invoice

04. Calculated Table Fields (8:31)
Expressions in Tables vs Queries
Problems with Calculated Table Fields
When to use them
Example: TimeToShip in Days

05. Review (5:35)

Access 2013 Expert 11

00. Intro (8:38)

01. Simple Aggregate Queries (13:13)
Aggregate Query
Totals Query
Summary Query
Grouping Query
What they are
Popular Aggregate Functions
SUM, AVG, COUNT
FIRST, LAST
MAX, MIN
Statistical Analysis
STDEV, STDEVP
VAR, VARP
Covered in future class
SUM in Form Footer Total
SUM in Table Footer (last row)
Totals Button
Simple SUM of all credit limits
Group By State
Default sort on Group By field
Sort by Credit Limit descending
Add criteria Is Not Null
Notice Field Name is Changed
SumOfCreditLimit
AvgOfCreditLimit
One query with SUM, AVG, MAX, MIN
ALIAS field names
Count of customers from each state
Only NON-NULL values are counted
Count ID field to include NULLs
Simple Query Wizard
Aggregate Options in Wizard
Summary Information

02. Complex Query Criteria (7:44)
Credit Limit by State After 1999
Brief overview of date functions
MONTH, DAY, YEAR Functions
HOUR, MINUTE, SECOND Function
Break into Two Queries
WHERE Field in Total Row

03. Sales Totals by Month (10:50)
Format to show "yyyy mm"
Format Property in Query Field
Format() Function
Week of Year ww

04. Lowest Product Cost (12:46)
MIN Cost for each Product
DLOOKUP Vendor ID at Lowest Cost
DLOOKUP Vendor Name, Unit Price
Calculate Most Profitable Products

05. Last Customer Contact (6:36)
MAX of Contact Date
DLOOKUP Contact Notes
Dealing with NULL Values
IIF Function Review

06. Employee Work Log (26:02)
Create Work Log Table
Time In, Time Out
Timeclock
Difference Between Two Times
Calculate Hours Worked
Caution with Dates and BETWEEN
BETWEEN #1/1# and #1/2#
Does NOT Include #1/2 5pm#
>=Start AND <End
CDATE() Function Brief Mention
Work Log Lookup Form
Employee Combo Box
Command Button Run Query
Forms!WorkLogLookupF!StartDate
Homework for Next Class

07. Miscellaneous (16:13)
Expression Option
DSUM Sales Last 30 Days
Refresh Form Data Button
Can't Refresh Unbound Form

08. Review (5:52)

Access 2013 Expert 12

00. Intro (8:01)

01. Order List Form (22:57)
Create Order List Aggregate Query
IIF to display PAID / UNPAID
IIF to display QUOTE / INVOICE
OrderListQ
AllowEdits
AllowDeletions
AllowAdditions
Sum of Order Total in Footer
Sort by Order Date Descending
Refresh Form Data Button Doesn't Work
Refresh vs Requery
Refresh vs Refresh All Buttons
Button Build Event
RunMenuCommand Refresh
Requery Macro Command

02. Work Log Entry Form (35:28)
Create a Button Without the Wizard
Copy Existing Button
Edit Embedded Macro Form Name
Copy Button for Query
Resize to Tallest
Create Work Log Form
Employee Combo Box
Tab Order
Bound Combo in Form Footer
Unbound Combo in Form Footer
Combo Default Value
DefaultEmployeeCombo
Default Date Textbox
TodaysDate
=[TodaysDate]+#9 AM#
Populate Defaults with Unbound Fields
Work Log for Current Employee
Add to Main Menu
Track Employee Activities
Activity Table
Add Activity Combo to Work Log

03. Fix Collection Letters (5:57)
Parameter Prompt from Missing Field
Update Order Collecton Letter Query
Aggregate Total for Amount Due

04. Report Grouping 1 (15:08)
Customers Grouped by State
Group & Sort
Add a Group
With a Header Section
With a Footer Section
With Totals
Group Footer Totals
Horizontal line
Line thickness
Showing a Value if Header IsNull
IIF IsNull
Display [None]
Circular Reference

05. Report Grouping 2 (17:56)
Add Credit Limit
SUM of Credit Limit
Grand Total
No Aggregate Functions in Page Footer
Page of Pages
Page Footer Below Report Footer
Section Visible Property
Hide Sections
Do not keep group together on one page
Keep whole group together on one page
Keep header and first record together
With title label - BUG
Force New Page After Section
Multiple Grouping Levels
No Country display United States

06. Employee Work Log Report (16:56)
Work Log Report
Group by Employee
Subgroup by Week of the Year
Access has no Week or WeekNum Function
DatePart Function
Calculate First Day of the Week
Week Starting Date
SUM of Hours Worked by Week
Total Hours for Employee

07. Number & Currency Grouping (17:55)
Show Sales Grouped by Rep
Subgroup by Month
Change to Numeric Grouping
Orders Grouped Every $500
Range of Values for Header
$0 to $499.99
Integer Division \
Format(X,"Currency")

08. Miscellaneous (7:23)
Group by Expression
Use a Query Expression Instead
Simple Report Wizard
Homework!

09. Review (5:21)

Access 2013 Expert 13

00. Intro (7:20)

01. Open Report WHERE Condition (28:04)
Buttons on Main Menu for Reports
Replace Invoice with Grouped Report
Same field name on two tables
OrderT.F1 and OrderDetailT.F1
OrderID Header Section
Open Report to Specific Multiple Records
Print Multiple Invoices
Macro WHERE Conditions
Print All Invoices for a Customer
Print All Invoices Between Dates

02. Types of Action Queries (5:49)
What are Action Queries
Update Query
Append Query
Delete Query
Make-Table Query
Backup your data!
How to backup your database file
Online backup recommendations
Backing up a table

03. Update Queries, Part 1 (14:48)
Update Queries allow Complex Criteria
Change Select to Update Query
Update To Property
RUN vs Datasheet View Buttons
Preview Records to be Updated
Running the Update Query
Multiple Criteria
Change "XYZ Corp" in "PA"
Save Query
Nested IIF in Update Query
Assign Reps Based on Territory

04. Update Queries, Part 2 (10:26)
Capitalize States
UCASE() Function
Can't Delete a Table with Relationships
Manually Delete Relationships
Delete and Rename Backup Table
Restore Relationships
LCASE() Function
STRCONV() Function for Proper Case
Exceptions: Don't change McDonald

05. Update Queries, Part 3 (9:26)
Add 5% to all Product Prices
UnitPrice = UnitPrice * 1.05
Update all Paid Orders as Shipped

06. Review (6:03)

Access 2013 Expert 14

00. Intro (9:13)

01. Scrubbing Data (15:49)
Cleaning Data with Update Query
Inconsistent Data Formats
Fax Numbers with Different Formats
Clean them to look uniform
Backup Your Data!
REPLACE() Function
Replace one string with another
Watch Field Names Carefully
Make sure you use [Brackets]
Null values can cause problems
LEN() Function
Length of String
Show FaxNumbers 7 digits long

02. Change Email Domains (12:39)
Hyperlink fields are not simple text
CSTR() Function
Convert to String
HyperlinkPart() Function
Change Domain Name
amicron.com change to 599cd.com
NZ() Function
Null to Zero

03. Separate First and Last Names (19:29)
Create Person Table
Put First and Last Names Together
INSTR() Function
Determine the position of the SPACE char
LEFT() Function
RIGHT() Function
MID() Function (Discussion)
Check for Prefix, Suffix, Middle Initial

04. Fix Non-Relational Table (9:51)
Employees and Departments
Turn Department Names into IDs
Query Join on Text Values
Update to IDs from other table

05. Daily Student Attendance (18:34)
Create Student Table
Attendance Table
Make Append Query
Append To Table Name
Current Database
Another Database (Discussion)
Only Students from One Class
Criteria in Append Query

06. Review (8:14)

Access 2013 Expert 15

00. Introduction (8:27)

01. Student Attendance 1 (20:12)
Types of Different Classes
ClassTypeT
Individual Class Sessions
ClassT
ClassQ with Type Name
Track Customers in Each Class
ClassXCustomerT
Class Form with Enrollment Subform
ClassF
ClassXCustomerSubF
Add Multiple Students to Class
Each Company Sends Multiple Students

02. Student Attendance 2 (31:12)
Create Attendance Table
AttendanceT
Master Attendance Form
Student Subform
Modal Popup
Default Values from Other Forms
AttendanceQ With Supporting Data
Left Joins
Create Attendance Subform
Data Entry Property
Attendance Append Query
Add Static Date Value to Append Query
CDate Function

03. Student Attendance 3 (34:59)
Create Button to Run Append Query
SetWarnings OFF
Turn on Potentially Unsafe Actions
Show All Actions
Requery
Short Date Format for Date Picker
Show Attendance for Any Date
Attendance Report By Class By Date
Sorting & Grouping
Attendance Report by Student then Date

04. Contact Archive (15:37)
Backup Contact Table
Create Contact Archive Table
ContactArchiveAppendQ
Remove Indexing Primary Key from Archive
Change AutoNumbers to Numbers

05. Review (6:31)

Access 2013 Expert 16

00. Intro (8:25)

01. Macro to Run Multiple Queries (9:21)
Command Button Wizard
Open Query
Delete Queries Not Listed
Archive Contacts Button
Macro Builder
OpenQuery Command
Add New Action
Show All Actions
SetWarnings On/Off
MessageBox Command (MsgBox)

02. Delete Query Multiple Tables (8:56)
Referential Integrity
Delete Queries and Cascade Deletes
Delete Contacts from Customers in CA
Two Queries Needed
Customer By State Query

03. Make Table Queries (16:05)
Create a Customer Mailing List Table
Make Table Query in Same Database
Create a new blank database ACCDB file
Make Table Query into a Different Database
Backup Customer Table
Multi-Valued Fields Not Allowed
Long Text with Append Only Property On
Automated Backup of Contact Table
Include in Command Button Macro
OpenQuery Command

04. Multiple Parent Mailing List (18:25)
Create Student Table with 2 Parents
Include In Mailings Field
Make Table Parent 1
Append Parent 2
Show Another Example Relational
Use a Query with a Join

05. UNION Query (10:30)
Merge Contacts with Archive
Don't use the Union button
UNION SQL statement
Homework Assignment

06. Review (7:25)

Access 2013 Expert 17

00. Intro (9:05)

01. Archive Old Unpaid Orders 1 (13:30)
Add ToBeArchived y/n Field
Create ToBeArchivedUpdateQ Query
Button to Archive Old Orders on Main Menu
Database Level Macro Primer
Macro to Open Main Menu
Putting Macros on Quick Access Toolbar

02. Archive Old Unpaid Orders 2 (21:22)
Make OrderListF Show All Order
Add ToBeArchived to Order List Form
TBA Tool Tip
To Be Archived checkbox on Order Form
Macro WHERE Condition
ToBeArchived=TRUE
Create OrderArchiveT
Create OrderDetailArchiveT
Set Autonumbers to Long Int
Append Orders
Create AppendOrderDetailArchiveQ
Create AppendOrderArchiveQ
Duplicate output destination
Create OrderToBeArchivedQ to avoid error

03. Archive Old Unpaid Orders 3 (21:27)
Backup tables with MakeTable query
MakeCustomerBackupQ
MakeOrderBackupQ
MakeOrderDetailBackupQ
Calculated columns are not allowed in SELECT INTO statements
Another reason I don't like calculated table fields
Remove DaysToShip from OrderT
Update customer record "Order Writeoff"
UpdateCustomerOrderWriteoffQ
Append "Order Writeoff" to ContactT
Put writeoff notice in contact table
Add OrderID to writeoff notice
AppendOrderWriteoffQ
Update the order description with "WRITEOFF!"
UpdateOrderWriteoffQ

04. Archive Old Unpaid Orders 4 (13:02)
Delete orders and details from main tables
DeleteArchivedOrderDetailQ
DeleteArchivedOrderQ
Copy and modify append queries
Delete FROM and WHERE
Make button on TBA form to perform archive
Copy button with macro in it
CloseWindow macro command
Macro commmand to Close Form
UNION query to show orders and archive together
SQL View
Fields must match exact with UNION and * all fields

05. Crosstab Queries (13:16)
What is a Crosstab Query?
Similar to an Excel PivotTable
Show example of a PivotTable
Add some new orders
Edit OrderListQ Add State Field
Create Crosstab Query
Crosstab Query Wizard
Built Crosstab Query Manually
Set up Helper Query
Orders by Month by State
OrderByMonthByStateCrosstabQ

06. Review (5:38)

Access 2013 Expert 18

00. Intro (6:26)

01. Crosstab Query Parameters (16:45)
Sales by Sales Rep by Month Crosstab
Fixed Column Headings
Manual Row Headers
Multiple Row Headings OK
Multiple Column Headings NOT OK
Criteria in Crosstab Queries
Date Criteria in the form yyyy-mm
Declaring Explicit Query Parameters
Declare Parameter Even If Form Field

02. Sales Rep by Quarter Crosstab (06:11)
Query Wizard Date Groupings
Total Row Headings
Count Row Headings
Crosstabs Used With Other Queries

03. Partition Function (7:29)
Tiers of Products
PARTITION() Parameters
Group Products into Price Brackets
Count of Products in Each Tier
Product Category Row Header
Product Tier Column Header
Value is Count of Products in Tier

04. Find Duplicates Query (21:19)
Create Find Duplicates with Wizard
IN() Function Review
SQL HAVING Clause, Brief Discussion Of
Manually Create Find Duplicates Query
Use an Aggregate Query Count > 1
Find Duplicates Multiple Fields
Unique Values
Unique Records
Field List Properties
Query Properties
Field Properties
Output All Fields
Recordset Type Dynaset vs. Snapshot
Prevent Editing of Records in Query
Order By Property
Filter Query Property
Filter On Load
Order By On Load
What is an ODBC Database
ODBC Timeout
Max Records
Record Locks, Brief Introduction
Record Locking
All Records
Edited Records
Orientation Right-to-Left, Left-to-Right
Subdatasheet Height
Subdatasheet Expanded

05. Find Unmatched Query (11:36)
Use Wizard
Create Unmatched Query Manually
Left Outer Join Important
Is Null
Cartesian Product
Cross Product
Softball Schedule
Each Team Plays Each Other Team Once

06. Review (5:23)

Access 2013 Expert 19

00. Intro (6:15)

01. Conditional Formatting (28:13)
Format large orders green
Conditional Formatting Rules Manager
New Formatting Rule
Check values in the current record or use an expression
Field Value Is
Greater than, Less than, etc.
Highlight orders over $500 in green
Add Views to Quick Access Toolbar
Conflicting Rules
Order of Applied Rules
Using Functions like Date() in Conditions
Formatting Date Values
Formatting Text Values Exact
Find Text Inside of Field
Expression Is
InStr Function
Format Customers with "Inc" in name
Apply to Multiple Fields at Once
Format Entire Row
Format if Field Has Focus
Disable Changes if Order Marked Paid
Conditional Format Subform Value
Disable Editing Products on Order if Paid
Compare to Other Records
Data Bars
Lowest, Highest value
Numbers, Percentages
Max 50 Conditions

02. Exporting to Excel (16:37)
Reasons to Export Data
Export Table to Excel
Different Versions of Excel Data
Export data with formatting and layout
Open the destination file after the export
Export selected records
Copy and Paste from Access to Excel
Copy Without Column Headers (Field Names)
Add an Export Checkbox to Table
Export Specific Records Query
Hide the Export Field
Save Export Steps
Create an Outlook Task with Reminder
Saved Exports
Manage Data Tasks
Run Saved Export Routine

03. Exporting to Word (15:05)
Export Mailing Labels
Export RTF File to Word
Mail Merge Access Data to Word Document
Word Merge
Microsoft Word Mail Merge Wizard
Link your data to an existing Microsoft Word document
Create a new document and then link the data to it
Mail Merge Wizard
Edit Recipient List in Word
Insert Address Block
Insert Merge Field
Preview Your Letters
Word Doc Automatically Updates from Table
Copy and Paste Table Data to Word

04. Exporting Text Files (10:14)
Export Text File
Export Windows Text
MS-DOS Text
Notepad Application
Basic Access Text Export is Garbage
Export Without Formatting
Export Text Wizard
Fixed Width vs. Delimited Text
Changing Width of Field
Field Delimiter
Text Qualifier
Date Order
Date Delimiter
Time Delimiter
Four Digit Years
Leading Zeros in Dates
Decimal Symbol
Save Export Specification
Include Field Names in First Row

05. Export Misc (18:23)
Export to XML
Export to Another Access Database
Database must be closed
Access needs exclusive read/write
Queries Export as Queries NOT Tables
Copy Multiple Tables
Export to Sharepoint, Brief Discussion
Export to ODBC, Brief Discussion
Export to SQL Server, Brief Discussion
Export to HTML Document
Formatted v. Unformatted HTML
Coming Up

06. Review (5:32)

Access 2013 Expert 20

00. Intro (5:43)

01. Send Bulk Email via Word (29:13)
Query for Customers to Email
Active Customers with Email Addresses
CustomerForEmailExportQ
External Data > Export > Word Merge
Create a New Document
Mail Merge Wizard
Use the Current Document
Use an Existing List
Edit Recipient List
Format Document
Insert Pictures
Preview your Email Messages
Complete the Merge
Merge to Email
To, Subject, Mail Format HTML
Check Names Error Message
Outlook Cannot Handle Hyperlink Fields
Separate Email Address from Hyperlink
HyperlinkPart Function
Word: Undefined Function HyperlinkPart
Make Table Query for Email Table
CustomerForEmailExportT
Find Data Source
Step by Step Mail Merge Wizard
Finish & Merge
Email Active Customers Button
Macro: Show All Actions
SetWarnings On/Off
OpenTable Command
RunMenuCommand WordMailMerge
Switch to Email Messages

02. Importing & Linking from Excel (21:27)
Copy and Paste Cells to New Table
Does first row contain column headings
Importing Proper Data Types
External Data > Import & Link > Excel
Import Excel Sheet Data
Import Spreadsheet Wizard
Field Name, Data Type, Indexed, Skip
Let Access add primary key
Save Import Steps
F5 to Refresh Navigation Pane
Saved Imports > Run
Import Errors
Append a copy of the records to the table
Linking to an Excel Spreadsheet
Link to the data by creating linked table

03. Importing Text & HTML (16:18)
Discussion of Different Import Features
Linked Table Manager, Brief Discussion
ODBC Database, Brief Discussion
Sharepoint List, Brief Discussion
Data Services, Brief Discussion
Importing Text Files
Advanced Import Specification
Importing HTML Documents
Importing HTML from a Web Page Table
Saving a Web Page as HTML

04. Exporting & Importing XML (14:43)
What is XML
Why is XML Better than Text or HTML
Exporting a Table in XML Data Only
Exporting Data with Schema XSD File
Exporting XML with Embedded Schema Data

05. Linking to Outlook Folders (11:21)
Link to Outlook Inbox
Read and Display Email in Access
Editing Email in Access
Linking to Outlook Contacts
Displaying Contacts in Access
Editing Contact Info in Access

06. Review (10:38)

Access 2013 Expert 21

00. Intro (7:14)

01. Import & Link Access Objects (14:45)
Import by Click-and-Drag
Import by External Data > Access
Browse to Database Folder
Import Objects Dialog
Import Options
Import Table Relationships
Import Menus and Toolbars
Import/Export Specs
Import Nav Pane Groups
Import All Images and Themes
Table Definition AND Data
Table Definition Only
Import Queries as Tables
Importing vs. Linking
Link to Customer Table
Custom Forms for Front End

02. Multi-User Database Theory (14:53)
Sharing a Database
Why Split Your Database
Front End vs. Back End Databases
Pros and Cons of Split Databases

03. Splitting Your Database 1 (20:38)
Compact & Repair First
Backup Your Data
Database Tools > Move Data > Access
Database Splitter Wizard
The database engine could not lock table
Already in use by another person
Notice Linked Tables
Set up Shared Folder
Linked Table Manager
Rename or Move Backend Database
Refresh Location of Backend Links
Setting up a Shared Folder
Folder Properties > Sharing
Assign Shared Folder Name
Setup Folder Permissions
Select Users or Group
UNC Path
Mapping a Drive Letter

04. Splitting Your Database 2 (21:15)
Storing Images on Server Share
Creating a 2nd Back End Database
Move a Secure Table to Different File
Creating a 2nd Front End Database
Custom Databases based on User Role
Distribute Encrypted Front End
Users Cannot Make Design Changes
File > Save As > Make ACCDE
Trusted Location - Shared Folder

05. Record Locking (28:42)
How Default Record Locking Works
Write Conflict Error Message
Save Record, Drop Changes
Copy to Clipboard
Form Properties > Record Locks
No Locks, All Records, Edited Record
What is an LACCDB file?
Database Client Settings
Page Level vs. Record Level Locking
Default Open Mode: Shared / Exclusive
File Open for Exclusive One Time Use
Default Record Locking
Open databases by using record-level locking
Changes take effect when first user opens db
Delete LACCDB file if record still locked

06. Review (6:15)

Access 2013 Expert 22

00. Intro (9:09)

01. Fixing Non-Relational Data (24:25)
Create Bad Spreadsheet
Import Sheet into Access
Aggregate Query for Unique Customer List
Customer Query with Most Recent Info
DLOOKUP Most Recent Address & Phone
Remove Customer Data from OrderT
Add CustomerID Foreign Key
Query to Change CustomerID
Delete Automatic CustomerID Join
Create New Join on Customer Name
Make Table Query to Create CustomerT

02. Update Vendor Pricing (41:41)
Price Sheet Changes from Vendor
Create Spreadsheet with Product Costs
Import Data into Access
Link Our Data with Vendor Data
Joined Query is Too Complex for Update
Make a Simpler Query
Discontinued Products have Null Price
Create Macro to Automate Import of Data
Make Button to Run Import
ImportExportSpreadsheet
RunSavedImportExport

03. Fix the Order Entry System (42:21)
Current DB alters orders on UnitPrice change
Add ProductName, UnitPrice to OrderDetailT
Fix the OrderDetailQ
Unbind Product Combo Box, Move to Footer
Add ProductID, ProductName
Lock ProductID
Unlock UnitPrice
Create Embedded Button Macro
Build Event > Macro Builder
IF Macro Command
MessageBox Macro Command
StopMacro Macro Command
GoToControl Macro Command
GoToRecord Macro Command
Show All Commands Button
Potentially Unsafe Macro Commands
SetValue Macro Command
Set SalesTaxRate with IF Statement

04. Review (5:28)

Access 2013 Expert 23

00. Introduction (7:58)

01. Remote Copy For Data Entry (36:44)
Copy PC Resale Main Database
Customize for Remote Use
Lock Customer Form
Don't Allow Edits, Deletions, Additions
Only Allow Additions on Contact Form
IsRemote Yes/No Field
Link to Main Database Tables
Not Connected to Network Error Message
Append New Records to Server
Delete Data in Remote Database
Copy All Records from Server to Remote
Macro to Perform Synchronization

02. Import Paypal Transactions (25:37)
What is Paypal
Download Paypal History File
Import Paypal Data Into Access
Set up my Transaction Table
Prevent Duplicate Records

03. Composite Keys (15:44)
Prevent Duplicate Values Multiple Fields
Create Store Locations Table
Do Not Want Two Stores in Same City
Indexed, No Duplicates
Indexes Dialog Box
Create the Composite Index
Also Called a Compound Index
Make Sure Store Doesn't Report Sales Twice
Create StoreSalesT
Prevent Duplicate Store Sales Figures
Allowing Duplicate Values
Find Duplicates Query Wizard
SQL Statement Explained Briefly

04. Transfer Multiple Tables XML (7:23)
Send Multiple Tables with Related Data
Exporting Customers with Orders
Export XML Menu Select Tables
Import Tables Into a New Database

05. Review (7:29)

Access 2013 Expert 24

00. Intro (9:47)

01. Import RSS Feed Data (14:21)
Pull News from Web Site
RSS Feeds are XML Files
Really Simple Syndication
Get the 599CD News Feed
External Data > Import XML
ImportErrors Table
Channel and Item Tables
GUID: Globally Unique Identifier
Field truncation
Can't Save Import Steps if Error
Import Latest News from MSNBC.com
Import Tech News from CNN.com

02. Import Real-Time Weather (14:02)
How to find an RSS Feed not advertised
Weather.com RSS Feeds
Weather Underground API
Register for an API key
Import Weather XML Feed

03. Display Real-Time Weather (20:56)
Convert RFC822 to Access DateTime Value
Query Sorted by Observation Date
Form to Display Current Conditions
Button to Run Macro for Import
RunSavedImportExport
Requery Form Data
WebBrowser Control
Display Images from a Web Site
Event Timer
Timer Interval
On Timer Event

04. Online SQL Server Database (28:39)
Share Access Data via Web Server
Use Access Front End, SQL Tables
Set up SQL Server DB on GoDaddy
Database Friendly Name
Username and Password
Export to ODBC Database
File Data Source
Machine Data Source
User Data Source
Create New Data Source > SQL Server
Set up a File DSN
Complete Export of CustomerT
Link to the CustomerT on SQL Server

05. Review (5:47)

Access 2013 Expert 25

00. Intro (5:44)

01. String Functions 1 (26:27)
Function Categories
Left()
Right()
Mid()
Separate Phone Number Parts
Area Code, Prefix, Suffix
InStr()
InStrRev()
Len()
Separate First and Last Names
Get Last Name even if Middle Name
UCase()
LCase()
StrConv()
Convert to Upper Case
Convert to Lower Case
Convert to Proper Case
Trim()
LTrim()
RTrim()
Remove Extra Spaces from String
Replace()
Replace One String with Another
String Concatenation &

02. String Functions 2 (14:51)
Str()
CStr()
Convert Number to String
StrComp()
String Comparison
Greater, Less, Equal, NULL
ASC()
CHR()
ASCII Code Table
Non-Standard Characters
Space()
Monospace Fonts
Kerning, Kerned Fonts
String()
Repeat a String X Times

03. Logical Functions 1 (14:23)
IsDate()
IsNull()
IsNumeric()
IsError()
NZ()
Replace NULL Value
VarType()
TypeName()
Determining Data Types
Data Type Names
AND, OR, NOT, XOR
Logical Operators

04. Logical Functions 2(26:33)
IIF()
Nested IIF()
Student Grades Pass/Fail
Student Letter Grades
Switch()
Choose()
Shipping Methods
Calculating Employee Overtime Pay
Separating First, Middle, Last Names

05. Review (5:44)

Access 2013 Expert 26

00. Intro (06:20)

01. Math Functions 1 (27:09)
Abs - Absolute Value
Sgn - Sign
Postive, Negative, Zero
Round
Banker's Rounding
Round to Evens
Don't Want Banker's Rounding
Round to Nearest Multiple
Int - Round to Integer
Fix - Negative Int Rnd Up
Cint - Convert to Integer
Val - String to Number
Val to Convert HEX to DEC
Remove Thousands Separator
Power of Tens: 2E14

02. Math Functions 2 (12:16)
Sqr - Square Root
e - Euler's Number
Mathematical Constant
Exp - Exponential
^ Number to Power
Log - Natural Logarithm
Calculate Log of Any Base

03. Math Functions 3 (11:40)
Calculating PI
Inverse Functions Missing
Degrees to Radians
Radians to Degrees
Sin, Cos, Tan, Atn
Calculate Building Heights

04. Math Functions 4 (29:21)
Mod - Modulus
\ - Integer Division
Is Number Divisible By X
Rnd - Random Numbers
Dice Roller Form
Reroll Button
Requery Macro Command
Display Random Records
Open a Random Customer
Semi-Random Customer Codes

05. Type Conversion (9:42)
CBool - Boolean
CByte - Byte
CCur - Currency
CDate - Date
CDbl - Double
CDec - Decimal
CInt - Integer
CLng - Long Integer
CSng - Single
CStr - String
CVar - Variant

06. Review (5:11)

Access 2000-2003
Access 101

1. Introduction
Welcome to Access 101
Important concepts

2. Terminology
What is a database
Database components (tables, queries, etc.)
Describe each component and it's function

3. Planning Your Database
Planning your database on paper
Plan for needed tables
Plan for needed fields in each table

4. Getting Started
Starting Access
Parts of the interface
Creating a blank new database

5. Customer Table, Part 1
Creating a customer table using design view
Creating the FirstName field

6. Customer Table, Part 2
Brief description of basic data types (text, memo, etc.)

7. Customer Table, Part 3
Adding additional fields to your table (phone, address. etc.)
Brief description of basic field sizes (long int, decimal)

8. Customer Table, Part 4
Creating a CustomerID Autonumber
Moving fields in order
Saving your table
Setting the primary key

9. Entering Data
Adding data to your table
Deleting records

10. Customer Query
Building a query in design view
Adding fields to your query
Sorting your query (LastName, FirstName)
Moving fields in your query
Using criteria (e.g. show all customers from NY)

11. Customer Form
Building a form using design view
Selecting a data source
Adding fields to your form using the field list
Lining up your controls
Switching to Form View and back to Design View
Using navigation buttons to navigate records
Moving controls with the Hand
Selecting a block of controls
Deleting controls
Trick to add all controls from the field list to the form
Saving your form

12. Form Formatting
Changing colors of your form and objects
Deleting a label, keeping the text box
Editing your label text
Resizing a text box
Moving a block of controls together
Moving a label independent of it's text box
Resizing your form
Object borders and special effects
Deleting a record from the form

13. Mailing Labels
Using the mailing label wizard
Desigining a prototype label
Report Print Preview
Zooming in and out

Access 102

1. Welcome
Objectives
Pre-requisites
Versions used
How to learn

2. Review
How to find sample database files
Review topics from Access 101

3. Field Properties
Text field sizes
Number field sizes
Format property
Date formats
Text formats
Number formats
Input masks
Required property
Default value
Validation rule
Validation text
Indexing

4. Search Sort Filter
Find & Replace
Sorting
Filtering data
Both tables and forms

5. Queries
Multiple OR criteria (NY or CA)
AND from different fields (NY and Active)
AND across, OR down
Saving a query with a different name
OR from different fields (NY or 200 credit)
Between keyword
Date criteria (>#1/1/1998#)
Wildcards
LIKE keyword
Parameter Query

6. Forms
Creating an Employee table
Using OLE Objects
Creating the employee form
Adding a picture field to your form
Resizing your form
Intro to object properties
picture size mode: stretch, zoom, clip
The toolbox
Combo box with static data (M/F)
Using the combo box wizard.
Change to: Combo box into a List Box
Selecting and copying a record
Tab Order
Form fields: align left
Zoom with shift-F2
Command button: Close Form

7. Reports
Query for people not missing address data
Is Not Null: AND condition
People who are missing address data
Is Null: OR condition
New Mailing Labels
Building a report with design view
Building a report based on a query
Page header & footer

8. Compact & Repair
Why compact your database?
How to compact & repair

9. Review
Review topics
Skills check
What's Next?

Access 103

l. Introduction
Objectives
Pre-requisites
Versions used
How to learn
Included files

2. Main Menu, Part 1
Creating a form with no data
Unbound form instead of a Switchboard
Command button to open a form and show all records
Form properties
Form caption
Record selectors
Navigation buttons
Scroll bars

3. Main Menu, Part 2
Form background color
Label control
Label properties (font, size, bold, effects, etc.)
Resizing a label
Putting an image on a command button
Button properties
Send to back / bring to front
Format painter
Setting a database startup form
Application title
Hiding the database window
Unhiding your database window
Creating a shortcut to your database on your desktop

4. Customer List Form
Query: sorted list of customers
Building a form based on a query
Lining fields up horizontally
Continuous forms v. single form
Optimizing form space
Form header & footer
Using the rulerbars to select across/down
Cleaning up your labels
Command button to open a form and show specific records
Opening the selected customer record
Filtered results in your form
Removing a filter
Autoform: Tabular
Autoform: Columnar

5. Tracking Lead Sources
Adding a lead source field to the customer table
Combo box with static list of lead sources
Making at table to store a dynamic list of lead sources
Creating a combo box based on a table
Adding new valves to your table
Updating our Main Menu with the new buttons
Using the Form Wizard to create a quick form
Create a form for lead sources using the wizard
Renaming your form
Create a button to close the database

6. Review
Review topics
Skills check
What's next?

Access 104

l. Introduction
Welcome
Objectives
Pre-Requisites
Versions
Sample Database Files

2. Contact Table
What is a "Contact"
Using the customer notes field for contacts
Limitations
Creating the Contact
Duplicate data problem
Setting the default date with the Now() function

3. Contact Query
Listing contacts by date
Show only a specific date between two dates
Between keyword
Let the user enter the dates with a parameter
Creating Full Name field by merging two fields
String concatenation
Formatting a query field
Format property

4. Contact Report
Using the page header
Selecting objects using the ruler bar
Notice an & disappears in label
&& in Label
Adding a line object
Keeping your line straight
Text boxes with large amounts of data are truncated
Can Grow / Can Shrink textbox properties
Can Grow / Can Shrink section properties
Sorting and Grouping
Sorting in the report
Group Header & Footer
Keeping a group together on a page
Report header / footer
Adding page numbers to your reports
Showing total number of pages

5. Miscellaneous
Put contact report on Main Menu
Control Tip Text

6. Review
Review topics
Skills check
What's next?

Access 201

1. Introduction

2. Relational Database (RDB) Concepts
Why are RDBs important
One-to-one relationships
One-to-many relationships
Many-to-one relationships
Many-to-many relationships
Normalization

3. Relational Contacts Table
Making our contact management relational
Inserting a CustomerID in ContactT
Removing duplicated fields
What is a Foreign Key?

4. Contact Query
Manually creating query relationships
Deleting relationships
String concatenation
Dealing with duplicated fields
Correcting our contact report
Manual formatting of the phone field

5. Contact Form
Constructing a contact form
Using a combo box to pick the customer
Manually editing combo box properties

6. Miscellaneous
Add contact form button to main menu
Global relationships between tables
Creating and deleting links between fields

7. Review

Access 202

0. Introduction

1. Contact Button
Command button to open contacts for current customer
Find specific data to display
Viewing filtered results

2. Setting Values Across Two Forms
Using Forms!FormName!Field notation
Get CustomerID for Combo Box on ContactF
Setting the Tab Stop property
Skipping fields in the Tab Order

3. Sorted Customer Combo
Creating a sorted query with customer info
Manually editing the Row Source property
Modifying the Column Count property
Column Widths property
Column Heads property
SQL Primer
Modifying the row source with an SQL statement
List Rows and List Width properties

Lesson 4. Callbacks
Adding callback and date to table/form
Creating a callback query
Creating a callback form
Buttons on the form to open customer/contact

Lesson 5. Contacts Subform
Learning about subforms
Creating the contact subform
Using the subform control and wizard
Format > Send to Back
Hiding text boxes with the Visible property

Lesson 6. Miscellaneous
Editing our Main Menu
Replacing button images with text
Coloring text on buttons
Phone AutoDialer

Lesson 7. Review

Access 203

0. Introduction

1. Creating the Extended Customer Table
Building the table
Creating fields
Hyperlink field for Web Site

2. Using Option Groups
Option Groups
Option Buttons
List Box
Creating a Customer Type Table

3. Toggle Buttons
Toggle Buttons
Making a Check Box > Change To Toggle Button
Creating a Toggle Button manually
Changing Toggle Button Properties
Changing Option Group Colors
&Caption trick

4. ActiveX Calendar
Combo Box for Sales Rep
Combo Box for Service Tech
Picture OLE Field
Birthday ActiveX Control

5. Tab Control
Linking CustomerF to CustExtF
Tab Control

6. Object Controls
Bound Object
Pasting v. Linking Bound Objects
Unbound Object
Image Control
Boxes & Lines
Page Breaks
Force a 1-to-1 Relationship

Access 204

0. Introduction 

1. Contact Letter Query
Build a query that shows only the current contact
Getting the ContactID from the form into our query

2. Contact Letter Report
Designing our Contact Letter Report
Placing the Print As Letter button
Dirty records - data needs to be saved

3. Macro Basics - skipped in AC2013X5 - saving for Advanced -
Constructing basic macros
Beep macro action
OpenForm macro action
OpenTable macro action
MsgBox macro action
On Dbl Click event
OpenForm WHERE condition

4. Refresh Macro - skipped in AC2013X5 - saving for Advanced -
RunCommand, Refresh macro action
OpenReport macro action
Creating a new button using the macro

5. Formatting the Letter
Creating new name and address fields
String concatenation in a new text box
Formatting as a long date
Format > Vertical Spacing > Increase
Report width, page width, margins
Placing a logo in the page header
Making a multi-line label

Access 205

1. Past Due Customers
Add fields to CustomerT: AmountDue, AmountDueDate
Create a past due query
Wilcard Field (*) in queries

2. Letter Table & Report
Copy letter to work with new query for collection letters
Create a Letter table to hold your letter text
Create a Letter form
Bringing data from an unbound form field into a report

3. More Letter Report
Force new page after section in reports
Create a greeting line
Place AmountDue and AmountDueDate in letter report
The difference between DefaultValue and ControlSource
Place report date in unbound text box on LetterF

4. Generic Letter Report
Making the letter writer generic so we can send to anyone
Add IncludeInMailing field to CustomerT
Create a Mailing List query
Create a generic letter report
Form design tricks with rectangles
Create a button to open generic letter report
Add IncludeInMailing field to customer list form
Add IncludeInMailing field to customer form
Update main menu with button to letter form

5. Post Cards
Create a blank report to use for post cards
Columns in reports
Sizing the post cards properly
Copying data fields and labels from other reports

Access 206

--- SKIPPING in Access 2013 Expert Lessons due to macros - move to ADVANCED ---

Lesson 1. Setting up Task Tables
Create TaskT table
Create PriorityT table
Create CategoryT table
Create RecurringT table

Lesson 2. Setting up the Task Form
Create a TaskF simple form
Create combo boxes for Priority, Category, Recurring
Modify the Row Source for each to Sort the boxes
Create a macro to open each table when combo box dbl-clicked
On Dbl Click event
Macro Names (grouping macros)
Macro to open supporting tables

Lesson 3. Task List Form
Creating a Task List continuous form

Lesson 4. Synch Task List to Task Form
Creating a macro to open a specific form
Setting the OnCurrent event in the Task List Form
GoToRecord macro command (macro action)
GoToControl macro command
Creating a command button without the wizard
Assigning a macro to a command button
SetValue macro command
Using the Expression Builder
Creating buttons to set the due date to today

Lesson 5. More Date Buttons, Requery Button
Button to set due date to tomorrow
Button to set due date plus one, minus one day
Learn about adding fractions of a day
Button to set due date to "later today"
Creating a second bound date field with different format
Creating a button to requery your form results

Access 207

--- SKIPPING in Access 2013 Expert Lessons due to macros - move to ADVANCED ---

Lesson 1. Task Query
Creating a TaskQ query
Create a TaskViewDate form field to limit the date
Base the data in the query on the date on the form
Run our requery macro in the On Open event
Run our requery macro in the AfterUpdate for the date
Copy date reset buttons from TaskF to TaskListF

Lesson 2. Recurring Tasks
Learn about Macro Conditions (groups)
Conditions are IF/THEN statements for Macros
If the Completed box is clicked on a recurring macro increment date
Deal with weekly, monthly, and other tasks
Allow Edits form property
Allow Deletions form property
Allow Additions form property
Locked control property

Lesson 3. Choose to Hide Future Tasks
Create a Show All Tasks check box
If the check box is unchecked, hide tasks by date
If checked, show all of the tasks regardless of date
Use the IIF() function to determine if task in future
Use the NOT keyword to negate values
Complex IIF() function with multiple conditions
Nested statements using AND and OR clauses
Feeding query results into a second query Task2Q

Lesson 4. Filtering Results
Manually filtering using built-in Access filters
Filter By Selection, Remove Filter/Sort
Creating our own filter text and combo boxes
Adding * values to our supporting tables
Review of the LIKE keyword in queries
Manually editing the Column Count and Column Width

Lesson 5. Miscellaneous
Dealing with missing data
Tasks without a category won't show. Fix this.
Compact and Repair Database
Add Tasks button to Main Menu

Access 220

1. Sales Log Table
Create SalesLogT
Default SaleDate to =Date()
Setup PaymentMethodT

2. Sales Log Query
Calculated Query Fields
Multiply Two Fields Together
Calculate Extended Price
Mathematical Operators
Add, Subtract, Multiply, Divide
Integer Division
Exponents
Modulo (Modulus)
IIF Function
Determine If Taxable
Calculate Order Total
Query Field Properties
Round Function
Rounding Field Values

3. Sales Log Form, Part 1
Copy Customer Combo from ContactF
Bring in Calculated Fields from Query
Continuous Forms
Form Header and Footer
Move Labels to Header
Horizontal Line

4. Sales Log Form, Part 2
Create an Order Total in the Form Footer
SUM Function in Footer Field
Totals with Filters On
Show Sales for Specific Customer
Open a Form and Show Specific Records

5. Query Join Types
Only include rows where the joined fields from both tables are equal
Inner Join
Include ALL records from Table1 and matching fields from Table2
Outer Join
Show ALL Customers and Sales if they have them
Show ALL Sales and Customers if listed

6. Global Relationships
Enforce Referential Integrity
Cascade Delete
Tools > Relationships
Why I don't often use Global Relationships

Access 221

--- STOPPED HERE in Access 2013 Expert Lessons --- if you want to keep learning from the CURRENT LOCATION (after Expert 8) then start with this lesson.

1. Aggregate Queries
Summary Queries
Totals Queries
Group Different Records Together
Sums, Averages, Counts
Examples in Excel (for understanding)
Sorting in Excel
Data > Subtotals in Excel
Grouping in Excel

2. Build an Aggregate Query
List of Unique Customer Names Only
Essentially Eliminate Duplicates
Totals Button in Query Design
Show Total Amount Due by Customer
Max Value
Count of Records
Average (Avg) of Amounts

3. Sales Totals By State
Group by State
First Record
Unique Phone List of Customers
Sales Totals by State
Add a Date Criteria
Add Parameters

4. Most Recent Contact Date
When was the last time you contacted a customer?
Show all customers and contacts if they exist
Outer Join on CustomerID
Show Min of DateTime
Oldest Contact Date for Each Customer
Count of Contacts for Each Customer
Renaming Query Fields
Limit by State with Criteria
In() Function
In("TX","NY")

5. Sales Totals by State
Report Page Header & Footer
Sorting & Grouping Levels
Group Footer Visible
Report Section Footer
SUM Total in Group Footer
Visible Property - Hide Report Fields, Labels

6. Employee Time Sheets
Table to Track Time In, Time Out
Join WorkLogT to EmployeeT
Calculate Hours Worked
SUM Up Hours Worked per Employee
CDate Function - Convert To Date

7. Work Log Form
Employee Combo Box
Hours Worked SUM Total in Form Footer
Filter By Employee
Button On Main Menu

8. Time Sheet Form
Parameter Form to Select Employee and Date Range
Display Time Sheet Based on Parameters Entered
Forms!FormName!FieldName
Get EmployeeID From Time Sheet Form
Run Query from Command Button Wizard

9. Time Sheet Report
Employee Data in Group Header
Hourly Data in Detail Section
SUM Hours Worked in Employee Footer
Command Button to Preview Report
"Print Timesheet" Button
Enter Hourly Pay
Calculate Amount Earned

Access 222

1. Intro to Action Queries
Different Types of Action Queries
What do Action Queries Do?
Update Query
Append Query
Delete Query
Make Table Query
Always Back Up Your Database!
Always Back Up Your Tables!

2. Update Query
Change a company name to a different one for multiple records
Build an Update Query with the Query Designer
Update To
Criteria

3. Another Update Query
Change company name only for companies in NY
Query to update all uncompleted Tasks to today's date

4. More Update Queries
Change all states to CAPS (ny to NY)
Change all names to CAPS (joe to JOE)
UCase() Function
LCase() Function
Proper Case with StrConv() Function

5. Yet Another Update Query
Length of a string: LEN() Function
Add an area code to phone numbers that are only 7 digits long
Change email domains from @xyz.com to @abc.com
INSTR() Function
Left() Function
Disecting the username from the email domain

6. Append Queries
Create a query to archive your old contacts
Copy all records older than 1 year to an archive table

7. Delete Query
Delete those archived records from the last lesson
Cascade deletes and global relationships
Cascade update related fields
Viewing related records in a table
Delete query also deleting all RELATED records

8. Make Table Query
Making a backup of a table with a Make-Table Query
AutoBackup Query
Exporting just a specific set of data

9. Automated Queries
Make a form to automate archiving contacts
Specify archive date in a form field
Macro with OpenQuery command
Command Button to Run Macro
Record Selectors to No
Navigations Buttons to No
Turning Off Warnings Permanently
Options > Confirm > Document Deletions, Action Queries
Turning Off Warnings Temporarily
SetWarnings Macro Command
MsgBox Macro Command
 

Access 223

1. Crosstab Queries, Part 1
What is a Crosstab Query?
Similar to an Excel PivotTable
Format() Function "yy-mmm"
Sales Totals by Month
Sales Totals by State by Month

2. Crosstab Queries, Part 2
Create a Crosstab Query
Column Heading
Row Heading
Value
Adding Date Criteria
Defined Query Parameters
Breaking Down by Year
Specify Column Headings

3. Crosstab Query Wizard
Using the Wizard to Build a Crosstab Query

4. Other Query Wizards
Simple Query Wizard
Find Duplicates Query Wizard
Find Unmatched Query Wizard
Finding duplicate customer records
Find customers that have no contacts

5. Query Properties
Showing the Top X Values (eg Top 10)
Showing the Top X% Values (eg Top 10%)
Unique Values
Unique Records

6. Beginner SQL
SELECT field FROM table
Build a Query in the Designer
Switching to SQL View
Multiple Fields
Removing Clutter that Access Adds to SQL
INNER JOIN
WHERE clause
Placing SQL statements in combo box Row Source property
ORDER BY clause
ORDER BY DESC
UNION Query - can only be done with SQL

7. Query Tips & Tricks
IN() Function
Cartesian Product
Count(*) Function
Medium Date Format
 

 

Access 301

1. Create Order Tables
Create the OrderT table for data on an Order
Create the OrderDetailT for line item data

2. Order Queries
Enter sample order data
Create OrderDetailQ
Calculate Extended Price (Price * Quantity)
Round() Function
Format as Currency
Field for Sales Tax Rate
Calculate Actual Sales Tax Value

3. Order Form
Create a SalesRepID to Track Salesman
TechRepID for Technical Support
Different IDs for the Same Field Type
Design the Order Form
Choose Builder - Code Builder
Learning VBA (Visual Basic for Applications)
On Dbl Click
Event Procedure
Private Sub
End Sub
DoCmd.OpenForm
OpenForm and show a specific record
Where Condition

4. Order Subform
Create the Order Subform from scratch
Continuous Forms
Subform Control
Turn off Navigation Buttons

5. Subform Totals
Showing the Totals in the Subform Footer
More with SUM() Function
Get Line Item SalesTaxRate from Order Form
Create a Button to Show Orders Only for Current Customer
Sales Tax Rate Per Customer, Per Order
Setting a Default Sales Rep for each Customer

 

Access 302

1. Product Information
Backup Access 301 Database
Create a Product Table
Non Taxable Products
Sales Tax Override
Put ProductID in Order Details table

2. Product Combo Box
Create a Product Combo
Remember the value for later use
Put Combo in OrderDetails Footer
Command Button to Add Product
Get Data from Combo Box Columns
Combo.Column() property

3. Get Data From Combo Box
ProductCombo.Column()
Move to a new record in VB code
DoCmd.GoToRecord
Accessing data from multiple columns

4. DLOOKUP
What is DLOOKUP
When should you use DLOOKUP
DLOOKUP to get values from tables
Me.Refresh
DLOOKUP Order Tax Rate from CustomerT
DIM a variable in VB
Basic Error Handling
On Error Resume Next
 

Access 303

1. Order List Form
Create a Form to Show All Orders
Deal with adding a product when no order exists
Force user to select a sales rep
Create OrderListQ with LineTotal

2. On Double Click
Double-click on Customer to open Customer record
On Dbl Click Event
Always Use Event Procedures
Command Button to OpenForm Without Wizard
Double Click Event to Open Specific Order

3. Bill To & Ship To Address
Why Store Duplicate Data
Bill To / Ship To Addresses for Customers / Orders
Automatically copy one to the other if blank
Fix tab order

4. Subroutines
DLOOKUP Both Addresses on Order Form
Add New Order Button
acFormAdd
Create Your Own Subroutine
Private v. Public Subroutines
Calling Public Subs from a Different Form

5. Missing Records
DLOOKUP SalesTaxRate for Orders
Fix Joins in OrderListQ
Show All Orders even if Missing a Customer
 

Access 304

1. Quotation or Invoice
Is this a quote or an invoice?
IsQuote field
Add Browse All Orders button
AfterUpdate Event
Event when a field is updated
Change button color and caption
Access numbered color codes

2. On Current Event
Event when user moves between records
Add IsQuote to Order List
Filter By Selection
Add sum of orders to order list footer

3. Printable Invoice, Part 1
Create a Master Invoice Query
InvoiceQ
Sorting & Grouping Levels
Keep all Order information together
Group By OrderID

4. Printable Invoice, Part 2
Arrange controls in Detail section
Force New Page After Section property
Invoice Footer Totals

5. Printable Invoice, Part 3
File > Page Setup > Margins
Resize invoice layout
Quote or Invoice Label
Dynamically change label in Build Event
Create page header
Add company logo to top of invoice
Stretch, Zoom, and Clip modes for graphics

6. Printable Invoice, Part 4
IsPaid Label
Hiding fields on a report
Can Grow, Can Shrink Properties
Create a Print Button on the Order Form
Printing just a single invoice

 

Access 305

1. Accounts Receivable Query
Aged Accounts Receivable
Create a Query with OrderTotals
Add OrderDate to tell Age of Order
Use IIF Function to Add Aging
Current
LessThan30
LessThan60
MoreThan60
Add DaysOld Field

2. Accounts Receivable Report
Create the report
Format as Currency
Hide $0.00 values with VBA Code
The Visible Property
Put SUMs in the Report Footer
Create an AR Total

3. Percent Discount on Orders, Part 1
Discounts as a dollar amount
Percentage Discount
Add PercentDiscount to OrderT
Add PercentDiscount to OrderDetailT
Adjust SalesTax to figure Discount
Link the Discount default to OrderF

4. Percent Discount on Orders, Part 2
Add PercentDiscount to CustomerTable
Add PercentDiscount to CustomerForm
Add to AfterUpdate event CustomerCombo
Update DiscountRate on Line Items if Form Changes
Create an Update Query to change Rate
Create an AfterUpdate event to run Update Query
MsgBox to Get a Response
vbYesNo, vbYes, vbNo
Line Continuation Character in VB Code
Dim variables
Option Explicit

5. BeforeUpdate
The Cancel parameter of BeforeUpdate
OldValue property
vbYesNoCancel

Access 306

Employee Commission Rates based on single rate, based on different rate per employee, based on order amount. Nested IIF Functions.

CommissionLookupT, paying employees commission based on order amount and separate rates for each employee. DMax. Using DMax with DLookup to pull a commission rate based on employee and largest order amount. (Note DMin is same as DMax but looks up the smallest value)

Commission Form. Me.Requery on opening a form (getting no records because underlying query is based on a checkbox on the form).

Commission Report to group commission amounts by employee. Update query to mark all commissions paid and track date paid. Using InputBox to ask for a password when a button is clicked.

Employee PayRateT. Calculating employee pay rates based on employee and dates worked. Another DMax, DLookup example. Calculating regular and overtime hours and total pay rates.

Access 307

Lesson 1. Custom Search Button
Docmd.FindRecord
Docmd.GoToControl
InputBox
Custom Sub with Parameter

Lesson 2. Dynamic Combo Boxes Part 1
Combo boxes that control (filter) other combo boxes
Form_BeforeUpdate
Cancelling BeforeUpdate
Verifying user data before allowing them to leave a record

Lesson 3. Dynamic Combo Boxes Part 2
ComboBox.Requery
Subform Link Master ID

Lesson 4. Simple Database Security
Set Database password
Open Database Exclusive Access
Unset password
Converting a database between versions
Creating an MDE file

Lesson 5. Splitting a Database
Database Splitter Wizard
Linking Tables
Linked Table Manager

Access 308

Lesson 1.
- Working with External Data
- ODBC (Open DataBase Connectivity)
- Importing & Exporting Data
- Linking v. Importing
- Examples

Lesson 2.
- Exporting to another Access database
- Creating a Blank Sales Rep database
- Exporting Customer Table
- Exporting to an Excel Spreadsheet file
- Creating a query to limit our data
- Exporting the query data to Excel
- Using the OfficeLink button
- Analyze It with Microsoft Excel
- Publish It with Microsoft Word

Lesson 3.
- Export Text: Comma Delimited
- Export Text: Fixed Width
- Create EXport Specification
- Create a Macro to Export Text
- TransferText Action
- Create a Macro to Export to Excel
- TransferSpreadsheet Action

Lesson 4.
- Import From Another Database
- Appending Records To Existing Table
- Matching Field Names (Append Query)
- Creating Values For Unmatched Fields
- Import From an Excel Spreadsheet
- Import Wizard

Lesson 5.
- Importing Delimited Text File
- Importing Fixed Width Text File
- Fields That Are Directly Adjacent
- Linking To Tables in Another Database
- Linking To A Spreadsheet
- Linking To A Named Range in Excel
- Linking To A Text File

Lesson 6.
- Splitting Single Name Field into Two
- INSTR Function
- LEFT and RIGHT Functions
- LEN Function

Access 309

1. Relationships Review
One to Many
Many to Many
Cross Reference Tables
DriverXVehicleT

2. Cross Reference Table
Creating the Many-To-Many Relationship
Making a Cross Reference Table
Creating a Group Table for our Customers
CustomerXGroupT
CustomerXGroupQ

3. Group Form
Creating the Group Subform
Many To Many Form

4. Converting Numbers into Dates
Got a field with dates like 990105
Convert with an Update Query to 1/5/99
LEFT, RIGHT, MID functions
Update Query
String Concatenation
Calculated Fields

5. Addendum on Numbers to Dates
What if you have dates with 5 digits?
Using LEFT and LEN functions
CDate() to convert text to dates

Access 310

0. Introduction - 4:33

1. Conditional Formatting in Forms - 5:05
Changing form field formatting based on data

2. Resizing a Form with VBA - 6:58
Using Visual Basic code to resize forms when they open
Me.InsideHeight, Me.InsideWidth
What are Twips

3. Company Settings Table, Part 1 - 11:23
Moving our company logos from forms/reports into table
Benefits: easily changed, saves space
Creating a company settings table
Locked Property
Inserting a Bound Object Frame in a report
DLOOKUP in a report object control source

4. Company Settings Table, Part 2 - 7:17
Closing one form when another one opens
DoCmd.Close
OnClose Event
When an open record on one form locks another
Using DLOOKUP to get other values from company settings
Changing the form caption property
OnOpen Event for a form
Me.Caption

5. Tracking Payments, Part 1 - 21:31
Payment table and form (PaymentT, PaymentF)
Button on order form to show payments for current order
DoCmd.GoToRecord acNewRec to go to a new record
Using DSUM() to add up all of the payments for an order
How is DSUM different from DLOOKUP
Popup and Modal properties for a form
Refreshing the records on a different form
Forms!OrderF.Refresh and Me.Refresh
Calculating the amount due on an invoice
Accessing a value on a subform
Forms!OrderF!OrderSubform.Form!OrderTotal

6. Tracking Payments, Part 2 - 15:19
Warning users not to make a payment if invoice is PAID
vbExclamation
vbYesNo+vbExclamation
Creating a new value called MyReply in VBA Code
Exit Sub
Option Explicit
Explicitly declaring variables
Using DIM to create your own variables
Adding more logic - don't allow a payment if it's a QUOTE
Using the BeforeUpdate Event
Don't allow a user to change an invoice to a quote if PAID
Cancelling a BeforeUpdate Event
Don't allow a user to mark a QUOTE as PAID
Don't allow a user to put payments on a Quote
Refreshing the form record before printing an invoice
Putting payments and amount due on the Invoice report: DSUM
When [OrderT].[OrderID] doesn't work use [OrderT.OrderID]

7. Tracking Payments, Part 3 - 18:28
Putting payment info on Invoice report
Hiding the payments field and label if there are no payments
OrderT.OrderID Footer Section Build Event
GroupFooter1_Format section
Invalid Use of Null
What to do if there are no payments?
On Error Resume Next
If IsNull(Field) Then
Hiding a line
Creating a "fake" amount due for orders with no payments
Getting rid of empty space for invisible fields
Labels don't have CanGrow or CanShrink properties
Changing labels into text boxes with ChangeTo
Making the label caption into a control source
Hiding the payments and amount due if the invoice is paid
Hiding the amount due if it's a quote

8. Tracking Payments, Part 4 - 12:14
Having the database notify us if enough payments received
Do you want to mark this invoice PAID?
vbQuestion
Notify the user if the customer has overpaid
Fixing our accounts receivable report
Adding the total payments to accounts receivable: DSUM

Access 311

0. Introduction - 2:42

1. Unit Cost on Orders - 13:16
Being able to calculate profit by knowing item costs
Adding UnitCost info to OrderDetailT
Creating an ExtendedCost field (UnitCost * Quantity)
Adding UnitCost and ExtCost to our OrderDetailF
Adding calculated totals to footer
Adding Cost fields to our Product Table
Get the product cost when a product is selected from ComboBox

2. Sales Report - 12:07
Creating a Sales Report with Cost & Profit
Add cost information to OrderListQ
Create the SalesReport Query and Report
Format() function to force numbers to show as currency

3. Sales Report & Form - 13:49
Adding totals to our Sales Report
Remember you can't put SUMs in your Page Footer
Create a Report Footer
Sort our Sales by Date - Sorting & Grouping Level
Group Header & Footer ON
Group On: Month
Format() Function: Format(OrderDate,"yyyy mmmm")
Criteria form to set begin date and end date for sales report
The Between keyword (remember me?)
[Date] is no good. Use Date()

4. Dynamic SQL Form 1 - 10:10
Clean up Customer List Form
Added Browse Customers and Add New Customer Buttons
DoCmd.GoToRecord acNewRec
Clean up Order List
Create an OrderList2Q so we can make more calculations
Add AmountDue information to OrderList form
Discuss what the Dynamic SQL Form is going to do

5. Dynamic SQL Form 2 - 14:44
Create a combo box with a list of options:
 - Show Invoices & Quotes
 - Show Quotes Only
 - Show Invoices Only
 - Show Paid Invoices Only
 - Show Unpaid Invoices Only
These are the options we want to see in our Order List Form
Set the default value for the combo box
Create an unbound text box to hold our SQL statement
Review of SQL Statements
SELECT Fields FROM Table WHERE Criteria ORDER BY Field
Create the BuildSQLStatement sub
Creating your own Private Sub
Set the form RecordSource equal to our SQL statement
Me.RecordSource = MySQL
Using VBA Code to build the SQL statement based on selection
Using a SELECT CASE statement instead of IF THEN statements
Creating another combo box for sorting:
 - Sort by Order Date
 - Sort by Company Name
 - Sort by Last Name
 - Sort by Order Total
 - Sort by Amount Due
Add another SELECT CASE to handle the ORDER BY clause

6. Dynamic SQL Form 3 - 17:07
Checking our Sort Combo Filter
Put a call to BuildSQLStatement in the SortCombo build event
Put a call to BuildSQLStatement in the form OnOpen event
DEFINITION - right click on a function or sub name to jump
Create our own Sort A to Z (ascending / descending) button
Capturing an image on the screen with Print-Screen
Drop that capture into Windows Paint
Cut out the buttons you want to "borrow"
Paste the buttons into your form.
Change the border style (raised, sunken)
Change the button names: SortAZ, SortZA
Make SortAZ visible, SortZA not visible
If the SortZA button is visible, add "DESC" to SQL Statement
Make OnClick events for each of these buttons
Change Enabled = YES for these buttons
"You can't hide a control that has the focus"
Shift the focus with DoCmd.GoToControl
Hide your SQL text box
Add buttons to browse orders, add new order
Change SortAZ and SortZA buttons to IMAGES not OLE objects

Access 312

0. Introduction - 5:29

1. Secondary Menu - 18:00
Creating a Secondary Main Menu
Copy Main Menu to Secondary Menu
Issues when copying buttons - code doesn't copy
Creating a Notepad field on the main menu
Saving Notepad data when you leave the field
OnLostFocus, OnGotFocus
Me.Refresh
Dirty Records (Unsaved Data)
Record Cycle: All Records, Current Record, Current Page
Make a button to the Secondary Menu
Make a button for our Accounts Receivable Report
TRIM() Function to remove spaces

2. Resizable Customer Form - 13:11
Get rid of some unnecessary fields on the Customer form
Add some new phone fields (home, cell)
Move fields we don't use a lot way over to the right
Determining the size of your form in Twips
NOTE: A Twip is 1/20 of a point, or 1/1440 of an inch
Me.InsideHeight, Me.InsideWidth
Create a button to resize the form to your specifications
Put code to make the form small in the form OnOpen event

3. Customized MyCompany Fields - 10:14
Add fields to the SettingsT
Creating a Company DefaultTaxRate
Adding InvoiceBottomText
Add to Settings form
Scale of a Decimal - digits to RIGHT of decimal point
Getting a new customer's tax rate using DLOOKUP
Using the BeforeInsert event
InvoiceBottomText on the Invoice - make it a text box
PageFooter Build Event
GroupHeader0_Format
Use DLOOKUP to get text from SettingsT

4. Creating a Service Invoice, Part 1 - 11:32
Creating a different invoice for services (vs. products)
Copy InvoiceR to InvoiceServiceR
Start by moving around the fields you want
Remember to move VB code from section to section if needed
Changing [OrderT].[OrderID] to [OrderT.OrderID]
Add Customer FirstName, LastName fields to OrderT
Change CustomerCombo.AfterUpdate to get Name fields

5. Creating a Service Invoice, Part 2 - 15:11
More basic field edits
Putting a border around a field that grows / shrinks
Inside margins for text boxes on reports
Left Margin, Top Margin, Right Margin, Bottom Margin
Leaving the Description of Job box there even if empty
Hiding fields that aren't necessary
The problem with vertical lines on reports - they won't grow
Creating our own lines with VBA code
Detail Section Build Event
Detail_Format code
Use the Me.Line command to create our own lines
Switching from Twips to Inches or Pixels with Me.ScaleMode
Creating a diagonal line in code
Changing the width of the line with Me.DrawWidth
Using Me.DrawStyle to create different types of lines
Changing the thickness of a regular line from hairline to 1

6. Customizing The Order Form - 17:06
Adding a Tab Control to the Order Form (BillTo / ShipTo)
Make a new command button to print the service invoice
Fixing the InvoiceR - some of our changes broke it!
What to do with CustomerCombo if there is no company name
Show either the company name OR the LastName, FirstName
Create the CompanyOrName field
If the company is null, show customer's name
Fix the OrderListQ - get customer name data from OrderT
Adding Sums to OrderList form
Changing the default sort of the order list form

Access 313

0. Introduction - 2:56

1. Product Category Master Form, Part 1 - 16:45
Making one subform control another with SQL RecordSource
Create the product category table: ProductCategoryT
Put a Product Category combo box on the Product Form
You can't put a subform in a continuous form
Make a second product list
Add ProductCategoryID to the Product Table
Creating a subform by dragging from the db window
Put two subforms side-by-side: Categories & Products
Filter the products based on the category I've selected
Give good names to each subform
Note that these are UNLINKED subforms - not linked to parent
Change OnCurrent event for Category subform
Set the RecordSource of the Product Subform to SQL
Forms!FormName!SubformName.Form.Property
Showing all records - include "*" record and handle it in VB
Dim a String of characters (variable)
Sorting the Category List

2. Product Category Master Form, Part 2 - 8:48
Lock the product subform
AllowEdits, AllowDeletions, AllowAdditions: set all to NO
Do the same trick and show the product form on the bottom
Set the OnCurrent event for the product list to show product
Adding a ProductCategoryCombo to the product form
Set the combo's default value equal to the current category
Add an "Add Product" button
Adding Refresh buttons

3. Product Category Combo on Order Form - 6:59
Copy the ProductCategoryCombo from ProductF to OrderDetailF
When you pick a category, rewrite the SQL of the ProductCombo
ProductCategoryCombo AfterUpdate Event:
Set the ProductCombo.RowSource = SQL Statement
Deal with the "*" to show all records - special IF/THEN
Automatically open the ProductCombo when a category is picked
ProductCombo.DropDown

4. Auto Zoom to Notes Field - 6:07
Automatically zoom in when focus moves to notes field
OnGotFocus for Notes textbox
DoCmd.RunCommand acCmdZoomBox
Discussion of RunCommand options in macros
Converting Macros to Modules
Create a macro and save it as a module to see VBA code
Editing the tab order, removing tab stops
Move the Zoom feature to the On Dbl Click event

5. Hidden Items on Invoice - 15:18
Items on invoices that you don't want the customer to see
Add ShowOnInvoice field to ProductT, OrderDetailT
Purpose of this to calculate job costs secretly
To hide item on invoice report, CANCEL the build section
Add ShowOnInvoice checkbox to detail section, make invisible
Go into BuildEvent
If Not ShowOnInvoice Then Cancel = 1, Exit the sub
Remember to add the same code to our other invoice report

6. Splitting the Database - 11:12
Compact the database
Split the database in two - covered in Access 307
Where your BACKEND database is located
Creating a new folder for your database - C:\Database
Move both database files to new folder
Open front-end database and it will ask for table locations
Load up the Linked Table Manager and point to Back End
Talk about setup on a network using a mapped drive or UNC path

Access 320

1. Recordset Basics
What is a Recordset?
Why use Recordsets?
Example Uses For Recordsets
Turn Off VBA Project Explorer
Tools > References
What is a Library?
Microsoft DAO 3.6 Object Library
Microsoft ActiveX Data Objects 2.1 Library
DAO vs. ADO, Brief Discussion

2. Our First Recordset
Create a Database Object
Create a Recordset Object
OpenRecordSet
db.Name, rs.Name
Closing Our Objects
Freeing Memory (Set to Nothing)
Creating a Status Box
Display Field Data: FirstName
When to use ! vs .
Ways to Refer to Fields
rs!Fieldname
rs("Fieldname")
rs(Index)
rs![First Name]
Create Unbound Text Boxes
Store RS Data Into Text Boxes

3. Moving Around, Part 1
MoveNext
MovePrevious
MoveFirst
MoveLast
Move X
EOF End of File
BOF Beginning of File
Endless Loops
Create a Counter
Dim Variables Outside of Subs
Variable Scope
Opening Recordset With Form Opened
Closing Recordset When Form Closed

4. Moving Around, Part 2
Buttons to Move Around Recordset
Dealing with Hitting EOF/BOF
RecordCount Property

5. Finding Records, Part 1
FindFirst
Types of Recordsets
dbOpenTable
dbOpenDynaset
dbOpenSnapshot
dbOpenForwardOnly
NoMatch

6. Finding Records, Part 2
Wildcard Search
Dynamic SQL For FindFirst
Do Loop
FindFirst
FindPrevious
 

Access 321

1. Customer ListBox, Part 1
Open Recordset with SQL
SQL WHERE Statement
Adding Items to a ListBox
AddItem
Row Source Type: Value List
Access 2000 Users: No AddItem

2. Customer ListBox, Part 2
Add the ID
Column Widths
Clearing Your List Box
ListCount
RemoveItem
Combo of Unique States

3. Edit Records
Editing One Record
rs.Edit
rs.Update
Editing Multiple Records
Loop Through And Edit

4. Multi-Select List Boxes
Simple v. Extended
Loop Through Items With ListCount
.ItemData(x)
.Column(1,Row)
.Selected Property
For Each
Object Collections

5. Adding & Deleting Records
Adding a Record
AddNew
Sorting Our Customer List
Deleting Records
rs.Delete
Always Have an Are You Sure? Prompt

6. Execute
db.Execute
INSERT INTO SQL Command

Access 322

1. Recordset Tips, Part 1
Recordset Options
- dbAppendOnly
- dbDenyWrite
- dbDenyRead
Record Locking Options
- dbPessimistic
- dbOptimistic
RecordCount Not Working
- dbOpenTable: OK
- dbOpenDynaset SQL: OK
- dbOpenDynaset Table: NOT OK
- dbOpenSnapShot: NOT OK

2. Recordset Tips, Part 2
Seek Method
Must Be Indexed Field
Limitations of Seek
CancelUpdate Method

3. Opening a Different Database
OpenDatabase Method
Reading One Value From Multi-Select Box
OnDoubleClick - Which Is Selected?
Reading Records From A Different Database
OpenDatabase

4. Fields Collection
Displaying All Field Values
Showing Field Properties
Fields Collection
Type of Data
Using the FieldName as a Variable
rs(FieldNameVar)

5. Project: User Logon, Part 1
Why I Don't Like Access User Level Security
Create a User table
Create a Logon form
Password type Input Mask
Recordset to Check Password
Default Command Button

6. Project: User Logon, Part 2
Create a Log table
LogIt function
With, End With
Make LogIt a Global Sub in a Module
Global Variables
Global CurrentUserID, CurrentUsername
Cancel a Form OnOpen Event

Access 323

1. ADO ActiveX Data Objects, Part 1
Benefits of ADO v. DAO
Create a Database Connection
CurrentProject.Connection
Provider
Recordset Types
- adOpenDynamic
- adOpenForwardOnly
- adOpenKeyset
- adOpenStatic
- adOpenUnspecified
Lock Types
- adLockReadOnly
- adLockPessimistic
- adLockOptimistic

2. ADO ActiveX Data Objects, Part 2
Loop Through Records (EOF)
Editing Records
Default is a READ ONLY Lock Type
AddNew to Add a New Record
Delete to Delete a Record
ADO Connection Strings
Connect to SQL Server
Connect to Oracle
Using Both ADO and DAO Together

3. Student Test Taker, Part 1
Question Table
Answer Table
Teacher Q/A Form
Student Table
Response Table

4. Student Test Taker, Part 2
Test Taker Form
Student Combo Box
Question Text Box
Answer List Box
Take Test Button
Disable the Student Combo
Disable the Test Button
Check to Make Sure Student Picked
Auto DropDown the Student Combo
DMin Function to Find Next QuestionID
Nz Function To Return Zero on Null

5. Student Test Taker, Part 3
Load List Box with Answers
RecordSet for Answers
List.AddItem
Button to Save Results
Save With RecordSet to ResponseT
Move To Next Question
Lock the Next Button When Test Done

6. Student Test Taker, Part 4
Query To Show All Student Answers
Different Links Give Different Results
How Multiple Relationships Interact
Report Grouped By Student
Grouping Options Button in Wizard
Summary Options Button
Calculate Student Average
Count of Records
Total Correct
Hiding the Detail Section

Access 324

1. Loan Calculator, Part 1
Calculating Loan Amortizations
Enter In Data For Loan
Price Of House
Down Payment
Interest Rate
Term of Loan
Calculate Amount Financed
Calculate Monthly Payment
Using the PMT Function
PMT in Excel
Calculate Total Payments
Calculate Interest Paid

2. Loan Calculator, Part 2
Build the Loan Form
Build the Payment Form
Use a RecordSet to Create Payments
DateAdd Function
Me.Requery to See Subform Updates

3. Loan Calculator, Part 3
Payment Total SUM
Label to Filter Payments
Show Current Payments
Show All Payments
Me.Filter
Me.FilterOn
Turning the Filter On and Off
Check For Existing Payments
DLookup Any Existing Payments
Delete Query to Erase Schedule

4. Loan Calculator, Part 4
RecordSet to Add Manual Payments
Change RecordSource Better Than Filter

5. Loan Calculator, Part 5
Fixing the Missing Penny Fraction

6. On Not In List Event, Part 1
Customer Table
State Table - Storing Text
Add Different State - Not In Table
AdSource Table - Storing a Number
Limit To List Property

7. On Not In List Event, Part 2
NotInList Event
NewData Parameter
Response Parameter
acDataErrDisplay
acDataErrContinue
acDataErrAdded
Ask User If They Want To Add Data
Recordset to Add Value To Table
Using InputBox for Additional Fields

Access 325

1. Compare Two Tables, Part 1
Synchronizing Two Tables
Discuss Replication
Detecting Changed Records
Exit For

2. Compare Two Tables, Part 2
ListBox to See Different Records
Customer SubForms
Change SubForm Data in VBA
Subform.SourceObject
Subform.Form.RecordSource

3. Compare Two Tables, Part 3
Importing Changes
Detecting New Records
Find Unmatched Query Wizard
What Does Unmatched Wizard Do?
Change RowSource of ListBox
Importing New Records

4. Student Attendence, Part 1
Tables to Track Students & Days Absent
Multi-Select Listbox to Pick Students
Recordset to Add Absent Days
Table to Track Days Off (Holidays, etc.)
Aggregate Query to Count Absent Days
Global Module
Public Function DaysInSchoolYear

5. Student Attendence, Part 2
DLOOKUP Start / End of School Year
Loop Through All Days
Count School Days
Exclude Weekends, Days Off
View > Immediate Window
Checking Variables
Testing Function Return Values

6. Student Attendence, Part 3
Count of Absent Days Query
Show Days In Class (Subtract From Total)
Show Percentage
Placing Start / End School Year in Table
Showing Days Attended up to Any Date
Form Cycle: Current Record Only
Saving Data To Table (Refresh) AfterUpdate
Button to Display Student Query

Access 326

1. Locking Orders When Paid
Pull Databases Back Together
Lock the Order if it's Paid
Me.AllowEdits
Me.AllowDeletions
Me.AllowAdditions
OrderSubform.Form.AllowEdits
OrderSubform.Form.AllowDeletions
OrderSubform.Form.AllowAdditions

2.Refunding an Order
Refund Button
Marking Unpaid in VBA Code
Can't Hide a Control That Has Focus
Pay a Quote Bug
Me.Undo

3. Ship Date
Add ShipDate Field
Lock The ShipDate
Show the ShipButton only if needed
ShipOrder Button
Can't Refund a Shipped Order

4. Inventory Levels
Quantity On Hand (QtyOnHand)
Adding Our UserLogon Form To Database
Adding a Log feature
Field.OldValue
Tracking which employee changed inventory level

5. Items Already On Order
Add QtyOnHand to Product Combo on OrderDetailF
Increment Quantity if already on order
Warning if no product picked
Create a recordset to increment quantity
Compile error: ByRef argument type mismatch

6. Shipping The Order, Part 1
OKToShip Function
Check to see if we have enough inventory on hand

7. Shipping The Order, Part 2
Optional Parameters
ChangeInventory function
Actually ship the items

Access 327

1. Shipped Orders
Add ShipDate to Order List
Show Shipped/Unshipped Orders
Sort by ShipDate
Create a Packing Slip
PackingSlipButton

2. Processing Returns
Processing Cancelled Shipments
Modify OkToShip Function to Handle Adding Inventory

3. Items Not In Product Table
Shipping Orders With Products Not In Database
Notify user of manual item
Add "manual item" to packing slip
Ask user if they want to go into negative inventory

4. Shipping Form
Create ShipHowT for Shipping Methods
Add Shipping Info to OrderT
Create Shipping Form
Modal, Popup Form linked to OrderT

5. Tracking On The Web, Part 1
Figuring Out UPS Tracking Links
How are Hyperlinks Stored in Access Tables
Building a Hyperlink

6. Tracking On The Web, Part 2
Following a Hyperlink in VBA Code
Automatically Launching your Web Browser
US Mail and FedEx Tracking URLs

Access 328

1. Reorder Levels
Add Fields to Product Table
Reorder Level
Vendor Information
Quantity On Order
Vendor Table
Add To Product Form
Query to show Products Understocked

2. Purchase Orders
Products Understocked Form
Purchase Order Table
Purchase Order Details Table

3. Order More Button
Create Order More Products Sub
InputBox to Ask How Many
Convert string to number: CLng function

4. Make Purchase Order, Part 1
Create the Make Purchase Order Code
Creating a PO with a Recordset
Get the assigned Autonumber for a record
Check if there is a PO for this vendor

5. Make Purchase Order, Part 2
Check if this item is already on a PO
Use recordsets to create the PO and details
Verify table data

6. Purchase Order Form, Part 1
Create Purchase Order Details Subform
Create an unbound ListBox to show all POs
OnClick event to show only items on selected PO
Bind the form to Purchase Order Table
Show Notes

7. Purchase Order Form, Part 2
Check Box to Filter Open/Closed POs
Check Box to Filter Received/Not POs
Tri-State CheckBoxes (Triple State)
QuickLinks to Set the Checkbox Values

Access 329

1. Purchase Order Report, Part 1
Printable Purchase Order
Grab MyCompany Info from InvoiceR
PO Information
Line Item Information
SUM of Items Quantities
COUNT of Line Items
WhereStr to WhereBox
Print Selected PO

2. Purchase Order Report, Part 2
Print All Listed POs
NoData event if no purchase orders

3. Closing POs, Receiving Parts Part 1
Mark PO as Sent to Vendor
UPDATE Query With Just SQL
UPDATE Table SET Fields WHERE Condition
Mark PO as Parts Received

4. Closing POs, Receiving Parts Part 2
Hiding Unnecessary Buttons in Code
"Sent to Vendor" label on PO

5. Emailing Purchase Orders, Part 1
File > Send To > Mail Recipient as Attachment
HTML Format
Snapshot Format
Microsoft Access Snapshot Viewer
Text Format
DoCmd.SendObject
acFormatHTML
acFormatTXT
acFormatSNP
acFormatRTF
acFormatXLS

6. Emailing Purchase Orders, Part 2
PO Report not Formatted Good for Email
Also Sends EVERY PO
Making a Unique PurchaseOrderEmailR

7. Sending Bulk Email to Customers
Add buttons for new forms to our menus
Add IncludeInEmails field to CustomerT
Bulk eMail List
Recordset to Send Bulk Email
acSendNoObject
Microsoft Outlook Email Security "Feature"

Access Seminars
Access Web Data Sync Seminar

0. Introduction (14:17)
Topics Covered
Pre-Requisites
Software Required
Important Information

1. WebBrowser Control (9:23)
Adding WebBrowser in Access 2007
Adding WebBrowser in Access 2003
Adding WebBrowser in Access 2000
Command Button to Go To Web Page URL
WebBrowser0.Navigate

2. WebBrowser Navigation (9:14)
User Input URL for WebBrowser
Add Text Box URL Control
Check if the WebBrowser Navigating?
IsNavigating Check Box
WebBrowser DocumentComplete
WebBrowser DownloadBegin

3. Reading HTML & Page Text (10:21)
Show Current URL
DocumentComplete URL Parameter
Read HTML in a Web Page
Read Text in a Web Page
For Each Next Loop
WebBrowser.Document.ALL
OuterHTML
OuterText

4. Parsing Page Data (10:15)
Understanding URLs
URL Parameters (QueryString)
Use Mobile Versions if Possible
Parsing Page Data
String Manipulation
InStr, Mid Functions
Reading Weather Data
Finding the Current Temperature

5. Insert Data Into Table With SQL (9:14)
Create TemperatureT Table
Append Query
INSERT SQL Statement
DoCmd.RunSQL
DoCmd.SetWarnings

6. Insert Data With Recordset (9:29)
setup Recordset
OpenRecordset
Save Data
Recordset Issues With Access 2000
Microsoft DAO Object Library
Declaration Issues With Access 2003
Option Explicit
Timing Issues With Access 2003
IsNull Problem

7. Automated Download With Timer (9:25)
OnTimer Event
Timer Interval in Milliseconds
Navigate Automatically
Avoid Multiple Simulataneous Events
Event Running Indicator

8. Separate Timer Form (10:54)
Create a Timer Form to Launch Events
DoCmd.OpenForm
Public Sub LoadURL
Integer Division and Modulus
While Wend Loop
DoEvents
DoCmd.Close

9. Recording Multiple Events (12:17)
Checking Temp in Multiple ZIP Codes
Adding ZIP Code to Table
Stop Timer Checkbox
Recordset to Loop Through ZIP Codes
Recordset While Loop
rs.EOF
rs.MoveNext

10. Checking Stock Quotes (8:45)
Copy Web Form
Me Keyword
Edit VB Code for Stocks
Using Quote.com to Get Stocks

11. Working With Text Files, Part 1 (10:38)
Why Use Text Files?
Analyzing Data Line by Line
Creating a Temporary Text File
Writing Text Files
Open Text File for Output
Problem with Windows Vista Security
Reading Text Files
Open Text File for Input
Line Input
Text File EOF
Left String Function
CSng Function - Convert String to Single

12. Working With Text Files, Part 2 (7:22)
Modifying our Recordset
Saving the Stock History Data
Invalid Use of Null
Debugging Break Points
Turn on the Debug Toolbar
Step Into, Over, Out of Code Lines

13. Working With Text Files, Part 3 (10:27)
Replace Function
CLng Function - Convert to Long Integer
Switching to Stocks.mobi
Viewing the HTML of a Web Page
View Source
Form Post Problem
Converting Form Post to Query String
Adjust Code for New Data
Storing the Stock Data

14. Multiple Automated Stock Quotes (9:41)
Add Check Stock Quotes to Timer Form
Create Stock Lookup Table
Automate Lookup of Multiple Stocks

15. Acquire Data on Your Web Site (12:35)
Using Expression Web or FrontPage
What if you use a different Web Editor?
Using ASP (Active Server Pages)
For More Info See my ASP Courses
Also See Web Database Seminar
Open my Web Site in Expression Web
Create a SubSite in my Web for Data Collection
Create an HTML Form to Collect Data
Database Creation Wizard
fpdb Folder
Global.ASA File - Don't Touch It

16. Custom Data Collection ASP Page (15:32)
You can't just drop a database in your Web
Clean up the Junk from the HTML Form
Create our own Submit.asp
Request Data from Web Form
Perform Data Validation (Missing or Bad Data)
Saving Data to the Database

17. Page to Display Collected Data (9:04)
ASP Code to Display All Collected Records
Basic Security for Your Display Page

18. Download Collected Data to Access (15:19)
Create Another Access WebForm
Modify Recordset for New Data
Gather Data from Web Site

19. Delete Records from Server, Part 1 (8:41)
ASP Page to Delete All Server Records
Modifying Access WebForm to Delete Records
Specify Operating Mode
AnalyzeDeleteRecords Sub
Dealing with No Records to Display

20. Delete Records from Server, Part 2 (7:41)
Browser Caching Problems with IE
Careful With Your IF/Then Logic

21. Virtual Page Data on Web Site (11:46)
Create a Virtual Page Database
Modify Access Database on the Web Site
Create a Virtual News and Sales Data Page

22. Local Virtual Page Database (5:24)
Create Local Virtual Page Data Table / Form
HasChanged Event & Indicator

23. Post Data From Access to Web, Part 1 (9:14)
Build ASP Page to Accept Data from Access
New WebForm to Post Data to Web Site

24. Post Data From Access to Web, Part 2 (10:00)

25. Posting Large Fields to Web, Part 1 (10:54)
Create HTML Web Form to Accept Data
Give your HTML Form a Name

26. Posting Large Fields to Web, Part 2 (12:24)
Set Web Form Values from JavaScript in VBA
Clicking a Submit Button with Code
Converting Line Breaks to BR

27. Product Database Access to Web, Part 1 (11:45)
Create Product Table for Access DB
Create Form and Database on Web Site
Update Field Types in Web Database

28. Product Database Access to Web, Part 2 (10:38)
Auto Add New Records if Not Found
Create a ProductF Form in Access
HasChanged BeforeUpdate event
Update WebForm code

29. Display Product Listing on Web Site (13:30)
Build Product List ASP Code on Web Site
Design Loop with Records to Display Table
Add Picture URLs
Get Images from Web, Store on Server

30. Review (4:15)

Access Calendar Seminar

00. Intro (7:35)
Topics Covered
Pre-Requisites

01. Create Database (7:02)
Turn on Overlapping Windows
Create Database File
Create Calendar Table
Create Calendar Form

02. Appointment List 1 (13:56)
Form to List Appointments
ApptListF
Double-Click to Open Appt
OnDblClick Event
Show Closed Items Checkbox

03. Appointment List 2 (7:54)
Triple State Checkbox
Show Closed, Open, All Appts
Create Dynamic SQL Rowsource
Refresh Button
List.Requery
Change Form Caption in VBA

04. Monthly View Form 1 (11:39)
CalendarQ With Short Time
DatePickerF Enter a Date
Start Monthly Form

05. Monthly View Form 2 (10:23)
Day 2 of our Calendar Form
Text Boxes to Show Dates
Make 7 Boxes for the Week

06. Monthly View Form 3 (11:30)
Calendar Control
Calculate First Day of Month
First Sunday on or Before 1st

07. Monthly View Form 4 (10:51)
Grey Out Days of Diff Months
Forms!Form("FieldName") Notation
&HFFFFFF Color Notation

08. Monthly View Form 5 (10:26)
Shrink Text
Get Rid of Horizontal Scrollbars
DblClick Event to Open Appointments
Use Excel to Generate VBA Code

09. Calendar Report 1 (9:40)
Design Printable Monthly Report

10. Calendar Report 2 (12:14)
VBA Code Rewrite for Reports
Using the Detail Build Event

11. Review (3:39)

Access Database Security Seminar

00. Introduction (8:00)

01. Database Setup, Login Form (12:23)
Create a New Database
Trusted Locations
Set Overlapping Windows
Setup UserT Table
Access User Level Security
Why ULS is Not Very Secure
"Good Enough" Security
Create Login Form
Popup & Modal Forms

02. Login Form Code (11:42)
Docmd.Quit
Check for blank username/password
DLOOKUP Username and Password
NZ Function

03. Main Menu (9:43)
Exit Database Button
UserID, Username Fields on Main Menu

04. User Groups (14:35)
GroupT Table
Group to User Cross Reference Table
Junction Table
UserF Form
GroupF Subform
Adding Users to Groups

05. Checking Group Permissions (12:35)
Open User Form Button
Code to Check if User in Group
Is the User an Admin
Defining Your Own Function
Public Function
Return a Boolean
Global Modules

06. Service Process (9:06)
Define Service Processes
Create CustomerT Table
CustomerF Form
StatusT Track Work Order Status
ServiceT Detail on Service Call

07. Service Form (10:40)
Build Service Form
Create Combo Boxes

08. Service Listbox (11:23)
Listbox Service for Current Customer
OnCurrent Event
Modify SQL for Listbox to Format
Format Function in SQL

09. Sales Rep Security 1 (10:51)
Security: Who can Browse Customers
Assigning Customers to Sales Reps
Query to Show Only Sales Reps
Sales Rep Combo Box for Customers

10. Sales Rep Security 2 (10:43)
AllowAdditions, AllowDeletions, AllowEdits
Setting Properties in VB Code
Change Properties Based on User Group
Change Properties Based on SalesRepID

11. Locking Fields on Form (8:56)
Locking All Fields on a Form
For Each Control Loop
RGB Color Function

12. Unlocking Needed Fields (7:08)
Unlock Fields for Sales Reps
Unlock Fields for Service Techs

13. Create Service Order 1 (10:08)
Create New Service Order Button
Enable/Disable the Button
Get Default Value from Another Form

14. Create Service Order 2 (14:01)
Requery Service Listbox OnGotFocus
Open a Specific Service Order
Set a Default Sales Rep
Security Opening Service Orders

15. Assign to Service Tech (10:27)
MsgBox vbYesNoCancel
Lock Fields Once Assigned to Tech

16. Unassigned Service List (12:47)
UnassignedServiceQ Query
service List Form

17. Accepting Service Call (7:33)
Accept Service Call Button
Perform Service

18. Unassigned or My Calls (7:58)
Show Unassigned Service Calls
Show My Service Calls Button
Show ALL Service Calls
Dynamic SQL in RowSource of ListBox

19. Service Completed (11:32)
Mark Service Completed
Show Status on Service List
Show New, Open Service Calls

20. Hide Buttons on Main Menu (14:30)
Show/Hide User Form Button
Show/Hide Browse Customers Button
Open Service List Show Different Things
Private v. Public Form Functions

21. Fixing Some Problems (8:01)
Fix Problem with Sales Reps
Fix Missing Button Problem
Force Service Details to be Entered

22. Manager Review (12:29)
Manager Review 1 Button
Show Service Calls to be Reviewed
Assign For Followup Button
Hiding All Buttons on ServiceF

23. Followup, Closing Call (11:38)
Sales Rep Followup Completed Button
Manager Closed Button

24. Sending Email Notices (13:00)
Sending Email using Microsoft Outlook
Docmd.SendObject

25. Admin User Combo (11:51)
Make the UserID a Combo Box
Quick Change to Another User

26. Locking the Database 1 (9:43)
Split the Database
Front End v. Back End Databases
Encrypt Database with Password
Open Exclusive
Linked Table Manager
Get External Data
Create Links to Backend Tables

27. Locking the Database 2 (13:28)
Backup Your Database
Turn off Navigation Pane
Turn off Database Window
Show Startup Form - LoginF
Application Title
Display Navigation Pane
Allow Full Menus
Allow Shortcut Menus
Hold SHIFT Key to Bypass Security
VBA Code to Shut Off Bypass Key
AllowBypassKey
Database CreateProperty
Properties Collection

28. Locking the Database 3 (10:09)
Import Linked Tables Bypasses Security
Create Admin Menu

29. Locking the Database 4 (13:55)
Global Const
Dynamically Link to Tables
Destroy Links to Tables

30. Locking the Database 5 (14:45)
Create a Recordset
OpenDatabase
OpenRecordset
Read User Info Before Linking Tables

31. Locking the Database 6 (17:09)
OnClose Event
Terminate Table Links
The Navigation Pane Shows
The Database Window Shows
Link Tables using TableDefs

32. Creating an ACCDE File (9:11)
Make ACCDE File
Make MDB File
Source Code (Design) is Gone
VB Debug Compile to Find Errors

33. AC2003 User Level Security 1 (10:36)
User Level Security Wizard Part 1

34. AC2003 User Level Security 2 (12:43)
User Level Security Wizard Part 2
Create a Workgroup File
Securing Objects
Selecting Security Groups
Creating User Accounts
Creating an Unencrypted Backup
Logging on with User Permissions
User and Group Permissions
Add or Delete Users
Add or Delete User Permissions

35. Other Database Properties (8:40)
Show All Database Security Properties
VBA Code to Change Properties
StartupShowDBWindow
StartupShowStatusBar
AllowBuiltinToolbars
AllowFullMenus
AllowShortcutMenus
AllowDefaultShortcutMenus
AllowToolBarChanges
AllowBreakIntoCode
AllowSpecialKeys
AllowBypassKey

36. Database Log (17:02)
Track Employees with a Database Log
Create LogT Table
Create LogIt Function
Track User Logons
Track Data Changes
OldValue Property

37. Review (2:59)
Review Topics



MAIN CONCEPTS COVERED

Creating your own security inside the database
- Making a user table
- Logon form
- User groups with permissions

Controlling the "work flow" in your database
- Sales rep enters a work order
- Service tech performs the work
- Manager approves it
- Sales rep follows up
- Control who can enter data into what fields
- Buttons to move to the next step in the process
- Locking access to data once that step is completed
- Sending an Email notice with Outlook for the "next step"
- Creating a system log to watch everything your users do

Determining which users can access what forms, fields, data
- Sales reps can't edit each others customers
- Service techs can't see customer data
- Users can't bypass the "work flow"
- Managers can edit everything
- Admins can supervise and change access rights
- Who can browse customers?
- Who can see service reports?
- Showing or hiding buttons/forms based on access rights
- Preventing record edits, deletions, or additions
- Forcing users to enter required data

Properly Securing Your Database
- Split the database into Front-End and Back-End files
- Encrypt Back-End database with a password
- Turn off Navigation Pane, full menus, toolbars, etc.
- Disable the Bypass Key for startups
- Dynamically link to tables using VBA code on database startup
- Destroy those links when database closes
- Compile your database into an ACCDE (MDE) file
- Access 2003 User Level Security Wizard
- Working with Access 2003 users, groups, and permissions
- Advanced database properties in VBA such as AllowBreakIntoCode

Programming Topics
- Using DLOOKUP to lookup usernames & passwords
- Many-to-many relationships with junction tables (users/groups)
- Locking fields on a form based on user rights
- For Each control loops
- Creating your own global modules with public functions
- Dynamic SQL rowsource for listboxes
- AfterUpdate, OnCurrent, OnGotFocus events
- Use the RGB function to set color in VBA
- Getting a value from a form using Forms!Formname!Field notation
- Create and edit databases in VBA
- Define global constants
- Create and work with RecordSets to read user info
- Use a recordset to read from an external database with a password

Access Data Encryption Seminar

00. Intro (5:02)

01. Lesson 1 (9:06)
Set up the database
Set up customer table with encryption fields
Create customer form for public data
Create customer form for encrypted data

02. Lesson 2 (9:37)
Button to open encrypted form
Encrypt Function
Creating a public function in a module
ASCII values & table
ASC() Function
For Next Loop
Mid() Function

03. Lesson 3 (9:41)
Convert Integers to String Values
CStr() Function
Len() Function
Const Keyword to create Constants
Key Values to Mathematically Obfuscate Data

04. Lesson 4 (9:40)
Convert String Values to Integers
CInt Function
Converting ASCII Values to Characters
CHR() Function
Convert to Currency Values
CCur() Function
MsgBox Command
Global Constants

05. Lesson 5 (7:59)
Decrypt Function
Reversing the Encryption Procedure
For Next Step
Deleting actual data from the table

06. Lesson 6 (7:58)
Save & Close Button
&& to get one &
DoCmd.Close Command
Decrypt the data when the form loads
OnCurrent Event
Hide the Encrypted Fields
Visible Property
Control how users can close the form
Only allow closing form with your button
Form Properties
Navigation Buttons
Record Selectors
Scrollbars
Control Box
Close Button
Max Min Buttons
Pop Up
Modal

Access Split Database Security Seminar

00. Intro (5:01)

01. Why Split Your Database (5:07)

02. Customer Tables (10:53)
CustomerT
CustomerSecureT
One-To-One Relationship
Referential Integrity
Cascade Deletes
Cascade Updates

03. Customer Forms (11:36)
CustomerF
CustomerSecureF
Button to Open Secure Form
Without Security
VBA DoCmd.OpenForm
Add a Password
InputBox
Hide Navigation Pane

04. Splitting the Database (5:57)
Database Tools
Move Data > Access Database
Database Splitter
Copy and Rename Backend Files

05. Set up Shared Folders (14:21)
Windows Server
Sharing
Share This Folder
Share Permissions
Full Control
Users & Groups
File Sharing in Windows 7
Linked Table Manager
Refresh Links

06. Encrypted Front End (8:43)
ACCDE File
Distribution Copy
Save your ACCDB file!

07. Review (6:01)

Access Search Seminar

00. Intro (19:06)

01. Simple Find & Replace (11:32)
Overview of Sample Database
CustomerT
CustomerF
Overlapping Windows
Find & Replace
Access Beginner 4
Find in a Table
Find in a Form
Use Ribbon
Backing Up Tables
Use Bottom Search Box
Find & Replace
Backup Your Data!
Replace All

02. Simple Filter & Sort (11:13)
Filter with column header button
Filtered/Unfiltered button
Apply filter
Blanks
Text Filters
Begins With
Clear Filter
Number and Date Filters
Filter by Selection
Filter Excluding Selection
Contains / Does Not Contain
Filter By Form
AND OR conditions in Filter by Form
Save As Query
Load From Query
Clear Grid
Advanced Filter
Sort Ascending / Descending
Sort by Multiple Fields

03. Parameter Query (13:53)
Simple Query
Benefits of a Query
Find Customers from NY
AND OR conditions
Parameter Query
CustomerListF
Form Design
Form Property Sheet
Record Source Property
Continuous Forms
Form Header/Footer
Change Form Record Source
Use Parameter Query in Form

04. Menus & Buttons (10:45)
Create Main Menu Form
Command Button Wizard
Show All Data
Show Specific Records
Startup Form
Show Customers By State Button
Open Selected Customer

05. Form Fields as Parameters (11:40)
Get Parameter From Form Field
Forms!FormName!FieldName
Search Parameter on Form
Unbound Text Box
Multiple Fields, OR Condition
Multiple Fields, AND Condition

06. Wildcard Searches (9:49)
LIKE Keyword
* and ?
String Concatenation
"*" & Criteria & "*"
Default Values Not Clearing
NULL values missing
Or Is Null

07. Showing Blank Values (19:06)
Null values problematic
Convert Null to Empty String ""
IIF Function
Rename a Query Field, Alias
Show ONLY blank values
Exact or Wildcard Search
Checkboxes
Show Blanks
Search City or State with Blanks

08. Search Combo Box (13:28)
Combo Box Wizard
Find a Record in my Form
Why Find Option Might be Missing
Company Name Search
LastName, FirstName Search
Combo Box Columns
Combo Box Column Widths
Use SQL to manipulate a combo box
Basic SQL Primer
SELECT Statement
ORDER BY for Sorting

09. Embedded Macros (21:03)
Unique list of company names
Group By in a query
Is Not Null
Type Mismatch
What is a Macro
Macro v. VBA Code
Examine Embedded Macros
Open Form Macro
SearchForRecord Macro
Quotes inside of Quotes
Single Quotes
Double Double Quotes
Make a Find Next Button
GoToControl
FindNextRecord

10. Single Field Search Button (11:43)
Wizard Default Search Button
Custom Search Button For 1 Field
Static Search for "XYZ"
User Prompt with InputBox
FindRecord Macro
SearchForRecord Macro
Full WHERE Condition SQL

11. Search Button with VBA (14:25)
Make Backup Copy of CustomerF
Creating VBA Code
Focus
DoCmd.GoToControl
DoCmd.FindRecord
Dim Variable
InputBox
Reset / Stop Code
If Then

12. More Find Buttons (13:05)
Company Name Search
Copy Paste Code
Make Your Own Subroutine
Sub Parameters
Comments
Finding the Next Record
Determine Previous Control
Screen.PreviousControl
Docmd.FindNext

13. Filters with VBA (23:24)
Simple Form Filter
DoCmd.ApplyFilter
If Then Else
DoCmd.ShowAllRecords
Concatenate Many Search Fields
And/Or Combo
Clear Filter Button

14. SQL RecordSource Form (14:40)
Build SQL Statement from Scratch
SELECT FROM
WHERE Clause
Me.RecordSource
Add a Sort Order
ORDER BY Clause
Order By Combo Box
Allow Deletions
Allow Additions
Allow Edits
Allow Filters

15. Dealing with NULL Values (10:52)
Adding IsNull to SQL
Adding NOT IsNull to SQL
Triple State Checkbox
Null Values Multiple Fields

16. Descending Sort Toggles (25:52)
Toggle Button
Button Quick Styles
Background Color
Hover Color
Pressed Color
Toggle.Caption Property
AfterUpdate Event for Toggle Button
RequeryForm Sub
Requery When ANY Value Changed
Sort DESC decending
Run Event when Form Opens
OnOpen Event
Add 2nd Field to Sort By
Definition Jump To
Add a No Sort Option

17. Simple Search Form (22:16)
Easier Form for New Users
Make sure user entered at least 1 item
Message if customer not found
Add new customer with one click
vbYesNoCancel MsgBox
vbYes
With Forms!CustomerF

18. Show Advanced Options (18:07)
Clear button
Tab Order
Determine if an email address
InStr Function
ENTER and ESC buttons
Button Default and Cancel Properties
Get form width property
Resize Form with Button
Me.Width
Twips
DoCmd.MoveSize

19. Recent Customer List 1 (17:43)
Listbox for Recent Customers
CustomerSearchT
On Error Resume Next
Simple Debugging
INSERT INTO SQL statement
Docmd.SetWarnings

20. Recent Customer List 2 (18:03)
Open Selected Customer
Listbox.Column()
OnDblClick Event
On Double Click
See if Customer Exists in Table
DLOOKUP
Look up a value in a table
NZ convert Null to Zero
Delete Query SQL
No Customer Selected on Delete

21. Recent Customer List 3 (17:33)
Change Width of Listbox
No ScrollBars Property for Listboxes
Listbox.ColumnWidths Property
Listbox.ColumnCount Property
Hide the Horizontal Scrollbar
Adjust Col Width in VBA
Format a Date with Format Function
UPDATE SET SQL
Increment In SQL

22. Recent Customer List 4 (25:08)
Sort Labels
Events and Default Values Conflicting
Reload Customer Search List
Order By Labels For Each Column
Default Sort Field
Beware Layout Mode Saving Design
Ascending / Descending Sort

23. Multi Table Search Form 1 (17:30)
Employee and Vendor Tables
Search Results to List Box
Split FirstName LastName to 2 fields
Left, Right, Len Functions

24. Multi Table Search Form 2 (18:41)
Set RowSource of CustomerList on Search
OnDblClick to Open Customer Form
Add EmployeeList, VendorList
Change SQL Statement Based on Which List

25. Multi Table Search Form 3 (24:00)
Create a UNION Query
Merge 3 Tables into 1 Query
Pull All Records from SearchUnionQ
Show Type of Record in Listbox
Select Case Statement
Open Whichever Form is Needed
Select Which Tables to Search In

26. Product Search Form 1 (17:31)
Create Product Table
ProductQ
Outer Join to Show All Products
Vendor Combo Box
Align to Grid
Size to Grid
Empty vs. Null - What to Look For
How to Test Empty or Null

27. Product Search Form 2 (12:02)
SQL for Product Search RowSource
Search Range of Unit Costs
Search Range of Dates Updated
Search Notes Field
Double Click to Open Product

28. Product Search Form 3 (10:50)
Domain Aggregate Functions
DLOOKUP Product Notes
Count of Records Returned
DCOUNT Function
Max and Min Unit Costs
DMAX and DMIN Functions

29. Product Search Form 4 (11:54)
Product purchased by how many customers
Aggregate Query Group By Count
List of customers who purchased product
Docmd.OpenQuery

30. Natural Language Search 1 (14:49)
CourseT Table
CourseF Form
CourseSearchF Form
Search Multiple Fields from One Lookup

31. Natural Language Search 2 (14:46)
Break up string into keywords
OR condition between keywords
Replace Function

32. Natural Language Search 3 (22:41)
Phrase search
Remove quotation marks
Highlight the search phrase in the form
SetFocus
SelStart
SelLength
Negative keywords
Exact match search

33. Search As You Type (18:36)
KeyDown, KeyUp, KeyPress Events
ASCII Character Codes
CHAR, ASC functions
On Change Event

34. Printing Reports (5:08)
DoCmd.OpenReport
Where Condition
Report Events

35. Review (3:21)

Access Work Order Seminar

0. Introduction (10:49)

1. Plan Database (11:33)
Tables
Relationships
Reports

2. Customer Tables (11:58)
CustomerT
LocationT
UnitT

3. Customer Form (11:20)
Make Customer Form
Locations List Box
SQL RowSource
Custom WHERE Condition
Forms!CustomerF!CustomerID
OnCurrent Event
LocationList.Requery

4. Location Form 1 (12:27)
Build Location Form
Customer Combo Box
Open Location From CustomerF
DoCmd.OpenForm Code
Where Condition
OnDblClick Event

5. Location Form 2 (9:03)
Add New Location
Open Form to Add Records
Form Field Default Value
Tab Stop Property
Requery Across Forms
On Error Resume Next

6. Unit Form 1 (12:47)
Build Unit Form

7. Unit Form 2 (9:19)
Limit List of Locations
Change RowSource in VBA
Notes

8. Using Subforms (10:23)
Creating Location SubForm
Creating Unit SubForm
Continuous Forms
Form Header/Footer

9. Customer List (11:00)
Build Customer List Form
Open Customer Button
New Customer Button

10. Work Order Form 1 (7:33)
Build Work Order Table
Design Work Order Form

11. Work Order Form 2 (11:14)
Dynamic Combo Boxes
Locations by Customer
Units by Location
Enabled Property
DropDown Method
SetFocus Method

12. Work Order Form 3 (11:58)
Blanking Combo Boxes
OnCurrent Event
Enable/Disable if Needed
Requery Combo Boxes Again

13. Work Order Form 4 (9:22)
DLOOKUP Name & Address Info
Dim Variable

14. Work Order Form 5 (12:17)
NZ Function
Dealing with NULL problems
Double-click to Open Forms
DoCmd.OpenForm

15. Status, Priority (12:52)
Status Table
Sorted Status Query
Priority Table
Sorted Priority Query
Status, Priority Combo Boxes
Default Combo Values
Default Requested Date

16. Create Work Order (12:57)
Button on Customer Form
Button on Location Form
Button on Unit Form

17. Work Order Query (6:35)
Master Work Order Query
Relationships
Outer Joins

18. Work Order List 1 (18:57)
List of Work Orders
Listbox on Customer Form
Listbox on Location Form
Listbox on Unit Form
Limit to Current Record
Double-click Open Event
OnCurrent Event Updated

19. Work Order List 2 (10:26)
Requery Work Order List
On Error Resume Next
Triple State Check Box
Show Closed Work Orders
Show Open Work Orders
Show Both Closed and Open

20. Work Order List Form (19:48)
Master Work Order List
Show Closed Work Orders
Change Sort by Column Headers
Main Menu Form

21. Labor Form 1 (9:01)
Form to Track Labor Items
Continuous Forms

22. Labor Form 2 (8:53)
Calculate Billable Hours
Rounding Time Values
SUM Total
Page Footer v. Form Footer
DateDiff Function

23. Labor Form 3 (9:41)
Recalculate Hours
Combo Box for Worker

24. Labor Form 4 (14:06)
Modal, Popup Form
Button to Open Labor Form
Calculate Hours on Work Order

25. Labor Form 5 (14:29)
Combo Box Default Worker
Minimum Billable Hours
End Time Earlier than Start Time

26. Materials Form 1 (9:39)
Table, Form for Materials
Calculate Line Totals

27. Materials Form 2 (9:04)
Product Combo Box
Add Product to Materials Form
Column() Values
DLOOKUP

28. Materials Form 3 (8:36)
Calculated Footer Values
Values on Work Order Form
Grey Out Calculated Values

29. Categories (11:42)
CategoryT
Category Junction Table
Many to Many Relationship
Category Subform on Work Order

30. Scheduling 1 (14:37)
Delete Scheduled Date Field
Change to IsScheduled
Schedule Table and Form

31. Scheduling 2 (12:27)
Filter Boxes
Dynamic Form Requery
SQL Statements for Recordsource

32. Scheduling 3 (11:00)
Filter Boxes for Dates

33. Scheduling 4 (16:05)
Schedule Button for Work Order
Mark as Scheduled
MsgBox vbYesNoCancel
Appt Conflict Resolution, Part 1

34. Scheduling 5 (16:37)
Appt Conflict Resolution, Part 2
Upcoming Appointment Preview List

35. Scheduling 6 (19:44)
Next Available Appt Time
Loop to Get Next Appt Time/Date

36. Fix Work Order List (12:32)
DateScheduled Now Missing
Add Show Scheduled Box
Link to Show Open, Unscheduled

37. Inactive Units (9:36)
IsActive Field
Show Active / Inactive Units

38. Search Form 1 (16:04)
Company Name Search
Location Name Search
Person Name Search Part 1
Union Query

39. Search Form 2 (11:51)
Search for Name Anywhere in DB
Custom VBA and SQL Code for Listbox

40. Search Form 3 (9:15)
Open Corresponding Form
Location, Customer, or Unit

41. Search Form 4 (7:06)
Add Phone Number Field to Search

42. Billing 1 (16:15)
MyCompanyT
Default Hourly Rate for All
Default Hourly Rate Per Customer
Add Billable, Invoiced to WO List
Show Completed, Not Invoiced Yet

43. Billing 2 (10:47)
Invoice Table
Invoice Detail Table
Billing Decisions

44. Billing 3 (13:49)
Make Invoice Button
Check to see if Billable
Check for labor or materials
Create InvoiceT record
Recordsets
Create a Recordset
AddNew
Set Field Values

45. Billing 4 (14:29)
Add Line Items to Invoice
Recordset to Loop through Materials
Add Materials to Invoice

46. Billing 5 (8:46)
Add One Line Item for Labor

47. Billing 6 (12:12)
Invoice Detail Form
Invoice Form
Show Invoice Button
Change Caption Dynamically
OnCurrent Event

48. Sales Tax 1 (13:30)
DefaultTaxRate
Adding TaxRate to Invoice
Add IsTaxable to Materials
Taxable Labor

49. Sales Tax 2 (7:27)
Add SalesTax to VBA Invoicing Code

50. Sales Tax 3 (14:57)
TaxableTotal
NonTaxableTotal
IIF Function
Form Footer Calculations
Order Total

51. Order List Form (18:46)
Order List Query
Aggregate Query
Show Totals
Group By
Sum Function

52. Update Status (12:16)
Update the Status Combo
On Error Resume Next
On Error Goto 0

53. Work Order Report 1 (16:53)
Work Order Report Query
Outer v Inner Joins
Work Order Report
Work Order Category Subreport

54. Work Order Report 2 (15:51)
Labor Subreport

55. Work Order Report 3 (25:26)
Materials Subreport
Button to Print 1 Work Order
Limit Work Order List by Dates
Print Batch of Work Orders

56. Printable Invoice (21:49)
Master Invoice Query
Report Grouping
Force New Page
Sum Totals
Printing Invoices

57. Printable Schedules (20:17)
Schedule for All Workers
Schedule for One Worker
Schedules Between Two Dates

58. Review (3:25)

Access Imaging Seminar

0. Welcome (14:40)

1. Intro to Objects (14:04)
OLE Objects v. Images
Bound v. Unbound OLE Objects
Linking v. Embedding
When and when not to store objects
Access 2007 Attachments

2. Database Setup (2:41)
Creating the Database
Customizing Options
Trusted Locations

3. Table Form Setup (5:41)
Setup Employee Table
Build Employee Form

4. Bound Embedded OLE Objects (10:02)
Insert Object
Icons v. Full Images
Edit or Display Object
Changing Registered Program Type
Zoom, Clip, Stretch

5. Linked OLE Objects (9:42)
Linking to Objects
File Folder Considerations
Shared Network Folders
Editing Linked Copies

6. Non Image Objects (6:55)
Excel Spreadsheet Fragments
Links to Word and Excel Files
Employee Report

7. Unbound OLE Objects 1 (5:05)
Shared Linked Excel Sheet
Editable Sheet Stored in Form

8. Unbound OLE Objects 2 (8:56)
Main Menu
Shared Logo on Forms Reports
Editable Logo in Multiple Places

9. Image Control 1 (11:18)
Inserting an Image Control
Manipulating the Picture Property
Loading an Image from your Drive
Change Image Button
Loading an Image when the Form Loads
Empty Image Control Box

10. Image Control 2 (12:00)
Show Image on Bound Data Form
Change Image for Each Record in VBA
Load Picture from Server or C: drive
Creating Your Own Subroutine in VB

11. Common Dialog Control (9:52)
Up to Access 2003 Users Only
Use CD Control to Pick a File
Save Filename in Field
Does Not Work in Access 2007

12. Common Dialog DLL (9:08)
All Versions of Access
Use DLL Call to Pick a File
Works With ANY Version of Access
Location of Sample Database Files

13. Copy Files to Server 1 (8:22)
Create a SettingsT Table
DLOOKUP Folder Path
Change Display Code
Null vs. Empty

14. Copy Files to Server 2 (11:25)
Separate Filename from Path
GetFilenameOnly Function

15. Copy Files to Server 3 (12:34)
Determine if File needs copying
FileCopy command

16. Copy Files to Server 4 (9:17)
Ask to Copy if File Already Exists
Dir Function
Make EmployeeID part of Filename

17. Handling Errors (9:13)
Cancel on Select File
Picking a Non-Image File Type
Image Files Moved or Renamed

18. Editing Images (6:27)
Creating Your Own Edit Event
Shell Command

19. Embedding OLE with VBA (4:21)
Pick a File
Embed the File in OLE with VBA code

20. Display Images from Web (10:53)
WebBrowser Control
Determining an Images URL
Navigate to a Web URL
LoadPicture Subroutine

21. Attachments (10:57)
Added in Access 2007
Attachments in Table Fields
Pros and Cons
Attachment in Form
VBA to Work with Attachments
Count Attachments
Cycle Through them on Form

22. Image Report (5:22)
Create a Report with Image Control

23. Review (2:55)

Access Payables Seminar

00. Intro (3:58)
What's Covered
Pre-Requisites
Versions Used
Student Forums

01. Payee & Category Tables (11:41)
Create new database
Place in trusted folder
Overlapping windows setting
Application title
Collapsed Ribbon
Create the Payee Table
Create the Category Table

02. Payee Form & List (12:55)
Create the Payee Form
Combo box for category
Create the Payee List Form
On Double Click event to open Payee

03. Bill Table & Form (11:42)
Create the Main Menu Form
Make Main Menu the startup form
Create the Bill Table and Form
Conditional Formatting for overdue Bills

04. Bill Data Entry (9:56)
Form for quick data entry of new bills
Alternate row color
Tab Order
Data Entry property

05. Payments & Balances (10:44)
Create the Payment Table
Aggregate Query for Payments by Bill
Bills with Payments Query
Calculate a Balance for each Bill

06. Payments on Bill Form (11:08)
Put balance info on forms
CCur function
Payment listbox on Bill form
Requery listbox in OnCurrent event

07. Bills on Payee Form (9:37)
Listbox to show Bills for Payee
Dynamic SQL statement
Labels for listbox columns

08. Sort Bills on Payee Form (13:02)
Sort by columns
Click on label to sort
RequeryBillList private sub

09. Show Open Bills for Payee (7:51)
Show All Bills checkbox
WhereStr variable in SQL
DblClick event to open bill form

10. Add New Bill (13:41)
Add new bill button on Payee form
BillF made non-updateable
DLOOKUP
IsNull
IIF
Check to see if a form is loaded
OnClose form event

11. Balances on Payee List (12:31)
DSUM function
NZ function null to zero
CCUR function convert to currency
Add balances to Payee List

12. Last Bill & Payment (11:07)
Last Bill Date
Last Payment Date
DMAX function

13. Payee List Sorting (16:45)
Sort functionality on Payee list
Recolor label to show sort column
Me.Controls collection
RGB color function

14. Revise Bill List Form (9:31)
Deleting embedded macros
GoToRecord
GoToControl
Button to open selected Bill

15. Bill Summary Form 1 (10:38)
Create bill summary listbox
Add header labels

16. Bill Summary Form 2 (13:27)
Add sorting capability
Requery the listbox
Sub to recolor labels
Double-click to open bill
Button to open payee
Listbox Column property

17. Bill Summary Form 3 (11:54)
See all, paid, or unpaid Bills
Combo box with set values
WHERE condition in SQL
OnOpen event to set initial listbox

18. Bill Summary Form 4 (12:09)
Add combo box for due date filter
What's past due? Past due 30 days?
What's coming up in the next week, month?

19. Bill Summary Form 5 (8:12)
Total for Balance column
DSUM with existing SQL where criteria

20. Payment Form 1 (8:40)
Create Payment Form
Get default values from previous form

21. Payment Form 2 (8:16)
InputBox to prompt for payment amount

22. Payment Form 3 (10:12)
Refresh all forms on updated balance
Hide payment button if balance is zero

23. Better Add Bill Method (13:29)
Add Payee Button
Refresh Payee List Form
Adding a Bill without an Amount
MsgBox prompt
Before Update event
Cancel Update event

24. Misc Updates (11:17)
Updates to Main Menu
Update color labels on open
Button to add bill on Bill Summary form
More refreshes when form closes
Refresh on change of Amount Due

25. Overpay Bills (11:29)
How to overpay a bill
MsgBox with vbYesNoCancel
Show total balance on Payee form

26. Quick Pay Bills 1 (11:36)
Processed field in Payment Table
Unprocessed Payments List Form

27. Quick Pay Bills 2 (14:04)
VBA code to quick add payments
Open the Payments List Form
Go to new record
Set amounts
Refresh listbox

28. Payment Methods 1 (11:18)
Payment Type Table
Check, Cash, BillPay, Credit Card
Payment Type Combo for Payees
Payment Type Combo on Payment Forms

29. Payment Methods 2 (14:18)
DLOOKUP Preferred Payment Type
Prevent user from adding bogus payment
After Delete Confirm event

30. QuickPay Next Item (7:36)
Set BillList to Null
ListCount property
Use Column property to pick a record
Row parameter

31. Process Payments 1 (11:53)
Add Processed to Payment Form
Edit PaymentWithPayeeNameQ Query
Add Payment Type Filter
Add Processed Filter
RequeryPaymentForm sub
Clear Filters label

32. Process Payments 2 (13:35)
Processed Filter Checkbox
Changing Form Name in All VB Code
Find and Replace in whole project
Add PaymentType to Query
Design Payment Form
Can Grow / Can Shrink

33. Process Payments 3 (14:04)
Sorting & Grouping
Group by Payee
Group Footer Total
Limit records based on Payment List Form
SQL Me.Recordsource for Report
Read filters from Payment List Form
DoCmd.OpenReport
On No Data
On Error Resume Next

34. Process Payments 4 (11:27)
Create a Check Report
Layout fields for printed check
Convert Currency to English
Five Hundred Ten Dollars and No Cents

35. Process Payments 5 (9:47)
Print Account Number on Checks
Mark Payments Processed
While Loop to Mark Processed
ProcessedDate

36. Recurring Bills 1 (12:55)
Recurring Bills Table
Frequency Table
Calculate next due date by frequency
NextDueDate public function
PayablesModule
Select Case statement
DateAdd function

37. Recurring Bills 2 (15:07)
Using NextDueDate in a Query
NZ for unprocessed (null) bill dates
Recurring Bills Form

38. Recurring Bills 3 (9:20)
Target Date
Loop to process recurring bills

39. Recurring Bills 4 (9:49)
Adding recurring bill to BillF form
Testing the loop
With VB command

40. Next Recurring Notice (8:29)
Show next recurring bill date on Main Menu
Modify color if past due
Popup warning message

41. Reports 1 (10:34)
Aged Accounts Payable
IIF to determine current v past due

42. Reports 2 (16:22)
Aggregate Query For Accounts Payable
Due in 30 days
Current Bills
Past Due 30, 60 days
Accounts Payable Report
Page Numbering
Report Footer

43. Reports 3 (10:21)
Bills Between Dates Query
Start End Date on Main Menu Form
DateSerial Function
DateAdd Function
+1/-1 Month Labels

44. Reports 4 (7:46)
Form with Embedded Chart
Pie Chart Summary of Expenses by Category
Format Chart Objects

45. Referential Integrity (8:20)
Setting up Global Relationship
One to Many
Enforce Referential Integrity
Cascade Deletes
Cascade Updates
Data Violating Rules Already
Find Bad Data

46. Payment Terms 1 (8:36)
Explanation of NET terms with discounts
2%10 NET 30
Setup TermsT Table
Add TermsID to Payee Table, Form

47. Payment Terms 2 (14:49)
Remove default dates from BillT
PayeeTermsNumDays Function
Set Bill, Due dates in Amount AfterUpdate
UpdateDueDate Sub
Update Due Date if Bill Date changed
Update same code in BillF
Update Recurring Form Loop
DueDate = NextDue + PayeeTermsNumDays(PayeeCombo)

48. Payment Terms 3 (17:29)
When payment is made, check for discount
Deduct discount from bill amount
Add DiscountTaken field to BillT, BillF
Code in BillF to take a discount

49. Payment Terms 4 (11:59)
Code in Quick Pay to take a discount
Convert listbox text to currency with CCUR

50. Qualified For Discount (6:34)
Query to show bills you can pay for discount
IIF Function to determine if qualified

51. Payee Priority (12:44)
Create Priority table
Add Priority to Payee Table
Adjust Bill Query
Add Priority column to Bill Summary Form

52. Repeat Until Date (11:45)
Recurring Bills Limited Repeat
Fix #ERROR on RecurringF
Add RepeatUntil date to table, form
Set default frequency

53. Bug Fixes 1 (7:35)
Set processed date on Payments form
Sort payments by Payee name
Add Payments to Selected Vendor to footer

54. Bug Fixes 2 (8:39)
Show only Active Payees
Warning if payee has a balance on inactive
Checkbox on payee form to filter payees

55. Bug Fixes 3 (9:54)
Payee Notes were Text not Memo
New Line in Field for Memo
BillSummaryF Due in 7 days
Fix Next Recurring on Main Menu
Add "there are payments to process" warning

56. Review (3:56)

57. Lesson Summary (14:34)

58. Database Walk-Thru (14:25)
 

Access SQL Seminar 1

00. Introduction (9:03)

01. What is SQL (6:09)
What is SQL
Structured Query Language
How to see SQL in queries
Why learn SQL

02. Basic SQL Components (14:27)
Build Customer Table
Build a simple query using QBE Grid
SQL View
SELECT statement
FROM clause
ORDER BY clause
WHERE condition
Changing fields in SELECT statement
Showing all fields with *
Clauses on separate lines
Semicolon
No spaces in field names

03. WHERE Clauses 1 (16:00)
Operators: = > < <= >= <>
String values and quotes
TRUE, FALSE, Yes, No, -1, 0
Dates in # signs
BETWEEN with dates, text, numbers
AND, OR with multiple fields
IS NULL
IS NOT NULL

04. WHERE Clauses 2 (13:23)
Parameter Queries
Parameters with BETWEEN
LIKE keyword
Wildcard characters: *, ?, #
Different wildcards: % and !
Range of Characters [A-P]
Not in a range [!A-P]
IN clause

05. ORDER BY and DISTINCT (6:49)
ORDER BY
Multiple fields
ASC, DESC
DISTINCT
DISTINCTROW

06. Customer List Form (15:22)
Build an Unbound Form
Add a Customer List Box
RowSource Property
Change SQL Dynamically
Command Buttons with VBA Code
Docmd.OpenForm

07. Name List Form (11:23)
Form that gets data from 2 tables
Show Customers
Show Employees
Show BOTH
UNION Query
Me.RecordSource
Me.Caption property

08. Review (5:13)

Access SQL Seminar 2

00. Intro (8:32)

01. Update Query 1 (14:47)
Four types of action queries
Update query
Create Product Table
Update query in Access QBE designer
Update with no parameter
SQL View
UPDATE SET
WHERE
Updating multiple fields

02. Update Query 2 (10:27)
Update Query with Multiple Tables
INNER JOIN ON clause
Update vendor product pricing

03. Update Query Form 1 (19:21)
Markup Text Box
Category Combo
SQL Rowsource for Listbox
Custom WhereStr

04. Update Query Form 2 (14:20)
Default markups for each category
Column() combo box property
Docmd.RunSQL
Run update query to perform markup
Format function

05. Append Query 1 (19:21)
Create a Log Table
Append Query with no input table
INSERT INTO SELECT
INSERT INTO VALUES
NULL
VBA Optional for sub parameters
DateTime reserved keyword
Numeric values

06. Append Query 2 (12:48)
Create ProductArchiveT
Archive historic product info
INSERT INTO SELECT FROM
WHERE Condition

07. Make Table Query (6:19)
SELECT INTO FROM
Creating time-based table backups

08. Delete Query (7:04)
Add IsActive to Listbox
DELETE FROM WHERE

09. Top X Items 1 (13:35)
SELECT TOP
SELECT TOP PERCENT
Top X Records Form

10. Top X Items 2 (15:42)
Custom TopStr
InStr function
Replace function
Ascending Descending Combo
Open Report with Custom SQL
Forms!FormName!Field.Property
.RowSource
.RecordSource

11. Aliases (9:57)
SELECT AS
Cartesian Product
Alias field names
Alias table names
AS optional with table names

12. Joins 1 (10:24)
Types of Joins
Inner Join
Equi-Join
Left, Right Outer Join
Full Join
Cartesian Join
Self Join
INNER join two tables
Joining multiple tables

13. Joins 2 (11:33)
LEFT JOIN
Left Outer Join
Right Outer Join
Full Joins not support in Access
SELF Join
Employees and Supervisors
Alias in the Access QBE grid
Cartesian Example
Softball Team Schedule

14. More with IN (4:08)
IN with SELECT statement
NOT IN

15 Union Query (11:10)
UNION
UNION ALL
Simulating a Full Join Query

16. Crosstab Query (9:01)
TRANSFORM
PIVOT
CrossTab like a PivotTable
Orders by State by Month

17. Calculations (5:13)
Operations
Addition, Subtraction
Multiplication, Division
Exponentiation
Integer Division
Modulus

18. Concatenation (5:45)
Ampersand
Plus with NULL values

19. Aggregate Queries 1 (13:17)
Sum
Avg
Count(Field)
Count(*)
Max
Min
First
Last
Total Row
GROUP BY
Month function
Criteria on Group By Field
Criteria on Aggregate Field
HAVING keyword
Nested Query

20. Aggregate Queries 2 (11:14)
Where option
Expression option
SQL statement as WHERE condition
Show all below average orders

21. Functions 1 (9:17)
String Functions
LTrim
RTrim
Trim
Left
Right
Mid
Len
InStr
UCase
LCase
StrConv for proper case

22. Functions 2 (15:16)
Date & Time Functions
Format function
Format parameter values
Date
Time
Now
Month
Day
Year
Hour
Minute
Second
DatePart
TimePart
DateSerial
TimeSerial
DateAdd
DateDiff

23. Functions 3 (11:36)
Misc Functions
ABS
INT
FIX
ROUND
SQR
IsNull
Nz
IIF
CSTR
CINT
CLNG
CDBL
CCUR
CDATE
CDEC

24. Cascading Combo 1 (12:51)
Country > State > City
SQL RowSource
AfterUpdate

25. Cascading Combo 2 (10:39)
Reverse the order
City looks up state
State looks up country
Boxes requeried
DLOOKUP

26. Review (4:18)

Access SQL Seminar 3

00. Intro (5:02)

01. Creating a Table (14:32)
Importance of SQL table design
CREATE TABLE command
TEXT field
Length of a TEXT field
Multiple Fields
Make a text box to hold our SQL
Bind SQL form to table
DROP TABLE

02. Access SQL Data Types (8:30)
Access to SQL Aliases
TEXT
MEMO
BYTE
SMALLINT
INTEGER
SINGLE
DOUBLE
CURRENCY
COUNTER
DATETIME
YESNO
OLEOBJECT
Hyperlink
Attachment
Lookup
Start COUNTER at different value
Inflate AutoNumber value
SQL view in query designer

03. Indexing (14:32)
NULL
NOT NULL
PRIMARY KEY
UNIQUE
CREATE INDEX
CREATE UNIQUE INDEX
DROP INDEX
WITH DISALLOW NULL
WITH IGNORE NULL
WITH PRIMARY
ASC, DESC

04. Altering Tables (16:12)
ALTER TABLE
ADD COLUMN
ALTER COLUMN
DROP COLUMN
ALTER COLUMN NOT NULL
Altering tables in EXTERNAL databases
Cannot alter LINKED tables
Dim DB as database
OpenDatabase command
DB.Execute
Adding tables to external database
CREATE link to external table

05. Constraints (10:49)
CONSTRAINT
PRIMARY KEY
DROP CONSTRAINT
ADD CONSTRAINT
UNIQUE
Multi Field Index
Index spanning 2 or more fields

06. Relationships (9:37)
Relationships review
CONSTRAINT FOREIGN KEY
Relationships Window

07. ANSI-92 SQL (8:32)
Differences between 89 and 92
Wildcard characters
Switch database from 89 to 92
Broken queries
LIKE to ALIKE

08. CHECK & DEFAULT (11:24)
CHECK
CONSTRAINT CHECK
Multi Field CHECK Constraint
Table-Level Validation Rules
DEFAULT value
ALTER SET DEFAULT
ALTER DROP DEFAULT

09. Referential Integrity (6:16)
ON UPDATE CASCADE
ON DELETE CASCADE

10. VIEWS & PROCEDURES (11:08)
CREATE VIEW
Views vs. Queries
DROP VIEW
Parameters
CREATE PROCEDURE
Action Queries
Append, Delete, Update
EXECUTE procedure
DROP procedure

11. Database Security (11:44)
ALTER DATABASE PASSWORD
Open Exclusive Mode
GRANT, REVOKE
USER, GROUP
CREATE, ALTER, DROP
Privileges
SELECT, DELETE, INSERT, UPDATE
DROP, CREATE
SELECTSCHEMA, SCHEMA
UPDATEOWNER, CONNECT

12. Review (5:01)

Access Relationship Seminar

00. Intro (7:59)

01. Types of Relationships (13:04)
No Relationships
One-To-Many
One-To-One
Many-To-Many
Self-Join One-To-Many
Self-Join Many-To-Many
Reverse Relationships
Multiple Relationships

02. No Relationships (5:26)
Flat File
The wrong way to build a database
Child and Parents in Same Table

03. One-To-Many Relationships (10:45)
Delete parent data from student table
Create a RelativeTypeT
Create ParentT
Fill in sample data

04. One-To-Many Form 1 (12:38)
Create ParentF Subform
Set up relationship with subform wizard
Turn off navigation buttons
Turn off scroll bars

05. One-To-Many Form 2 (9:53)
Hide IDs
Combo Box for RelativeType
Combo Box Wizard

06. One-To-Many Query (15:34)
StudentQ for Mailings
Query Criteria
Field Name Aliases
OR Conditions
Show ALL students and related parents
LEFT Inner Join
Global Relationships
Cascade Delete

07. One-To-One Relationships (18:33)
Student
Extra Student Info
One-To-One Global Relationship
Command Button to Open 2nd Form
Show Specific Records
Forms!FormName!FieldName
Refer to Value on Another Form
Default Value
One-To-One Subform
Discuss Securing Data with Split DB
Discuss Multiple Back-End Files
One for Users, One for Admins

08. Many-To-Many 1 (11:47)
Products and Vendors
Multiple products per vendor
Multiple vendors per product
Junction table
Cross Reference Table
Vendor Form
Product Form

09. Many-To-Many 2 (14:24)
Products with Vendor Subform
Vendors with Product Subform
ProductVendorF
Product Combo Box
Vendor Combo Box
On Double Click Event
VBA Code to open a form
OnDblClick
Docmd.OpenForm
Where Criteria

10. Self Join One-To-Many (17:00)
Relating a Table to Itself
EmployeeT
SupervisorID
Supervisor Combo Box
String Concatenation
Calculated Field
Employee With Supervisor Q
Grouped Report
Sorting & Grouping Level
Report Showing Supervisor with Employees

11. Self Join Many-To-Many (20:46)
People to People with Relationships
Person table and form
Junction Table
PersonQ with Full Name
Form Subform for People and Relatives
Great for Genealogy Tracking

12. Reverse Relationships 1 (14:23)
Add opposite relationships
If Mother is picked, Child is default
Non-gender specific opposites
AllowAdditions
Do not allow additions to subform
Unbound forms
Control Source
Unbound Combo Boxes
Turn off Navigation Buttons
Record Selectors
Scroll Bars
Modal
Popup
Docmd.Openform
Set value across forms
Forms!FormName!FieldName

13. Reverse Relationships 2 (22:44)
AfterUpdate Event
DLOOKUP Function to find Opposite
SQL
INSERT INTO
Append Query
IsNull
MsgBox
Exit Sub
Docmd.Close
Refresh
DoCmd.SetWarnings

14. Multiple Relationships 1 (14:58)
Main Organization Table
Members Subtable
Great for Charities, Churches
Fund Raising possibilities
Good to track Family Data
Multiple subforms of data
One master form
OrgT for organization
Junction Table
Member Subform

15. Multiple Relationships 2 (21:56)
Address subform on Org Form
Address is for Org OR Member
Address subform on Member Form
OnDblClick to jump from Org to Member
Vice Versa
Contact History Subform
Contacts for both Orgs and Members

16. Review (5:43)
 

Access Fiscal Year Seminar

00. Intro (1:59)

01. Simple Fiscal Year Query (9:17)
Create Order Table
Simple Fiscal Year starting 6/1
YEAR function
MONTH function
IIF function

02. Sales Totals by Fiscal Year (5:47)
Aggregate Query
SUM of Sales by Fiscal Year
Order Totals
Order Report
Group and Sort
Group, Sort, and Total

03. Fiscal Not 1st of Month (10:19)
Create your own Function
Public Function MyFiscalYear
Function Parameters
Function Return Value
IF THEN statement
DAY function
Nested IF statements

04. Multiple Companies (9:50)
Variable Fiscal Start Month Day
Additional VBA Parameters
Tables Joined in Query
Orders By Company By Fiscal Year

05. Review (2:17)

Access Email Seminar

00. Intro (19:51)

01. Sending Email Manually (16:35)
Create Customer Database
Customer Table
Customer Balance Report
Email Button
Send Object As Dialog
Brief Discussion of Output Formats
Export as PDF
Send and Receive in Outlook
Command Button Wizard
Build Event
Embedded Macros
OpenReport Macro Command
Where Condition
CustomerID=Forms!CustomerF!CustomerID
VBA Code Editor
Docmd.OpenReport acViewPreview

02. EmailDatabaseObject SendObject (17:49)
Mail Report in Command Button Wizard
EmailDatabaseObject Macro Command
Specify Output Format PDF
Send to Static Email Address
Send to Current Customer
To, Cc, Bcc Fields
Subject Line
Message Text
Edit Message
Unsafe Actions
Access Security Warnings
Access VBA SendObject Command
Adding FirstName to Custom Message Text
Limitations of SendObject Method

03. Email Merge to Word (16:14)
Pros and Cons
External Data, Word Merge
Send Company Newsletter
Microsoft Word Mail Merge Wizard
Use Current Document
Use an Existing List
Edit Recipient List
Edit Document - Compose Email
Add Formatted Text
Insert Hyperlinks
Insert Pictures
Layout Email with Tables
Complete the Merge
Merge to E-mail
Embedded Images

04. Automating Outlook Email 1 (22:54)
Create Send Email Form
VBA Primer with MsgBox
Adding a Reference to Outlook
Microsoft Outlook 15.0 Object Library
Outlook.Application
Outlook.MailItem
CreateObject, CreateItem
To, Subject, Body Properties
Displaying the Message
Sending the Message Immediately
Outlook Security Warning
Send to Multiple TO Recipients
Add a CC and BCC
Stop Code Execution
Invalid Use of Null
Checking for Required Values
If Not IsNull

05. Automating Outlook Email 2 (27:09)
Sending Formatted Emails
Plain Text vs Rich Text
HTML Codes in Text
Using Word for Exporting HTML
Web Page HTM HTML
HTML Filtered
Putting Images Files to the Web
Upload to Google Drive
Change URL in HTML for Image
Switch from Plain to Rich Text
Send to Outlook

06. Automating Outlook Email 3 (15:44)
Create Separate Email Form for HTML
Send Email Button on Customer Form
OpenForm Command
Set Forms!EmailF!MsgTo to EmailAddress
Send Balance Report via Automation
Creating Your Own Subroutine
Switching Plain v Rich Text in VBA
Enter Key Behavior for New Lines

07. Attachments in Outlook Email (26:21)
Sending a Static Attachment
Create an Attachments Folder
Attaching Multiple Files
Creating a ZIP file
On Error Resume Next
On Error Goto 0
Check to see if File Exists
DIR Function
Add Button to Select a File
Common Dialog DLL
Create a Global Module
SelectFile Function
Attach File Button
Two Attach File Buttons

08. Create PDF Access Attachments (14:48)
Code Definition
Export Report as a PDF File
Docmd.OutputTo
acFormatPDF
Format Function with Dates
Keep History of Sent Reports
Importance High, Normal, Low

09. Mass Email Using Outlook (29:06)
Create Customer List Form
Continuous Forms
Don't Send Report to Everyone
SendReport Field y/n
Docmd.GotoControl
Loop Through All Customers
While Loop, Wend
Docmd.GotoRecord acNext
OpenForm to Specific Record
Run a Subroutine on Another Form
Public Subroutines
Preview Checkbox (Display or send)
Disable Outlook Security Warning
WARNING: Watch For Spam Bots
Trust Center
Programmatic Access
Run As Administrator

10. Send Email Without Outlook 1 (20:05)
What is an SMTP Server
Simple Mail Transport Protocol
SMTP Server Port
Double Click Event
Open Current Customer from List
CreateObject CDO.Message
Collaborative Data Objects
SendUsing
SMTPServer
SMTPServerPort
SendUsername
SendPassword
SMTPAuthenticate
Hide my Username and Password
Global Variables & Constants
Global Const
Global Modules
Renaming Buttons & Moving Code
Caption Property

11. Send Email Without Outlook 2 (21:26)
Clean up Code
Shorten Schema Line
Set Email Variables to Form Field Values
Check for Valid Data
Run-time Error from SMTP Server
Sending Rich Text
Sending HTML Mail
Create Listbox for Mail Format Options
Change Colors with the RGB Function

12. Send Email Without Outlook 3 (20:54)
Sending Attachments
Using Gmail to Send Email
Email Port 465
SSL Secure Sockets Layer
Displaying Real Names in Email Fields
String Concatenation
Trim() Function

13. Send Email Without Outlook 4 (8:51)
SMTPConnectionTimeout
Error Handling
On Error Resume Next
On Error Goto 0
Err.Number
Err.Description
Err.Clear

14. Email from HTML or Web Page (10:37)
Create a Newsletter in Microsoft Word
Access Learning Zone Newsletter
Save as HTML Filtered
CreateMHTMLBody
Sending a Local HTML File
Sending Email by Importing a Web Page

15. Bulk Customized Email 1 (13:25)
Create BulkEmailF
Recordset Loops
Loop Through Customers
CurrentDB
OpenRecordset
dbOpenSnapshot
While Loop
EOF
MoveNext
Customize TO, Subject, Body Fields

16. Bulk Customized Email 2 (12:09)
Variable Substitution
Merge Fields
Replace Function
Nz Function

17. Bulk Customized Email 3 (15:40)
Email Batch Statistics
Current Email Counter
DCount Total Number of Emails
Percent Completed
Create a Status Box
Status Subroutine
Docmd.Beep
DoError Error Box

18. Bulk Customized Email 4 (19:28)
Email Countdown Timer
Send Delay
OnTimer Event
DoEvents
Abort Checkbox
Pause Checkbox
Flash if Paused

19. Bulk Customized Email 5 (36:23)
Select an Email Template
Template Table and Form
OnCurrent Event
Show Allowed Merge Codes
Tab Order
Auto Order
Tab Stop Property
Docmd.GotoControl
SetFocus
Custom From Address for Each Template
RecordSource Field vs Property
SQL SELECT Statement
Dirty Records
Me.Refresh

20. Bulk Customized Email 6 (22:59)
MoveFirst, MoveLast
RecordCount Recordset Property
"Sending..." Indicator on Communicate
HTML Editor
Microsoft Expression Web
FTP with Windows Explorer
Import HTML Text into Body Field
Read Text File
CreateObject
Scripting.FileSystemObject
OpenTextFile
ReadAll

21. Mailing List Management (39:43)
Yes/No Field to Send or Not
Mailing List Table
Customer X Mailing List Junction Table
Many to Many Relationships
Customer X Mailing List Subform
Continuous Forms
Combo Box for Selecting Mailing Lists
Mailing List Query with Customer Data
MailingListWithCustomerQ
MsgBox with VbYesNoCancel Buttons
Get Input from MsgBox
Count Total Emails on Mail Form Open
Set Mailing List Name Automatically
Set Recordsource SQL Automatically
Set Background Color Green on Change

22. Your Own External Mail Server 1 (24:15)
Send Using Your Own Dedicated Mail Form
Copy Email Send Form
Check For Missing Email Addresses
Recordset for Adding Emails to Server
dbOpenDynaset
Add Emails to Outbox Table

23. Your Own External Mail Server 2 (27:43)
Removing Code we Don't Need
Start/Stop Server Button
Message Status and Status Text
Outbox, Sent, Fail Counts
RS.Edit to Edit a Recordset Record
Read Status of Email Send
Set Value in Table

24. Your Own External Mail Server 3 (24:49)
Enhanced Countdown Timer
Separate Email Server Database
Delete Email Table from Customer Database
Attach to Mail Server Table

25. Reading Email From Outlook (35:23)
Read Email Form
Outlook Object Library Reference
Minimum VBA Code Needed to Read Emails
Outlook.Application
Outlook.NameSpace
Outlook.MAPIFolder
Outlook.MailItem
GetNamespace
GetDefaultFolder olFolderInbox
For Each Item in Inbox
Save Messages to Table in Access
Subject, Sender, SenderEmailAddress
Body, HTMLBody, BodyFormat
olFormatHTML, olFormatPlain
olFormatRichText
Delete a MailItem
Inbox Items.Count
Mark Read, UnRead
Move Emails from Inbox to Completed Folder

26. Collecting Data Via Email (42:01)
Creating a Survey Email
Replying to the Email
Pulling in the Data
Responses from Gmail and Outlook
Reading and Writing Text Files
Open TextFile For Output
Open TextFile For Input
FreeFile
Line Input FF
InStr, Len Functions
Right, Left, Len Functions
ByVal vs ByRef Variable Parameters

27. Collecting Data Via Web Page (21:49)
You will need Windows Hosting for this example
Create an Online Form
HTML Form and Input Tags
Create a Submit page
ASP <% %> Tags
Request Command
Response.Write Command
Response.Redirect Command
DELETE SQL Statement

28. Extracting from Attachment Field (9:02)
Saving Files in an Access Attachment Field
Attachment Field Recordset
myAttach.Value
SaveToFile

29. Miscellaneous (11:28)
Tracking Email Delivery
Read Receipts return-receipt-to
Delivery Status Notification (DSN)
disposition-notification-to
Sending Text Messages from Access Email
Email to SMS Gateways
Extracting Attachments from Incoming Email
Outlook Mail Attachments
Attachments.Count
SaveAsFile Command

30. Review (4:30)

 

You may want to read these articles from the 599CD News:

 
 

Learn
 
Accessindex
Excelindex
Wordindex
Windowsindex
PowerPointindex
Photoshopindex
Visual Basicindex
ASPindex
Seminars
More...
Customers
 
Account Login
Online Theater
Downloads
Lost Password
Free Upgrades
Insider Circle
Student Databases
Info
 
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Articles
Search Our Site
Waiting List
Production Schedule
Collapse Menus
Help
 
Live Chat
Customer Support
WalkThru Tutorials
Troubleshooting
FAQs
TechHelp
Consulting Services
About Us
 
Background
Testimonials
Jobs
Affiliate Program
Richard Rost
Free Lessons
Mailing List
Order
 
Video Tutorials
Handbooks
MYOLP Memberships
Idiot's Guide to Excel
Volume Discounts
Payment Info
Shipping
Terms of Sale
Gift CDs
Contact
 
Live Chat
General Info
Support Policy
Contact Form
Email Us
Mailing Address
Phone Number
Fax Number
Course Survey
Facebook    Twitter

Google Plus    LinkedIn

Blog RSS Feed    YouTube Channel
Richard Rost Microsoft MVP