Access 2007-2019
Access 2000-2003
Access Seminars
TechHelp Support
Tips & Tricks
Access Forum
Course Index
Topic Glossary
Insider Circle
Home   Courses   Seminars   Templates   Help   TechHelp   Forums   Contact   Join   Order   Logon  

Microsoft Access Outlines
Access     Excel     Word     ASP     VB    

Access Index Page
Welcome to the Access Index page. Here I've included all of the course outlines for ALL of my Access courses. The goal is so that you can search through the text of this page to find anything you're looking for, in case the (admittedly limited) search box up at the top of the site doesn't give you the results you want. Just press CTRL-F to find words in your browser. Enjoy!


Access 2019 Beginner 1
01. What is Microsoft Access? (14:10)
What is a database?
Why use a database?
Progression from paper
Benefits of Access
Parts of an Access database
Fields / Columns
Records / Rows
Query, Form, Report
Macro, Module

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

03. Getting Started (16:40)
Starting Access
Pin to TaskBar
Create a new blank database
Parts of the screen
Title bar
Window control buttons
The Ribbon
Collapse (Minimize) the Ribbon
Quick Access Toolbar
Minimize the Navigation Pane
Status Bar
Object Pane
Turn on Overlapping Windows
Get Rid of Tabbed Documents
Close & Reopen Database
Security Warning

04. Customer Table, Part 1 (25:14)
Create New Table
Datasheet View, Table Design
Field Name, Data Type, Description
Field naming conventions
Short Text, Long Text (Memo)
Numbers (Long Integer, Double)
Date/Time, Currency, Yes/No
OLE Object, Attachment
Calculated Field
Lookup Wizard

05. Customer Table, Part 2 (21:29)
Text or Number?
Calculations, Sort Order, Leading Zeros
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 (21:08)
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
Dirty Records

07. Entering Data, Part 2 (15:09)
No data is better than bad data
Download sample database from web site
Security Warning
Pressing ESCape to cancel adding/editing a record
Record Selectors
Delete a record
Access Options - Client Settings
Confirm Record Changes (Deletions)
AutoNumbers are NOT reused
Do you have to save records?
Saving layout changes
Moving a column in datasheet view

08. Sorting & Filtering (18:30)
Sorting a single field of data
Text, numeric, date sorts
Numeric v Alphanumeric Sort
Legos Analogy
Remove Sort button
Filtering records
Show just customers from NY
Filter by Selection
Remove Filter button
Filter Checkboxes
Filter to show Blanks (Null Values)
Navigation Buttons
Printing a table
File > Print
Quick Print, Print, Print Preview
Send as Email Attachment
Export to Word Document
Why Queries are better

09. Customer Queries (24:56)
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?
Show box
Credit Limit less than $1000

10. Customer Form 1 (18:49)
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
Design View
Resizing Text Boxes
Layout View uses Rows & Columns
Save Form
Open in Form View
Editing Data
Using Navigation Buttons
Pencil indicates Dirty record
Record Selector
Delete a Record
Delete the CustomerF Form
Create a Multiple Items form
Continuous Forms
Create a Split Form

11. Customer Form 2 (10:06)
Design View Preview
Form Properties
Record Source Property
Add existing fields
Field list
Click and Drag One Field
Add Multiple Fields
Shift-click method
Ctrl-click method
Switch to Form View
Switch to Design View
Deleting a field
Resize text box or label
Edit the text of a label
Change Colors

12. Customer Reports (25:42)
Create a simple customer report
Types of reports
Modify report title
Print Preview
Zoom Controls
Paper Size
Send as Email Attachment
Save as PDF
Layout View
Report View
Build report from a query
Report Wizard for Multiple Item Report
Report Wizard for Single Report
Mailing Labels
Send to Active Customers Only
Create IsActive Query
Label Wizard
Setup a Prototype Label


Access Index - Beginner
Access 2013 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
Fields / Columns
Records / Rows

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
Field naming conventions
OLE Object
Calculated Field
Lookup Wizard

05. Customer Table, Part 2 (12:14)
Text or Number?
Long Integer
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
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
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 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)
Standard Color vs Theme Color
More Colors
Full Color Palette
Shape Outline
Line Color
Line Thickness
Line Type
Property Sheet Overview
Special Effect Property
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 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
Long Integer
Warning: data may be lost

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

3. Field Properties 3 (16:02)
Format Property
Number Formats
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
BETWEEN keyword
Date Values #1/1/1980#
Date() Function in Validation Rule
Functions in Validation Text

7. Review (4:47)


Access Beginner 4


00. Introduction (6:07)

01. Field Properties 7 (10:42)
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
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

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 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
Inequalitites Review
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
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#
Customers before 30 days ago
Became customers yesterday
>=Date()-1 AND
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
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 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
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 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 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 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()
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
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 Index - Expert
Access 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

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 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
Improve Performance
Storing History Data

02. Explicit Relationships (14:00)
Global Relationships
Relationships Window
Add Tables
Edit Relationships Window
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
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 Expert 3


00. Introduction (7:38)

01. SQL Primer (11:39)
Combo Box Row Source
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
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
Aggregate Functions
Modify Embedded Macro
Change Form Name in Macro
Moving to Access 2013

05. Review (6:41)


Access 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
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 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
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 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
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

06. Review (5:21)


Access 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
Cross-reference table
Junction table
Customers to Groups
Vendors to Products
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
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
Resizing with the Rulerbar
Button from Letter Form

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

07. Review (5:10)


Access Expert 8


00. Intro (8:22)

01. Products to Vendors (24:01)
Homework Review
Button to Open Product Form
VendorF Form
Button to Open Vendor from Product
Button to Open Product from Vendor
Give Names to VendorCombo, ProductCombo
Query to add UnitPrice to Junction
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
Order of Operations
Comparison Operators
< > <= >= <> =
-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
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 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

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
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
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 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
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 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
Statistical Analysis
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
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
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
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 CDATE() Function Brief Mention
Work Log Lookup Form
Employee Combo Box
Command Button Run Query
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 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
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
Default Date Textbox
=[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 \

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

09. Review (5:21)


Access 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 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 change to
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 Expert 15


00. Introduction (8:27)

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

02. Student Attendance 2 (31:12)
Create Attendance Table
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
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
Remove Indexing Primary Key from Archive
Change AutoNumbers to Numbers

05. Review (6:31)


Access 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 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
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
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"
Append "Order Writeoff" to ContactT
Put writeoff notice in contact table
Add OrderID to writeoff notice
Update the order description with "WRITEOFF!"

04. Archive Old Unpaid Orders 4 (13:02)
Delete orders and details from main tables
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

06. Review (5:38)


Access 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 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
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 Expert 20


00. Intro (5:43)

01. Send Bulk Email via Word (29:13)
Query for Customers to Email
Active Customers with Email Addresses
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
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 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 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

03. Fix the Order Entry System (42:21)
Fixing the Order Entry System

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 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 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
Import Tech News from

02. Import Real-Time Weather (14:02)
How to find an RSS Feed not advertised 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
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 Expert 25


00. Intro (5:44)

01. String Functions 1 (26:27)
Function Categories
Separate Phone Number Parts
Area Code, Prefix, Suffix
Separate First and Last Names
Get Last Name even if Middle Name
Convert to Upper Case
Convert to Lower Case
Convert to Proper Case
Remove Extra Spaces from String
Replace One String with Another
String Concatenation &

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

03. Logical Functions 1 (14:23)
Replace NULL Value
Determining Data Types
Data Type Names
Logical Operators

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

05. Review (5:44)


Access Expert 26


00. Intro (06:20)

01. Math Functions 1 (27:09)
Abs - Absolute Value
Sgn - Sign
Postive, Negative, Zero
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 Expert 27


00. Intro (5:46)

01. Date Time Now 1 (24:49)
Inserting DateTime Shortcut Keys
Insert Date with Ctrl-;
Insert Time with Ctrl-:
Insert Date from Previous Record with Ctrl-'
Date Time Functions
Date(), Time(), Now() Functions
Display Current Date in Unbound Form Field
Use as Default Value in a Table Field
Use as Default Value in a Form Field
Format Function Parameters
Show Date as "Monday, December 15, 2014"
Two Digit Year Cutoff - 2030 Problem
Automatically Timestamp New Records
Show Records From Today
Show Records in the Past
Show Records in the Future
Show Records on a Specific Date
Show Records NOT on a Specific Date
Show Records Between Two Dates
Show Records NOT Between Two Dates
Show Records Outside of a Date Range
Show Records on Specific Dates with IN() Function

02. Date Time Now 2 (37:17)
DateTime Mathematics
Date Stored as a Number
Number of Days Since 12/30/1899
Good Enough for Most Circumstances
Watch for Dates with Times with Between
Show Records From Today With Times
Show Records Between Two Dates With Times
Show Records on a Specific Date With Times
Show Records For Tomorrow
Show Records from Yesterday
Show Records Within one week from today
Birthdays From Now until Next Week
Orders Less than One Week Old
Aged Accounts Receivable
Query with Orders, OrderDetailQ, CustomerT
Need DueDate, CompanyName, OrderTotal
Order must be an Invoice and Not Paid
Aggregate Totals Group By ID, SUM Total
Put Aging in Second Query (for clarity)
Use IIF Function to Add Aging Data
Current Orders
Less than 30 days old
Between 30 and 60 days old
More than 60 days ago
Number of Days Late
Accounts Receivable Report
Conditional Formatting to Hide Zero Amounts
Totals in Report Footer

03. Date Time Now 3 (25:24)
Hours as Fractions of a Day
One Hour in the Future
Five Minutes in the Past
CDate() Function Convert Numbers or Text as Date
Calculating Age "Good Enough" Div by 365.242
Calculating Age in Whole Years
Display Total Time Worked in hh:nn
Display as 8:30, 8.5, and "08h 30m"
Timesheet Spanning Midnight
Original Worklog used Date and Time
What if you have only Time in your log?
Showing 24:00 in WorkLog instead of 00:00
Format two digits

04. Review (6:09)


Access Expert 28


00. Intro (8:30)

01. Break Apart Dates (21:59)
Day(), Month(), Year()
Hour(), Minute(), Second()
Is date in current year
Format property display -1/0 as True/False or Yes/No
Is date in previous year
Is date in next year
Is date in current month
Is date in current year to date
WeekDay() Function
WeekDayName() Function
MonthName() Function
What Day of the Week is Today?
First Day of Week
Last Day of Week
Tuesday Following Date
Weeks that Start on a Different Day (like Monday)
What's today's Weekday if week starts on Monday?
What's the date of the Monday before today?
Is Date a Work Day (Mon-Fri)
Is Date a Weekend Day (Sat, Sun)

02. DateAdd Function (9:32)
Query Insert Columns
DateAdd() Function
One day from Date
DateAdd Format Codes
One week from Date
One month from Date
One year from Date
How DateAdd handles leap years
One week before Date
Exactly 9 months from Date
Exactly 21 years before Date
1.5 years from Date
Within one calendar month from Date
Less than one calendar month before Date
Ten Minutes from Date

03. DateDiff Function (15:32)
Calculating Difference Between Two Dates
DateDiff() Function
Number of days between two dates
Number of days since order placed
Number of months until mortgage is paid
Someone's age (not 100% reliable)
Number of weeks since Jan 1st of current year
Number of minutes worked
First Day of Week Optional Parameter
First Week of Year Optional Parameter

04. DatePart Function (16:52)
Working with individual date components
DatePart() Function
Show orders from this year
Similar things with DatePart as with the Day, Month, Year functions
Date in a specific month
Date in a specific quarter
Date in current week of year
Invalid formulas on Microsoft's web site
Date in previous week
Date in next week
Be careful of suspicious behavior around the end of the year
Use optional parameters for DatePart
Date in previous month
Date in next month
Date in current quarter
Date in previous quarter
Date in next quarter

05. DateSerial Function (17:03)
First Day of Month
Last Day of Month
First Day of Previous Month
Last Day of Previous Month
First Day of Following Month
Last Day of Following Month
First Day of Quarter
Last Day of Quarter
First Day of Year
Last Day of Year
How Many Days in Month
How Many Days in Quarter
How Many Days in Year
What Day of the Year is it?
How Many Days Remaining in Year?
Exact Age / Anniversary Calculations
TimeSerial() Function Just like DateSerial
DateValue() Function
TimeValue() Function
CDate() Function - BOTH date AND time
Useful to convert strings to DateTime values

06. Ordinal Dates (08:40)
Displaying 1st, 2nd, 3rd, 4th, etc.
Discuss VBA Function for Ordinals
Learn how to calculate with IIF Functions

07. Miscellaneous (33:02)
Calculate Someone's Next Birthday Date
List of Birthdays in Next 60 Days
How many days until your next birthday
List of Birthdays from Next Month
Use the NZ function to substitute a date
Not all customers have CustomerSince
If NULL, assume start date of business
Field Level Validation Rules (Field-Level)
Table Level Validation Rules (Table-Level)
Validation Rule ShipDate > OrderDate
Validation Rule OrderDate <= Now()

08. Review (6:36)


Access Expert 29


00. Intro (6:23)

01. Aggregate Functions (12:56)
Sum, Avg, Count, Max, Min, First, Last
Will cover StDev Later
Will cover Variance Later
Use in a Form Footer
Use in an Aggregate Query
Use Aggregate as Query Criteria

02. Domain Aggregate Functions (17:53)
DLookup, DSum, DCount, DAvg
DMax, DMin, DFirst, DLast
Domains & Criteria
Number, String, Date Criteria
No Criteria
Double Double Quotes for Strings
Largest Autonumber in Table
How Many Records in Table
Criteria on the Current Form
Criteria on Another Form
Concatenation of Criteria
Multiple Criteria
Parentheses in Multiple Criteria

03. Domain Aggregate Examples (18:14)
DLookup Sales Rep Phone Number
DSum Orders From Last 30 Days
System Default Values Table
DCount Orders in Last 30 Days
DMax Show Last Order Placed
DAvg to Show Average Sales
Calculate Average Daily Sales
Forecast Future Sales

04. Create Your Own Counter (26:39)
Creating Editable Counters
Can't put DMAX or MAX in Table Default
Expression Builder List of Functions
Try Using Form Default Value Field
Order Of Evaluation
Before Insert Event
Can't use Max in Macro
Use DMax to Find Largest Current Value
Increment Counter Value
Test Referential Integrity
Cascade Updates for ProductCategoryID
Use NZ for initial seeding
Show DMax VBA Code

05. Running Balances (24:48)
Set up Bank Account Table
Check Register
Deposits are Positive Amounts
Checks are Negative Amounts
Running Sum Property in Report
Running Sum over Group: Month
No Running Sum in Forms
Use DSUM for Running Balance
DSUM Based on the ID
DSUM Based on the Transaction Date

06. Excel VLOOKUP Replacement (14:19)
Student Letter Grade Example
Assign "A" for Grade 90, etc.
DMAX and DLOOKUP Combination
Look up Customer Credit Bracket
Gold, Silver, Bronze, etc.

07. Review (6:36)


Access Expert 30


00. Intro (6:00)

01. Functions & Interest (9:08)
Access Financial Funtions
Functions Covered Today:
Functions NOT Covered:
Simple v. Compound Interest Rates
APR Annual Percentage Rate

02. Loan Calculator (18:08)
Calculate Mortgage Payments
PMT Function
Amount of Loan
Number of Periods
Interest Rate
Monthly Payment
Total Amount Paid
Total Interest Paid
Rounding Issues
Extra Payment at End of Loan

03. Investment Calculator (7:37)
Calculate Future Value of Investment
Amount of Investment
APR Annual Percentage Rate
Number of Years to Invest
Extra Payments
Future Value
FV Function

04. Interest Rate Calculator (11:30)
What was my rate of return ROI
Intial Investment Amount
Amount of Return
Number of Years to Invest
What was my Interest Rate
RATE Function
Why sometimes divide by 12
Annual vs. Monthly Compounding

05. Millionaire Calculator (9:30)
How long until I'm a millionaire
Initial Deposit Amount
Monthly Payment Amount
Goal Amount
Interest Rate
Calculate Number of Months
Convert to Whole Years
INT Function
NPER FUnction

06. Initial Deposit Calculator (7:06)
College fund calculator
How much deposit to hit goal return
Current Balance Amount
Interest Rate
Years to have account open
Extra annual payments
PV Function

07. House Value Calculator (5:55)
Monthly payment you can afford
Interest rate from bank
How many years for your mortgage
Calculate house you can afford
PV Function

08. Loan Amortization (22:47)
How Amortization Works
Interest and Principal Portions
Build LoanF Form
Create AmortT Table for Periods
Build AmortF Subform
Link Child and Master Fields
Calculate Interest Paid
IPMT Function
Calcaulte Principal Paid
PPMT Function
Rounding Issues
IIF Function to Hide Errors

09. Review (6:51)


Access Expert 31


00. Intro (4:55)

01. Charts 1 (11:22)
What is a Chart
Why are Charts Useful
Chart Area
Data Series
Data Points
Chart Axes
Horizontal X Axis
Vertical Y Axis
Chart Title & Legend
Chart Gridlines
Create a Sales Chart
Chart Wizard
Add Chart to Form
Chart Types

02. Charts 2 (10:52)
Chart Formatting Options
Chart Datasheet
Components Dropdown
Edit Chart Title
Format Data Series
Change Colors
Fixing Distortion
Clip, Stretch, Zoom

03. Charts 3 (10:40)
Transparent Chart Background
Multiple Data Series
Preview Chart Button
3-D Column Chart
3-D View & Effects
Elevation, Rotation

04. Charts 4 (12:03)
Data Series Shape
Data Labels, Value
Gap Width, Depth
Chart Depth
Format Walls, Floor
Aggregating Data
Add Trendline
Pie Charts
Angle of First Slice
Data Labels
Leader Lines

05. Charts 5 (9:28)
Review PCResale Database
Sales by Rep previous 30 days
Chart Dashboard
Setup Queries

06. Charts 6 (11:13)
Build Dashboard
Chart in a Report

07. Review (4:49)


Access Expert 32


00. Intro (4:10)

01. Single Letter Report Header (18:05)
Contact Report
Creating Group for Single Letter
Group by First Character
Display LEFT 1 Character
Group by Contact Name
IsNull to Show No Company Name

02. Report List Form (17:19)
List of All Reports in Database
MSysObjects Table
List of Type Values
Custom Report List Table
Report List Form and Listbox
Macro to Open Report
Build Event
On Click Event

03. Rank, Median, Mode (25:04)
SubQueries, SubQuery
Query Customers Less Than Average Employees
Rank Formula
Alias for a Table
Median Value "Good Enough"
Mode Value
Totals Query, Top 1 Value
Percentile Ranking

04. Intro to Macros (36:18)
Customer Mailing Labels
Create LabelT Table
Create LabelR Report
Insert Blank Records at Top
Cut and Paste
Create LabelF Form
Create View Labels Button
Print Preview Report
Macro to Insert Blank Records
Command Button Macro
GoToRecord New Record
Show All Actions
Unsafe Actions
Form Macros v External Macros
Button to Run External Macro
Prompt For Loop Count Value
InputBox Function
Insert Blanks on Top of Label Sheet
SelectRecord, SelectAllRecords
Cut, Paste
Button on Customer Form to Add Label

05. Review (5:12)

Access Index - Advanced
Access Advanced 1


00. Intro (2:31)

01. Macro Basics (13:40)
What is a Macro?
Benefits of Macros
Limitations of Macros
Review of Macros in our Database

02. Building Macros (14:47)
Create a Standalone Macro
Create > Macro
Macro Builder
Action Catalog
Beep Command
Delete a Command from Macro
MessageBox Command
Command Arguments
Hello World Example
OpenForm Command
Where Condition v Filter
Embedded Macro in Form Button
Build Event
On Click Event

03. Making Macros Smart (16:58)
Macro to Open Form to Specific Record
On Dbl Click Event
= Sign Required in Where Condition
Blue Fields Double-Clickable
ControlTip Text
Open Specific Contact
If Then Command
IsNull Function
If Not IsNull
Add Else Condition
MessageBox Command
StopMacro Command
Show Orders for Specific Customer

04. Form Open Events (15:29)
More With Conditions
Open Specific Order from OrderListF
Requery a Form when you return to it
On Activate Event Example
Discussion of Other Events:
On Open, On Close
On Resize
On Activate, On Deactivate
On Load, On Unload
On No Data Event for Reports

05. Review (4:44)


Access Advanced 2


00. Intro (2:33)

01. Add New Order Button (7:28)
Open Form to New Record
GoToRecord, New
Add New Order Button on CustomerF
Add New Order Button on OrderListF

02. Add Details Before Order (20:29)
Adding Child Records Before Parent
Missing Parent ID
Before Insert Event

03. Quote or Invoice Caption (28:23)
Toggle Button
Bind Button to Data
SetProperty Command
On Click Event
Caption Property
Change from Record to Record
Change on Form Open
On Current Event
External Macros
RunMacro Command
Reusing Code / Macros
SetValue Command
Enabled Property

04. Review (4:05)


Access Advanced 3


00. Intro (2:48)

01. More Control Properties 1 (19:20)
#FFFFFF Colors
Use VAL() Function
How Hexadecimal Numbers Work
RGB() Function
Access Color Codes
Command Button to MessageBox Color Code
Discussion of Color Constants in VBA
vbRed, vbBlue, etc.
Theme Colors v. Standard Colors
FontSize Property

02. More Control Properties 2 (21:14)
FontName Property
Visible Property
Height, Width Properties
OnGotFocus, OnLostFocus Events
Make Notes Field Larger When Clicked On
Top, Left Properties
BackStyle Property
Have Access Tell You the Numeric Values
BorderStyle Property
Changing Form Background Colors

03. Locking Paid Orders (35:14)
Enabled Property for Controls
Lock Subform
Difference Between Locked and Enabled
Store Data in Temporary Variables
SetTempVar Command
InputBox Function
MessageBox TempVars!Variable
Check for Password
BeforeUpdate vs. AfterUpdate
Cancel BeforeUpdate Event
CancelEvent Command
Don't Run Event on PAID only UNPAID
RunMacro Command

04. Review (4:13)


Access Advanced 4


00. Intro (3:48)

01. Password to Open Form (14:23)
InputBox to Collect Password
SettingT Table for Global Settings
SetTempVar to DLOOKUP Password

02. Full Sheet of Same Customer Label 1 (9:02)
Non-Macro Method
Create Mailing Labels Using Wizard
Use Forms!CustomerF for Data
Set up Bogus Table for 30 Records

03. Full Sheet of Same Customer Label 2 (20:22)
Individual Labels with SendKeys Method
OpenTable Command
GoToRecord Command
SendKeys Command
Loop Method with External Macro
Create Label Form
SetValue Labels from Customer Form
RunMacro Command
Repeat Count to Loop
Repeat Expression

04. New Method Quote v Invoice (6:07)
Quote or Invoice Word on Report
Build Event of Detail Section
SetProperty Caption
Make IsQuotation Invisible

05. Macro Groups and Submacros (9:49)
Macro Group
RunMacro MacroName.SubMacro
Default Macro Action
One Macro to Open Multiple Forms

06. Review (3:59)


Access Advanced 5


00. Intro (3:14)

01. Separate Shipping Address 1 (14:59)
Add ShipTo Fields
Ship Same as Billing Checkbox
Macro to Copy Billing to Ship Address

02. Separate Shipping Address 2 (15:21)
Add Fields to Order Table
Copy Ship Bill Fields to Order Form

03. Move Items Between List Boxes (21:13)
Access Drag and Drop Limitations
Form Showing Active and Inactive Employees
Button to Move From Active to Inactive List
RunSQL Command
Put Move Code in On Double Click Event
Click to Preview Employee Record

04. Miscellaneous (15:03)
AutoExec Macro
Bypass the AutoExec
Error Handling
Simple Calculator Form
OnError Command
VAL() Function
Setting 0 on NULL Values
Error Submacro
Macro Design Tab
Run Button
Single Step Toggle

05. Review (5:55)


Access Advanced 6


00. Intro (4:38)

01. SQL Search Boxes (35:50)
Create Unbound Search Text Boxes
SQL Statement to Replace RecordSource
Build SQL Statement in a Text Box
Separate WHERE Clause Box
Multiple Conditions

02. Data Macros (17:40)
If NumEmployees < 0
ProductT Add UnitCost, Markup
Auto Calculate UnitPrice
If [IsInsert] For Adding Records
After Update
Create a Log Table
="Updated " & [CustomerT].[CustomerID]
Macro Shortcut on Desktop

03. Review (7:42)

Access Index - Developer
Access Developer 1


00. Intro (4:14)

01. Intro to Access VBA (8:42)
What is VBA
Visual Basic for Applications
Visual Studio
Visual Basic .NET
Benefits over Macros

02. Our First VBA Code (12:51)
Hello World
Turn off Project Explorer
Turn off Properties Window
Turn off Immediate Window
MsgBox Command
Optional Parameters
Multiple Parameters

03. DoCmd.OpenForm (12:46)
Open Form Command
Arguments List
Where Condition
Open Specific Record
Duplicate Code
Preview of Creating Subs

04. Subs with Multiple Commands (5:59)
Archive Contacts Button
MsgBox Command

05. Add Product Button (12:01)
If Then Statement
End If
Docmd.GoToRecord acNewRec
SetValue in VBA

06. AfterUpdate Event (10:19)
Add New Customer Button
After Update Event
Always Use Event Procedures
Boolean Values TRUE is Assumed

07. Review (3:53)


Access Developer 2


00. Intro (2:20)

01. Calculator 1 (20:05)
Naming Buttons
String Concatenation
VAL() Function
Change Button Name, Edit Code
Add, Subtract Buttons
Multiply, Divide Buttons
Square Root, Exponents
Nth Root
Divide by Zero Error
On Error Resume Next
If Then Else
Exit Sub

02. Calculator 2 (12:59)
Create Private Sub
Parameters to a Sub
Right Click > Definition for Sub
Select Case Statement
End Select
Proper Indenting
Tab / Shift Tab Multiple Lines

03. Commission Form 1 (27:00)
Sales Rep Combo Box
Commission Rate in Columns
AfterUpdate Event to Copy Rate
Calculate Commission Button
Create a Function
Return a Value
Boolean Values
Subs vs. Functions

04. Commission Form 2 (15:57)
VB Line Continuation _
MsgBox vbInformation
Warning if Sale Amount $0
Set to Null after logging

05. Review (4:18)


Access Developer 3


00. Intro (3:07)

01. Variables (22:12)
Exit Sub
Line Continuation Character
DIM ID As Long
On Error Resume Next
On Error Goto 0
Initializing Variables
MsgBox as a Function
vbYes, vbNo, vbCancel

02. Random Numbers (9:33)
Const - Constants

03. For Next Loops (21:47)
Looping Math Quiz
Set NumLoops
For Next
Format Function, Percent
Format Function, 0.0%
Data Types in Access VBA
Integer, Long Integer
Single, Double
Date, Date Time

04. Quick Add Sales Rep (21:10)
Add Link on Commission Form
InputBox First, Last, Com Rate
DCount, DMax, CDbl
Run Another Objects Event

05. Review (5:43)


Access Developer 4


00. Intro (3:09)

01. Add Notes to Order Details (22:15)
DLOOKUP Long Text Memo
Notes Field in OrderDetailT
Add to OrderDetailF
Make Size Change in OnGotFocus
Height, Width Properties
Change Detail Section Height
Variable Scope - Sub vs Form
Form Scope Variable to hold Height, Width
OnDblClick Event to Grow

02. Commissions by Order Amount 1 (22:33)
Modify CommissionT
Base Commission on Order
Employee Commission Table
Set up Brackets for Commission Ranges
Fix Sales Rep Combo Box
Query to Link Orders, Commissions, Cust
Order Picker Combo Box

03. Commissions by Order Amount 2 (43:50)
Change Order Combo to List Box
SQL Statement to Load List
Limit by Sales Rep
Query Employees w Unpaid Commissions
Limit Employee Combo to Unpaid Only
Lookup Order Total for Selected Order
Look that up in Commission Table
Log to Table with SQL INSERT INTO

04. Review (4:06)


Access Developer 5


00. Intro (3:58)

01. Pay Commissions 1 (15:21)
Add Commission Check ID to Com Table
Create Commission Check Table
Commission Select Query
Commission Check Employee Query
Commission Check Form
Combo Box to Select Employee

02. Pay Commissions 2 (20:38)
Commission List Box
Is Selected
Copy Between Two List Boxes
Drag and Drop Discussion
Click and Drag
Create ComList2
SQL UPDATE to Select Items
Totals for each List Box
DSUM Function

03. Pay Commissions 3 (20:17)
MsgBox vbYesNoCancel vbYes
SQL INSERT Check into Table
Add EmployeeID to CommissionT
Database Theory v Practice
Update CommissionT for Changes

04. Printing Checks 1 (15:23)
Convert Currency or Number to English
SpellNumber Function
Create a Global Module
Look up Largest Check Number in Table
Create New Check Number

05. Printing Checks 2 (20:30)
Create Check Report
Blank Laser Printer Checks
Process Current Check
Print All Checks
Mark Printed
DoCmd.OpenReport acViewPreview
OnNoData Report Event
Mark ALL Commissions Selected

06. Review (4:27)


Access Developer 6


00. Intro (4:36)

01. Converting Macros to VBA (29:41)
Quotation or Invoice in Report
Examine old Macro from Advanced 2
Label.Caption Property
Detail_Format Section
Report Detail Build Event
TRUE assumed in IF statements
VB Editor Auto Capitalization
vbRed, vbBlue
ForeColor Property
Quote or Invoice Toggle Button
Global Module for IsQuotationCode
Enabled, Locked Properties
IsPaid Password Code to VBA
Convert Macros to VBA

02. Enhanced Timer Form (16:35)
OnTimer Event
Timer Interval
One Second Ticks
IsPaused Checkbox
Checkbox to Control Events Firing
Specifying Timer Interval Text Box

03. Cascading Combo Boxes (9:47)
Dynamic Combo Boxes
Control Wizard Not Starting
Product Category SQL Rowsource
Select Category, Products Filtered

04. Automated Backups (34:26)
Trusted Locations
Backing Up Critical Tables
You Should Have Separate Backup
Use This to Backup Critical Data
Backup With Timestamp
Current Date Time Field
Last Run Field
Run Every X Minutes or Hours
86400 Seconds
FileCopy Permission Denied
CopyFileA Windows API
Replace Function

05. Review (3:44)


Access Developer 7


00. Intro (3:24)

01. Partial Payments 1 (30:54)
Create PaymentT
Create PaymentMethodT
Create PaymentF
SUM in Form Footer PaymentTotal
DSUM Payment Total
Popup, Modal Forms
Refresh Subforms
Forms!FName!Subform.Form!Field Notation

02. Partial Payments 2 (13:36)
Don't allow Payment if PAID
Don't allow Payment if QUOTATION
Add Payments to Invoice Report
If No Payments, Handle NULL Value
NZ Function

03. Partial Payments 3 (22:29)
Add Horizontal Line over Payments
Hide Payments, Due if Null or Zero
CTRL-Y to Delete Line of Code
Visible Property
Section Header Build Event Code
Can Grow, Can Shrink for Section Header
Can Grow, Can Shrink for Text Boxes
Labels Don't Shrink
IsQuotation Not In Subform
Duplicate Fields in Both Tables
Specified Field Could Refer
Refer To More Than One Table
Add IsQuotation to OrderDetailQ

04. Review (2:35)


Access Developer 8


00. Intro (3:30)

01. Payment Mods 1 (23:48)
Default to Amount Due
Combo DropDown Method
Add Amount Due to PaymentF
Refresh Amount Due
Refresh Total Payments
On Unload Event
Mark Order Paid
Check for Overpayments

02. Payment Mods 2 (27:25)
Don't Allow Edits if Paid
Manager Override Password to Edit
Create a Public Global Function
IsManager Function
Returning a Value from a Function
Replace Password Elsewhere
Globally Search All Code
Search Code Entire Project

03. Search As You Type (23:11)
Create Search Query
On Key Press Event
What is ASCII?
CHR Function
OnChange Event
Len, Nz

04. Review (2:32)


Access Developer 9


00. Intro (6:31)

01. Miscellaneous (27:19)
Add Search Form to Main Menu
Open Customer from Search Form
Show/Hide Manager Button If Paid
Delete A Payment Problem
Form Current Event from Top Menus
Mark Unpaid if Payment Deleted
Lock or Unlock if Paid Unpaid
Enabled v. Locked Properties
Properties of Subforms

02. Test Taker 1 (38:48)
Build Tables
Table Relationships
Enforce Referential Integrity
Multiple Choice v. Fill In Blank

03. Test Taker 2 (20:07)
Create Forms for Data Entry
Department, Class, Test Forms

04. Test Taker 3 (19:00)
Question Form
Set Caption On Open
Form with a Subform DefaultView
Continuous Forms Problem

05. Test Taker 4 (37:41)
Begin Taking Test
Combos to Select Student, Test
Disable on Selections
Begin Test Button
Check for Questions on Test
Create Test
Create a TestResult Record
DMAX to Look Up ID

06. Review (2:42)


Access Developer 10


00. Intro (2:51)

01. Test Taker 5 (22:09)
Figure Out Which Question to Ask
Show Unanswered Questions for Test
Set Order Default to Max +1

02. Test Taker 6 (9:40)
Get the Question
Fields to Display Q&As
Add Scroll Bars to Text Box
Display Next Question Sub
Required Order Value
Validation Rule >0

03. Test Taker 7 (36:26)
Answer Given Text Box
Answer List Box
Determine if Multiple Choice
Display Text or List Box
Check Answer for Correctness
NZ with False
StrComp() if Case Sensitive

04. Test Taker 8 (15:31)
Calculate Grade
Store in TestResultT
ROUND Function

05. Review (3:30)


Access Developer 11


00. Intro (5:39)

01. Test Taker 9 (32:21)
Hide Begin Test Button
Display Question 1 of X
Take This Test Button on TestF
DoCmd.Close acForm Options
acSaveYes, acSaveNo, acSavePrompt
IsLoaded Check if Form Is Loaded
Cascade Close Child Forms
OnCurrent Close and Reopen Child

02. Test Taker 10 (38:44)
Tracking Test Start End Times
Answer Start End Times
Display Timer on Form
Timer Interval
RGB Function and Colors
Max Time to Take Test
Calculate Elapsed Time

03. Universal Dialog Box 1 (23:52)
Custom Prompt

Enhanced MsgBox 
OK Button
Modal, Popup
Create a Global Public Module
uDialog Sub
PromptLabel.Caption Property
uDialog.Title Property of Form
Optional Parameters
BackColor RGB
With / End With
FontSize Parameter

04. Review (3:54)


Access Developer 12


00. Intro (5:01)

01. Universal Dialog Box 2 (21:28)
OK, Cancel, Yes, No Buttons
Turn Sub into Function
Global Variables
While Loops
While Wend
Return Function Value

02. Custom Form Positioning (23:49)
Height and Width of Forms
Twips = Inches * 1440
Twentieth of an Inch Point
Design Time Width of Form
Runtime Width of Form
OnResize Event
WindowHeight, WindowWidth - Read Only
There is no OnMoved event
You can use a timer event
Twips Round to 15
Center Form On Screen

03. Universal Dialog Box 3 (35:14)
Not exactly in the center of the screen
uDialog Left, Top, Height, Width
Size of Controls in Form
Resize Prompt Label on Form Resize
Border Style Sizable, Dialog
Move Buttons as Form is Resized
Button Top, Left
One or Two Buttons Visible

04. External String Manager (21:59)
Multi-Language Database
Don't Design Multiple Forms or Databases
Different Prompts on Forms and Reports
Create User Settings Table
For Multiple Users See Security Seminar
Create String Table
Dynamic Labels on Customer Form
Label Caption Property
Dynamic Labels on Customer Report
Create External Module GetString Function
Use GetString as a MsgBox String

05. Review (2:31)


Access Developer 13


00. Intro (3:32)

01. Search Between Dates (26:01)
Search Records Between Dates
Begin Date, End Date
SQL To Requery Form
Combo Box for Before, After, Between
Select Case
Visible and SetFocus
Limit To List
Allow Value List Edits

02. Tuition Levels 1 (32:27)
Tuition for a Private School
Same as Products With Multiple Options
Billing on Grade Level
Add Tuition Discounts
Add Discounts Per Child
Multi Field Indexing
Composite Key
Composite Index
Create Pricing Form to Add Schedule

03. Tuition Levels 2 (47:58)
On Dbl Click to Add Tuition to Order
OpenForm, GoToControl
GoToRecord, Set .Form!Pricing
Pricing Wizard Form
Show Only Tuition Levels for Grade
Aggregate Query
Requery, SetFocus, DropDown
Change to List Boxes
Variable Pricing
Check if Form Open
On Error Resume Next
On Error Goto 0

04. Conclusion (2:23)


Access Developer 14
01. The Database Structure (30:14)
The Easy Access POS
Planning the Database
Discussing Features to be Built
Groups & People (Family & Students)
Products & Categories
Discounts, Adjustments, Tax, Tip
Unbound Modal Popup Forms
Barcode Scanner Capable
Avoiding Multi-User Problems
Touch-Screen Friendly
Payment & Payment Types
Credit Cards, Cash
Popup Keypad for Numeric Entry
Calculate Change
Voiding Sales
Multiple Locations
Manager Menu
Edit Lists, System Settings
Edit Orders & Payments
Sales Charts & Graphs
List of Groups with Open Balances
Mail Statements
Detailed Sales Reporting
Possible Future Upgrades

02. Building the Tables, Part 1 (31:37)
Review of Comments from Lesson 1
Changes to be made
Table Design

03. Building the Tables, Part 2 (34:15)

04. Order Form, Part 1 (21:30)
Create New Form
Modal, Popup
Note design view bug for Popup Forms
Make NOT Modal / Popup For Now
No Scrollbars, Navigation Buttons, Record Selectors
No Control Source
Create CategoryList
Make text large enough for touch screens
Create ProductList
Delete RowSource
AfterUpdate Event in CategoryList
Populate ProductList
Create OptionList
Populate OptionList

05. Group and Person Select (30:42)
GroupName, PersonName on OrderF
Hidden PersonID


Access Developer 15
Lesson 1: Move Between List Boxes (20:15)
Active / Inactive Customer List Boxes
Move Between Boxes
UPDATE SQL Statement
Passing a Control as a Parameter

Lesson 2: Debugging Level 2 (31:55)
On Error Resume Next
Invalid Use of Null
Debug Toolbar
On Error Goto 0
On Error Goto MyErr
Custom Error Messages
Exit Sub Before Error Handler
Immediate Window
Show Debug.Print
Immediate Window
Basic Math ?5+9
Checking Variables ?X
Set a Breakpoint
Testing Function Return Values
Test a DLookup
I prefer StatusBox for Users to See
Status Function
Step Over, Step Into, Step Out
Watch Window
Global Const

Lesson 3: Multi-Select List Boxes (42:34)
Change to Multi Select
Simple v. Extended
.ListCount Property
.ItemData(Row) Property
Don't Use Column Headers
Loop Through All Items
For X=0 to Count-1 (For Loop)
With / End With (Dev 11)
.Column(0,x) Property - Row is New!
How to Tell What is Selected
.Selected(Row) Property
Status Selected Items Only 
For Each Loop
For Each I in .ItemsSelected
Control variable must be Variant or Object
ChangeCustomerStatus Update
Call ChangeCustomerStatus in Loop
For Each Form in Forms Collection
Write ButtonCode Sub
Change IsActive to IIF Function
CTRL-Y to Delete a Line
db.Execute vs. DoCmd.RunSQL
Select All / Deselect All buttons


Access Developer 16
01. Homework Plus (13:16)
Select & Deselect All Buttons
Both List Boxes
One Sub Handles All 4 Cases
Count of Records Selected
IIF for Customer or Customers

02. Bug Fix, Visual Glitch (18:05)
Bug in List Box Form
Deselect Items as you Change Table Value
Visual Glitch in Listbox Selection

03. Formatting, Colors in a List Box (34:31)
Make a Continuous Subform Instead
Sales Rep Table
Conditional Formatting Expression Is
Make SubForm
No Link Master or Link Child Fields
Expression = SalesRepID
Requery On Current
CustomerID.SetFocus Get Rid of Selection
On Mouse Move
Change Screen Pointer

04. Recordsets Part 1 (19:36)
What is a Recordset
Why use Recordsets?
Talk about DAO v ADO
Create our First Recordset
Dim Database, Recordset Objects
Close Objects
Set to Nothing
Display DB and RS Names

05. Recordsets, Part 2 (32:00)
Displaying FirstName
While Wend Loop Thru Records
Avoid Endless Loops
Add a Loop Counter
Don't Use Spaces!
rs.EOF rs.BOF
Dealing with No Records
Creating Unbound Text Box
Value List
Adding Records
Ampersand Button Caption Alt-A
Default Button for Form
Add from RecordSet to ListBox
RemoveItem from Single ListBox


Access Developer 17
01. Remove Items Unbound Multi-Select List Box (23:16)
Remove From Single Column Box
Remove From Multiple Column Box

02. Export Individual Customer PDF Files (18:22)
Create Customer Report
Get Rid of Alternating Background Color
Force New Page After Detail Section
Export Report to PDF
Export Individual Customer Reports
Open Report to Specific Customer
View Individual PDF Files

03. DLookupPlus Function (34:43)
Use Recordset to Return List of Items
Example: Sales Reps & States they Cover
Joe: NY, TX, FL
Sue: PA, CA


Access Developer 18
01. Progress Bar Part 1 (32:26)
Microsoft ProgressBar Control v 6.0
Problems with ActiveX Controls
For Loop with Progress Bar
Multiple Running Queries
Simulated Action with Sleep
Text Box with XXXXX
String Function

02. Progress Bar Part 2 (15:46)
Two Textbox Progress Bar
Overflow Error

03. Progress Bar Part 3 (46:12)
Option Explicit
Loop Time with SQL Insert
Calculate Elapsed Seconds
Loop Time with Recordset
Show Seconds Remaining
Set Caption "Speed Test"
Recordsets Faster than SQL INSERT

04. RS Edit AddNew (53:20)
Create Unbound Customer Form
Error Handling CustomerT Not Exist
Load Current Record
Talk About Needing MoveLast with Snapshots
Lock Down the Controls
Edit Button

05. Delete & Sort (12:09)
rs.Sort Discussion Of
Sort by Changing Recordset SQL


Access Developer 19
1. Bound Multi-Select Listbox 1 (29:28)
Recordset Types
dbOpenTable, dbOpenDynamic
dbOpenDynaset, dbOpenSnapshot 
Multi-Select Option Listbox
Product Interest Junction Table
Subform to Select Products
Replace with Multi-Select Listbox
Load Values OnCurrent

2. Bound Multi-Select Listbox 2 (11:15)
Save on AfterUpdate of Listbox
Save on AfterUpdate of Form Once

3. Nested Recordsets (13:41)
Customers w Contacts
Multiple Recordset While Loops

4. Chef's Kitchen Helper 1 (18:15)
Recipes, Ingredients, Inventory
Products On Hand
What Can I Make?

5. Chef's Kitchen Helper 2 (17:15)
Quantity on Hand
Quantity Required
Calculate with Queries

6. Chef's Kitchen Helper 3 (24:54)
Use Nested Recordsets
Fill Listbox

7. Chef's Kitchen Helper 4 (22:22)
Select Dish to Make
Remove Ingredients From Inventory



Access Developer 20
1. Recordset to External Database (25:04)
Set up Back End Database
Connect with OpenDatabase
Set Database Password on Backend
Open Exclusive
Encrypt With Password
Send Password in Connect String

2. Add Followups (16:39)
5, 10, 30, 90 Day Followups

3. Add Records GoToRecord (17:02)
Custom Boxes
Determine Next Box Number
DMAX Function
Split Prefix / Suffix
Nested FOR Loops
ASCII Codes, CHR Function

4. Add With RecordSets (15:59)
Much Faster!
Jump Back X Rows


Access Developer 21
1. Recordset Data Scrub (32:08)
Making Data Relational
Keep Existing Data
Put Sample Data in BoxT
Create ContainerT: ContainerID, ContainerName
Create PositionT: PositionID, ContainerID, Volume
Create Transfer Routine
Get ID of Newly Added Record

2. Nested Continuous Forms (19:11)
ContainerF, PositionF
Continuous Form inside a Continuous Form
Recreate Add Box Code
Composite Key ContainerID, PositionName

3. Side By Side Subforms (13:13)
Side by Side Continuous Subforms
OnCurrent Event Change RecordSource
Change Subform SourceObject

4. Arrays (17:20)
Dim Static Array
Load Manually
Load with Recordset Data
Dynamic Size Array
Redim Preserve
Ubound, Lbound

5. Parent Breadcrumbs (27:31)
Load Array with Parents
Load Parents into Listbox
For Next Step


Access Developer 22
1. Tag Property (35:04) - Bonus Lesson
Tag Property
Loop Thru Controls
For Each Loop

2. Applying Payments to Orders (40:40)
Display Total Orders For a Customer
Display Total Paid
Display Total Unpaid
Input Payment Amount
Apply to Orders

3. Record Count 1 (37:57) - Bonus Lesson
Numbering Rows
Auto Ordering DCOUNT
Manual Ordering

4. Record Count 2 (51:30)
Recordset Renumber
Deleting Multiple Records
Me.SelHeight Property


Access Developer 23
1. Rental Inventory 1 (17:21)Bonus Lesson
Daily Event to Mark Back in Inventory
Scan Out / In Rental Items

2. Rental Inventory 2 (45:50)
Add Serial Numbers to Items
Look Up by Serial Number / Barcode
Scan Out Books
Scan In Batches of Books

3. Order Entry 1 (67:08) Bonus Lesson
Order Entry Form
Printable Invoice

4. Order Entry 2 (60:16) Bonus Lesson
Order List Form
Copy Customer Address to Order
Product Select Combo
Add to Order Button

5. Inventory Out (40:02)
Add QtyOnHand to ProductT
Create ProductHistoryT to Audit Transactions
Add IsShipped to OrderT, OrderF, OrderListF, InvoiceR
If Shipped Don't Allow Edits, Deletes
Lock IsShipped Don't Allow Manual Edits
Make Ship Button
Order Must Be Paid
Cannot Already Be Shipped
Check Inventory Levels First
If OK, Remove from Inventory


Access Developer 24
1. Reservation System (45:14)
Reserve an Item, Tool, Room, etc.
Before Update Event Logic
Must be Future, Up to 1 Year
Minimum 1 Day Rental
Maximum 2 Week Rental
Conflict Resolution

2. Copy Order with Details 1 (34:22)
Copy Record with Children "Messy" Way
Copy Paste Manually
Copy with Duplicate Record Button
Copy with the Messy Method
DoCmd.Echo On/Off
Deleting Child Records
Don't Rely on Cascade Deletes
Referential Integrity Not in Linked Tables
Find Orphan Records

3. Copy Order with Details 2 (27:31)
Copy Record with Children "Proper" Way
Loop Through Fields in a Recordset
Dim Fld As Field
For Each Fld in rs.Fields
Fields Collection of a Recordset
Copy Order Record
Copy All Details in Loop
Future Proof the Copy!


Access Developer 25
1. ByRef v ByVal Part 1 (11:57)
Passing Values
Learning the Difference
Example: Increment Sub

2. ByRef v ByVal Part 2 (11:58)
Optional Parameters
Use with Form Controls
Determine if Field or Number
Public Subs & Functions

3. ByRef v ByVal Part 3 (18:36)
Function that Returns Multiple Values
Return a Status Code
Return Customer Name

4. Active Form & Control (16:51)
Dim F As Control
Set Keyword for Objects
List of Control Types
acTextBox Type
Change Properties Like BackColor

5. Product Groups 1 (30:09)
Product Group Table, Form
Product Details Table, Form
Calculate Bundle Price

6. Product Groups 2 (37:47)
Add Package as Line Item
Loop Through Package Details, Add
Hide $0 Prices On Invoice in Detail Section
Indent $0 Line Items
Bold Package Names


Access Developer 26
1. Bug Fix - Product Groups (7:34)
Ignore Product Groups
Inventory Calculations

2. Dynamic Customer Search Form 1 (42:18)
Build the List Box
Add Sorting Labels
Sort NULL Values To Bottom of List
Colorize Ascending Descending

3. Dynamic Customer Search Form 2 (20:50)
Field Filter Boxes
Clear Filter Button
Build Dynamic Where Condition

4. Recordset Based Search Form 1 (26:32)
Create SearchT
Delete Filter Boxes
Delete AND / OR Combo
Create RS Loop For Where Condition
TableDefs, Fields, Field Type
Process Numeric, Text, Date, Different
Make Search Subform

5. Recordset Based Search Form 2 (37:03)
For Each Field in TableDef Fields
Fill Combo Box with Field Names
Add Criteria to SearchT
Delete Criteria Button
Update Records on Any Change


Access Developer 27
00. Intro (7:24)

01. Scanning Barcodes, Part 1 (29:07)
Learn Types of Barcodes
1D vs 2D Barcodes
Create Product Table
Barcode Field
Form to Scan Into

02. Scanning Barcodes, Part 2 (34:21)
Scan Products Directly to Order
Increment Quantity if On Order
If Not in Database, Google Product

03. Printing Barcodes, Part 1 (19:27)
What is Code39
Install a Free Barcode Font
Create Custom Labels
Product Barcodes

04. Printing Barcodes, Part 2 (18:52)
Print Multiple Barcodes
Print Button on Product Form
Add to Label Table
Clear Labels Button

05. QR Codes, Part 1 (15:34)
Google Charts API
WebBrowser Control
Display QR Codes in Forms

06. QR Codes, Part 2 (28:01)
Download QR Codes from Web
Save as Local File
Display and Print in Reports

07. Merge Barcoding (27:27)
Bring Barcode Stuff to Developer DB
Copy Scan Textbox
Create Product List Form
Synchronize Two Open Forms
Create Product Form
Product Barcode Lookup Box

08. Add Remove Manual Inventory (19:48)
Manual Inventory Adjustments
Combo Box to Add or Subtract on Lookup
Changing Quantities
Logic to Prevent Negative Inventory

09. Serial Numbers (43:54)
Unit Table, Form
Adding Multiple Units
Create Label Report
Bug Found in Label Wizard
Print Labels Button
Mark Labels as Printed

10. Serial Numbers in Orders (35:29)
Look Up Serial Number OR Product
Scan Part or Product Onto Order
Don't Increment Qty if Serial Number
Check to See if On Another Order
Print Barcodes on Invoices

11. Merge Barcode Printing (15:45)
Add Labels for Any Number of Products
Print Labels, Mark Printed


Access Developer 28
1. Get Report Data from Form (11:25)
Download the D26 L5 Database
Examine the CustomerSearchF
Create CustomerSearchR Report 
Bind Originally to CustomerT
Create Open Report Button
Report_Open (On Open) Event
Report RecordSource Property
Set to RowSource of CustomerList
On No Data Event 
Same Thing with CustomerRecordsetF

2. Customer Pricing 1 (18:20)
Customer Specific Pricing Table
Dlookup Custom Price
IIF Compare Prices
Display Final Price List

3. Customer Pricing 2 (11:17)
Custom Pricing in Combo
Add to Invoice

4. Customer Pricing 3 (15:33)
If Price Changed, Ask to Make Custom Pricing
If Exists, Update. If Not Add New
Check for BOF, EOF
Add or Edit Accordingly
Subform to Edit Custom Pricing

5. Time & Billing 1 (25:47)
Create Work Table, Form
Calculate Total Billable Time
Calculate Unbilled Time
Option to Show All, Unbilled

6. Time & Billing 2 (32:16)
Start / Stop Timer
Lock Controls on Form
Bill in 15 Minute Increments

7. Time & Billing 3 (23:37)
Create Invoice Button
Create Order, Get ID
Recordset to Create Order
Recordset to Loop Thru WorkT
Create Order Detail Items
Update Customer Address Info

8. Time & Billing 4 (24:38)
Aggregate Query Unbilled Work
Listing for All Customers
UnbilledWorkF with Listbox
Convert Code to Global Mod
RS Loop Thru Unbilled Work
Generate All Invoices

9. Print & Email Invoices (37:30)
Print all Unpaid Invoices
Email all Unpaid Invoices
CDO.Message Send Email
Export Invoice Report as PDF 
Option to Skip Printing Cust w Emails
Hide Order Form


Access Developer 29
01. Product Components 1 (15:28)
PartT, ProductXPartT & Q
ProductF, ProductXPartF

02. Product Components 2 (24:33)
Profit = Price - Cost
Margin = Profit / Price
Markup = Profit / Cost
Buttons to Adjust All Three
Algebra to Solve Equation for Price Albegra Calculator
Bring Price Up to 99 Cents

03. FIFO Stock Rotation 1 (37:16)
FIFO: First In, First Out
FEFO: First Expired, First Out
Update UnitT and UnitF
Add DateAdded, ExpirationDate, OrderID
Change Sort: ExpirationDate, DateAdded, UnitCode
Resize ProductF
In Code Ask for Expiration Date
If Specific Unit Scanned, Use It
Warn if Expired
Pick the Oldest Unit NOT Expired, Not on Order
Assign Order ID to UnitT

04. FIFO Stock Rotation 2 (14:42)
Deleting Item from Order
Put Back Into Inventory
I Don't Like Delete Events
OnDelete, BeforeDeleteConfirm, AfterDeleteConfirm
Turn Off Record Selectors
Set Allow Deletions to NO
Manual Warning with MsgBox
Don't Allow Qty > 1 if Serial Numbered Item
Lock Serial Number Field


Access Developer 30
01. Intro to File I/O (18:32)
Why learn File I/O
Types of File I/O: Classic VB & FSO
Pros & Cons of Each

02. TechHelp 1: Import Data (13:12)
Import CSV Files into Access

03. TechHelp 2: Import w File I/O (24:04)
Import Fixed Width Text File
File Sizes Greater Than 2 GB

04. Classic VB File I/O, Part 1 (42:07)
Write a Text File
Open Filename For Output as #1
Print, Close, Append
What Folders you can Write to
FreeFile #FF
Shell Notepad to View File
Create Customer Summary to Email
Loop Thru All Text Boxes on Form
Dim Ctl As Control
For Each Ctl in Me.Controls
ControlType acTextBox
Exclude Fields for Export
Use Recordset to Print Multiple Records
Export an Invoice

05. Classic VB File I/O, Part 2 (43:12)
Reading Text Files
Open Filename for Input as #FF
Reading in Lines of Text
Line Input
Read to End of File (While Not EOF)
Reading in Customer Data File
Emailing Surveys
Copy Paste Responses and Read Into Table


Access Developer 31
01. Navigating Files & Folders (38:52)
File Management with Access
File Picker (PickFile)
Early vs. Late Binding
Use Numbers not Constants
Dir Function
Loop Thru Files in Folder
vbNormal, vbDirectory
GetAttr Function
Move Thru Folders
InStrRev Function
ShellExecute to Open Files
FileLen Function
Format #,##0 Thousands Separator

02. Copying Files, Part 1 (32:34)
Learn the FileCopy Command
Browse and Pick File
Copy to Central Images Folder
Display on Customer Record
Add MyPicture Field to CustomerT
TempVars("PictureFolder") on Startup
Make Sure Folder Exists
FileExists, FolderExists Functions
Create Folder with MkDir
Button to Browse for Picture
Open a Command Prompt CMD
Windows Environment Variables SET
Remember Last Browse Folder Location
FileCopy Command
Put Image Control on Form
Check Size of Files
FileLen Function

03. Copying Files, Part 2 (20:18)
Delete Old Profile Picture if Exists
Kill Command
Verify that File was Deleted
Timestamp Filenames
File I/O Error Handling Concerns
Rename a File with Name Command
ChDrive, ChDir

04. Compact & Repair Backend Files (45:54)
Create Backend ACCDB Folder
Copy BE Files
Create BEFolder Field
Create PickFolder Function
Global Settings Table for BE Folder
Make FileList List Box
Loop Thru ACCDB Files in BE Folder
Use DIR for Loop
Load into FileList
Display FileLen in FileList
Loop Thru FileList 
Create DoCompact Sub
Make Sure Backup Folder Exists
Copy Backups with DateTime Stamp
Perform Compact
Verify Dest File Exists
Delete Old File, Rename New One
Add Error Handling


Access Developer 32
01. Export Report as HTML 1 (11:17)
Part 1: TechHelp
Perform Export

02. Export Report as HTML 2 (14:43)
Part 2: Extended Cut
Create Shortcut to Google Drive Folder
Make Folder Public

03. FTP File to Web Server (16:11)
Explain Command Line FTP
Create a Script File
Perform FTP Operation

04. Convert Currency Part 1 (15:14)
Create Conversion Table
Lookup Value
Do Conversion in Query

05. Convert Currency Part 2 (16:59)
Table for Multiple Rates
Display All Currencies
Customer Currency
Put Conversion on Invoices!

06. Auto Update Rates from Web API (23:15)
Opening Call to URL
Getting ResponseText
FindBetween Function
Updating Rates in CurrencyT

07. Groups 1 (14:48)
Group Field
Simple Wildcard Search

08. Groups 2 (25:06)
Group Table
Pick from List
Filter on Customer List

09. Group Search (35:22)
Search Table
On Delete Issues
Proper Way to Handle Form Delete Event
More Fun with TempVars
Friendly Errors For Duplicate Values
BeforeUpdate, Cancel Event, Undo Changes
ANY Groups
ALL Groups


Access Developer 33
01. Linked Table Manager (9:10)
How to Use Linked Table Manager
Relink Files
Relink Using Same Names
Runtime Error 3024
Runtime Error 3044

02. Relink Single Backend File (18:52)
Check if Tables Linked
Prompt User for Location, One Backend File
TableDef, TableDefs, PickFile

03. Relink Multiple Backend Files (17:54)
Loop Thru TableDefs
Check Each Linked Table
Determine if Table is Linked
Prompt For Specific Backend File
TableDef Attributes

04. Relink ODBC Backend Files (26:37)
Relink SQL Server Tables
Setup LinkedTableT
Function to Link to SQL Server Tables
ODBC Connection String
Modify Function to Test Link
Use dbOpenSnapshot
Load in Timer Event not OnOpen


Access Developer 34
1. XXX (00:00)


Access Check Register Seminar
Lesson 1. Basic Check Register (22:43)
Check Reg Table Design
Query Calculate Amount
Cleared Amount
Check Register Form
Footer Totals

Lesson 2. Running Balance (13:08)
Check Register Report
Running Sum
Query Running Sum
DSUM Function
Running Sum in Form

Lesson 3. Printing Checks (35:49)
Print Single Check
Currency to English Function
Find Next Available Check Number
Hide Print Check Button on Non-Check Items
Has Been Printed

Lesson 4. Batch Printing Checks (22:16)
Batch Print Check Form
Mark To Be Printed
Calculate Amount to be Printed
Update Checks Mark Printed
Select/Unselect All Buttons

Lesson 5. Payees & Categories (37:58)
Main Menu Form
Payee Table & Form
Category List
Add Address Info to Checks
Can't Edit Cleared or Printed Records

Lesson 6. Multiple Accounts (45:11)
Set Up Accounts
Account Summary on Main Menu
Open Single Account Register
Open to Single Account, Default
Batch Print Checks One Account

Lesson 7. Misc Enhancements (51:10)
Debit OR Credit, Not Both
Conditional Format
Cleared Filter Triple State Checkbox
Filter By Date Text Boxes
Sort AZ Buttons
Hide Batch Print Button if No Checks to Print
Select 1-Up or 3-Up Checks
Show Checks To Print on Menu

Lesson 8. Reporting Part 1 (23:15)
Report Menu
Expenses by Category by Dates
Account, Date Filters
Classic Chart Object
Modern Charts
Expenses Pie Chart
Pie Chart Properties

Lesson 9. Reporting Part 2 (56:28)
Expenses by Category by Date Report
Sorting and Grouping Levels
Adding Group Footers & Totals
Report Build Events
Changing Captions Dynamically
Date Printed, Page 1 of X
All Transactions Report
Running Sum Property


Access SQL Server Online Seminar
0. Introduction (8:34)

1. Overview (13:35)
Why SQL Server Online
Pros and Cons
What You Need
What They Need

2. Setup WinHost Account (21:03)
Create an Account
Setup or Transfer Domain Name
Site Control Panel
Create SQL Server Database
Get Connection String

3. Connect Access to SQL Server (23:44)
SQL Server Management Studio
Install SSMS
Connect to SQL Server
Mention SQL Server Migration Assistant
ODBC Data Source Connection
Machine Data Source
File Data Source
Publish from Access to SQL Server
Export CustomerT to SQL Server
Verify in SSMS that dbo.CustomerT Exists
Link to CustomerT from Access

4. Querying Data on the Server (32:19)
Export and Link to Remaining Tables
Import Data in SSMS Seldom Works
Differences Between Access SQL and SQL Server
TSQL or Transact-SQL
Single Quotes for Strings and Dates
1/0 for True/False
Creating a Pass-Through Query
Pass-Through Queries are Read-Only
Rewrite Query SQL Statement On The Fly
QueryDefs Collection
Create a Pass-Through Query
Store Connect String in TempVars

5. Relinking TableDefs, ADO Recordsets (39:06)
Relink Tables in VBA
TableDefs Collection
ADO Recordsets
Changing the Server DB Password
Problems with Initially Loading Tables
Ping a Pass-Through Query On DB Load

6. Security Part 1 (28:41)
Lock Down the Database
Delete PassThru Queries
Linked Tables are OK
Hide Navigation Pane
Minimize the Ribbon
Disable Bypass Key
Admin Menu
Admin Password InputBox
Make ACCDE File
Move to Different PC and Test
Delete PassThru on Shutdown

7. Security Part 2 (20:15)
Make Linked Tables Hidden
Make PassThru Queries Hidden
Read Only Problem
Create Indexes for Read Write
Test on Second PC

8. SSMS Query, Alter, Backup (13:40)
Querying Data on SSMS
ALTER TABLE to Add Fields
Relink on Changes
Editing Fields in SSMS
Identity Specificaion (AutoNumber)
Database Backup Options

9. Display Data on Web Using ASP (31:28)
Download and install EditPlus
Why ASP and not ASP.NET
Connect to SQL Server with ADO
Display Customer List
Display Specific Customer

10. Security Part 3 (14:22)
Access Caches Connections
Don't Store Passwords in Queries
Pass-Thru Benefits


Access Work Order Seminar
0. Introduction (10:49)

1. Plan Database (11:33)

2. Customer Tables (11:58)

3. Customer Form (11:20)
Make Customer Form
Locations List Box
SQL RowSource
Custom WHERE Condition
OnCurrent Event

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

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

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
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

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

29. Categories (11:42)
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)
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
Create a Recordset
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)
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)
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 Barcode Inventory Seminar
A. Introduction (9:17)

B. Welcome (4:49)

C. Database Setup (22:58)
Setting up the Database
Customer Table
Customer Form
Customer List Form
Main Menu

D. Preferences (4:42)
Access Options
Trusted Location
Client Settings
Action Queries

E. Invoicing 1 (64:15)
Create Order Entry System
Order Form
Order Details Subform
Printable Invoice

F. Invoicing 2 (58:35)
Order List
Refresh Quantity, Price
Copy Address Info
Product Select Box

G. Recordsets 1 (19:36)
What is a Recordset
Creating a Recordset

H. Recordsets 2 (32:00)
Display Fields from Table
Look Thru Records
Value List Unbound List Box
Add Manual Rows to List Box
Add Rows from Recordset
Delete Single Items

I. Rentals 1 (56:55)
Daily Event Back in Inventory
Scan Out / In Rental Items

J. Rentals 2 (45:50)
Access Options
Trusted Location
Client Settings
Action Queries

K. Inventory Out (40:02)
Add QtyOnHand to Products
Create ProductHistoryT
Add IsShipped to OrderT
Lock Order Once Shipped
Make Ship Order Button
Check QtyOnHand First
Remove from Inventory

L. Barcode Scanning 1 (29:07)
Learn Types of Barcodes
1D vs 2D Barcodes
Create Product Table
Barcode Field
Form to Scan Into

M. Barcode Scanning 2 (34:21)
Scan Products Directly to Order
Increment Quantity if On Order
If Not in Database, Google Product

N. Barcode Printing 1 (19:27)
What is Code39
Install a Free Barcode Font
Create Custom Labels
Product Barcodes

O. Barcode Printing 2 (18:52)
Print Multiple Barcodes
Print Button on Product Form
Add to Label Table
Clear Labels Button

P. QR Codes 1 (15:34)
Google Charts API
WebBrowser Control
Display QR Codes in Forms

Q. QR Codes 2 (28:01)
Download QR Codes from Web
Save as Local File
Display and Print in Reports

R. Merge Barcoding (27:27)
Bring Barcode Stuff to Developer DB
Copy Scan Textbox
Create Product List Form
Synchronize Two Open Forms
Create Product Form
Product Barcode Lookup Box

S. Add Remove Manual Inventory (19:48)
Combo Box to Add, Subtract, Lookup
Changing Quantities
Logic to Prevent Negative Inventory

T. Serial Numbers (43:54)
Unit Table, Form
Adding Multiple Units
Create Label Report
Bug Found in Label Wizard
Print Labels Button
Mark Labels as Printed

U. Serial Numbers in Orders (35:29)
Look Up Serial Number OR Product
Scan Part or Product Onto Order
Don't Increment Qty if Serial Number
Check to See if On Another Order
Print Barcodes on Invoices

V. Merge Barcode Printing (15:45)
Add Labels for Any Number of Products
Print Labels, Mark Printed

W. Dymo Labelmaker 1 (19:20)

X. Dymo Labelmaker 2  (11:24)

Y. Conclusion (2:06)


Access SharePoint Seminar Outline
Lesson 1. Requirements
What you can do
What SharePoint is good for
What SharePoint is NOT good for
What YOU need
What THEY need

Lesson 2. Create Sharepoint Site & List
Create a New SharePoint Site
Create a SharePoint List for Customers
Add and Edit Data
Hide Title Field
Make Title Field Not Required
Display ID Field

Lesson 3. Connecting Access to SharePoint
Connect to New Site from Access
External Data > New Data Source >
From Online Services > SharePoint List
Log in to SharePoint in Access
Export CustomerT to SharePoint
Back Up Your Data!
Link to CustomerT On SharePoint

Lesson 4. Permissions & Distribution
Giving Others Access to your SharePoint
Granting Permissions to a List
Sending Invites
Your End User Creating a Microsoft Account
Compact & Repair
Distribute ACCDE File
Creating a ZIP File

Lesson 5.
Seeing Who Edited / Modified Records
Hidden Fields in Table
Display CustomerT on Quick Launch
Removing Permission
Requiring Permission to Change


Old Access Index - 2003 - 100s
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
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

4. Search Sort Filter
Find & Replace
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#)
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
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
Sample Database Files

2. Contact Table
What is a "Contact"
Using the customer notes field for contacts
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?

Old Access Index - 2003 - 200s
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

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
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
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


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


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
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


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

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
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

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 to
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
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
WHERE clause
Placing SQL statements in combo box Row Source property
ORDER BY clause
UNION Query - can only be done with SQL

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


Old Access Index - 2003 - 300s
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
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
Move to a new record in VB code
Accessing data from multiple columns

When should you use DLOOKUP
DLOOKUP to get values from tables
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
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
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
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

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
Custom Sub with Parameter

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

Lesson 3. Dynamic Combo Boxes Part 2
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

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

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
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

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

6. Tracking Payments, Part 2 - 15:19
Warning users not to make a payment if invoice is PAID
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?
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
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
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
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

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
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![First Name]
Create Unbound Text Boxes
Store RS Data Into Text Boxes

3. Moving Around, Part 1
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
Types of Recordsets

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


Access 321


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

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

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

4. Multi-Select List Boxes
Simple v. Extended
Loop Through Items With ListCount
.Selected Property
For Each
Object Collections

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

6. Execute


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

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

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
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
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
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
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

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

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

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
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

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

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
Microsoft Outlook Email Security "Feature"

Old Access Index - Seminars
Access Seminars
Access Web Data Sync Seminar


0. Introduction (14:17)
Topics Covered
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

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

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

6. Insert Data With Recordset (9:29)
setup Recordset
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
Public Sub LoadURL
Integer Division and Modulus
While Wend Loop

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

10. Checking Stock Quotes (8:45)
Copy Web Form
Me Keyword
Edit VB Code for Stocks
Using 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
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

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
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
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)
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

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
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
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

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


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
Control Box
Close Button
Max Min Buttons
Pop Up


Access Split Database Security Seminar


00. Intro (5:01)

01. Why Split Your Database (5:07)

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

03. Customer Forms (11:36)
Button to Open Secure Form
Without Security
VBA DoCmd.OpenForm
Add a Password
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
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)
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
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
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
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
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
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

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
Dim Variable
Reset / Stop Code
If Then

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

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

14. SQL RecordSource Form (14:40)
Build SQL Statement from Scratch
WHERE Clause
Add a Sort Order
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
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

19. Recent Customer List 1 (17:43)
Listbox for Recent Customers
On Error Resume Next
Simple Debugging

20. Recent Customer List 2 (18:03)
Open Selected Customer
OnDblClick Event
On Double Click
See if Customer Exists in Table
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
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
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

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
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)
Where Condition
Report Events

35. Review (3:21)


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
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
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
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
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

36. Recurring Bills 1 (12:55)
Recurring Bills Table
Frequency Table
Calculate next due date by frequency
NextDueDate public function
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
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

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)
Multiple fields

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

07. Name List Form (11:23)
Form that gets data from 2 tables
Show Customers
Show Employees
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
Updating multiple fields

02. Update Query 2 (10:27)
Update Query with Multiple Tables
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
Run update query to perform markup
Format function

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

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

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

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

09. Top X Items 1 (13:35)
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

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

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

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

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

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

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

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

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

19. Aggregate Queries 1 (13:17)
Total Row
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
StrConv for proper case

22. Functions 2 (15:16)
Date & Time Functions
Format function
Format parameter values

23. Functions 3 (11:36)
Misc Functions

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

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

26. Review (4:18)


Access SQL Seminar 3


00. Intro (5:02)

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

02. Access SQL Data Types (8:30)
Access to SQL Aliases
Start COUNTER at different value
Inflate AutoNumber value
SQL view in query designer

03. Indexing (14:32)

04. Altering Tables (16:12)
Altering tables in EXTERNAL databases
Cannot alter LINKED tables
Dim DB as database
OpenDatabase command
Adding tables to external database
CREATE link to external table

05. Constraints (10:49)
Multi Field Index
Index spanning 2 or more fields

06. Relationships (9:37)
Relationships review
Relationships Window

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

08. CHECK & DEFAULT (11:24)
Multi Field CHECK Constraint
Table-Level Validation Rules

09. Referential Integrity (6:16)

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

11. Database Security (11:44)
Open Exclusive Mode

12. Review (5:01)


Access Relationship Seminar


00. Intro (7:59)

01. Types of Relationships (13:04)
No Relationships
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)
Extra Student Info
One-To-One Global Relationship
Command Button to Open 2nd Form
Show Specific Records
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
Product Combo Box
Vendor Combo Box
On Double Click Event
VBA Code to open a form
Where Criteria

10. Self Join One-To-Many (17:00)
Relating a Table to Itself
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
Do not allow additions to subform
Unbound forms
Control Source
Unbound Combo Boxes
Turn off Navigation Buttons
Record Selectors
Scroll Bars
Set value across forms

13. Reverse Relationships 2 (22:44)
AfterUpdate Event
DLOOKUP Function to find Opposite
Append Query
Exit Sub

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
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
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
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
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
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
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)
Error Handling
On Error Resume Next
On Error Goto 0

14. Email from HTML or Web Page (10:37)
Create a Newsletter in Microsoft Word
Access Learning Zone Newsletter
Save as HTML Filtered
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
While Loop
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
DoError Error Box

18. Bulk Customized Email 4 (19:28)
Email Countdown Timer
Send Delay
OnTimer Event
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
Custom From Address for Each Template
RecordSource Field vs Property
SQL SELECT Statement
Dirty Records

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

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
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
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
GetDefaultFolder olFolderInbox
For Each Item in Inbox
Save Messages to Table in Access
Subject, Sender, SenderEmailAddress
Body, HTMLBody, BodyFormat
olFormatHTML, olFormatPlain
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
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

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

30. Review (4:30)


Access Open Other Programs Seminar


00. Intro (7:18)

01. Display External Images (17:29)
Create Customer Table
Setup File Attachments Folder
Copy Images to Folder
Create Customer Form
Display Customer Image on Form
Multiple Attachments Per Customer
Setup Attachments Table
Display File Extensions
Hide Extensions for Known File Types
Attachments Subform

02. Shell Command (23:33)
Command Button Build Event
SHELL Function
System Path Variable
Command Prompt
Determining What Program Opens a File
On Double Click Event
Dealing with Spaces in File Names
Launching Internet Explorer
Command Line Arguments Switches

03. ShellExec Command (20:57)
ShellExecute Function
Create the ShellExec Sub
Code Sample Table and Form
Choose Default File Handler
Open Web Page with ShellExec
Open Google Maps to Customer Address
Build Google Maps URL
Replace Function
Open Folder with Windows Explorer
Sending Email with Mailto

04. ShellExec Command Advanced (7:24)
Private and Public Constants
Error Handling
Other OPEN type arguments

05. Review (2:35)


Access DateTime Seminar


00. Intro (11:41)

01. Number of Work Days 1 (20:19)
Excel NETWORKDAYS Function
Create a Module
Reference to Microsoft Excel 15.0 Object Library
Create myNetWorkDays Function
Remove the Excel Reference and Code

02. Number of Work Days 2 (21:05)
While Loop Date Counter
Count Number of Days Between Dates
WeekDay Function to Determine Day of Week
Holiday Exclusion Table
DLOOKUP to Check Holiday Dates
Dealing with #Error problem

03. Number of Work Days 3 (22:20)
Using DCOUNT Outside Loop for Speed
Validation Rule to Prevent Sat Sun in Table
IsWorkDay Function to Check One Date

04. Timestamp on Edited Records (16:40)
When was Customer Record Last Updated
Create Customer Table and Form
Use Default Value to Track Creation Date
Record Change to a Single Field
Record Changes to Multiple Fields
Field AfterUpdate Event
Record Changes to Entire Record on Form
Form OnDirty Event
Tracking Changes at the Table Level
Table Data Macros
Create Data Macro
Before Change Event
SetField Command

05. Reminder Popup Notifications 1 (27:32)
Create Reminder List Table
Reminder Text, ReminderDateTime, Closed
DLOOKUP to Check for Reminders
Closed=FALSE and Reminder Now or in Past
Conditional Formatting to Highlight Due Reminders
Checking for Reminders Automatically
OnTimer Event
Timer Interval Milliseconds
Constant MAXVAL
Countdown Loop for Timer
IsPaused Checkbox

06. Reminder Popup Notifications 2 (22:57)
Open Reminder Form if there are Reminders
Special Popup Reminder Form
Preventing the Popup from Interfering with Typing
SetFocus Method
Set TimerInterval to Zero to Disable
You have 3 Reminders
DCOUNT to Count Number of Reminders
Label.Caption Property
Button to Show Reminder Form
Check to see if Reminder Popup Form is Open
IsLoaded Property

07. Reminder Popup Notifications 3 (29:41)
Remind Me Later Button
Remind Me in X Minutes
Access Commands Not Available Bug!
Have the Timer Pause Itself if On Reminder Form
OnActivate Event
Have the Timer Restart When Leave Form
OnDeactivate Event
Prevent User from Closing Reminder Form
Close Button Property
Buttons to Move Reminders +5 min, +1 hour, +1 day
Move Reminder to Tomorrow at 9am
Docmd.GotoControl to Move to Next Record
Hide Closed Reminders

08. How Many X Days in Interval (32:00)
How many Mondays are in This Month
Combo Box with Weekdays
Separate Month, Day, Year Values
DateSerial Function to Build Dates
First Day of Month
Last Day of Month
Loop Through Days
Code Breakpoints
CLng Convert Text to Long Int
Convert Code to a Function
Specify our own Interval (month, quarter, year)
How Many Tuesdays This Year
How Many Fridays This Quarter
Select Case Statement
Count from Target Date until End of Period

09. Calculating Holidays (34:33)
United States Holidays
Independence Day - July 4th
Christmas Day - Dec 25th
Labor Day - 1st Mon Sept
Memorial Day - Last Mon May
MLK Day - 3rd Mon Jan
Presidents Day - 3rd Mon Feb
Thanksgiving - 4th Thur Nov
Daylight Saving Time Begin - 2nd Sun Mar
Daylight Saving Time End - 1st Sun Nov
Veterans Day - Nov 11, +/- 1 day
Easter Sunday - Complex Calculation

10. Recurring Appointments 1 (47:09)
Add Frequency and Recurring to Reminder Table
Days, Weeks, Months, Years
Last Completed Date
Done Button
Move Recurring Appointments Ahead
Close Non-Recurring Appointments
Filter List of Appointments by Dates
Custom SQL Recordsource
Add Where Conditions
Show Closed, Open, Both
AfterUpdate Event

11. Recurring Appointments 2 (15:33)
Add Custom Sort Order
Custom Date Range Buttons
Back and Forward One Week's Appointments
See All Appointments Not Closed

12. Ordinal Dates (3:47)
MyOrdinal Function
Display 1st, 2nd, 3rd, 4th, etc.

13. Review (6:59)


Access Asset Management Seminar Part 1


00. Intro (7:55)

01. Setup Core Tables (27:10)
Query for Entities Based on Available Info

02. Setup Core Forms (26:10)
Double Click to Open EntityF
Search Box in List Form
Enter Assets in Table
Assign ParentID
Create AssetF
Make Combo Boxes

03. Asset List & Child Subform (35:09)
Create Asset List Form
Create Child Subform
Click to Open Selected Asset
Recolor Fields Based on Values
Hide Owner, Location for Children

04. History Log (16:58)
Track Changes to Any Asset
Note Field Values in Log Table
Create LogIt Subroutine

05. Topics for Part 2 (10:39)
List of Topics to Cover Next

06. Review (5:09)


Access Amortization Seminar


0. Introduction (10:17)

1. Lesson 01 (22:49)
Initial setup
Making the Tables
Loan Form

2. Lesson 02 (21:04)
NumberOfPayments Calculator
Tab Order
PMT Function
Creating the Schecule Form

3. Lesson 03 (22:05)
Delete any previous Schedules
Create a new Schedule

4. Lesson 04 (24:44)
Summary Totals
Adding a Subform
Check for changes
Add a Payment
Recalculate other figures

5. Lesson 05 (22:46)
Recalculating Payments
Adding the Payments SubForm
Master/Child Fields

6. Lesson 06 (22:14)
Adding a Payment
Loan Status Values

7. Lesson 07 (18:18)
ClientF, ProviderF
Main Menu
Listbox of Loans
Default Values

8. Lesson 08 (22:34)
Fixing a Requery Problem on new Loan
Echo to stop Screen updating
Delete a Record
Stop Warnings

9. Lesson 09 (24:27)
Repeat for the ProviderListF
DoubleClick Events to open Forms
Active Loans

10. Lesson 10 (21:11)
Format Painter
Size/Align to Grid
Force New Page
Master/Child Fields

11. Lesson 11 (29:05)
Report Headers
Payments SubReport
Relationships 101
Sorting and Grouping

12. Lesson 12 (27:26)
Accounts Receivable

13. Lesson 13 (31:12)
Accounts Receivable Reports
Hiding Report Sections

14. Lesson 14 (25:13)
Current Loan Value Report


Access Registration Seminar Part 1


00. Intro (3:42)

01. Lesson 1 (10:46)
Registration Discussion
Security Topics
Startup Form
Form Open Event
The Windows Registry

02. Lesson 2 (10:26)
GetSetting from Registry
SaveSetting to Registry
MsgBox Command
InputBox Command
Quit Command

03. Lesson 3 (11:45)
Random Number Generator
Checking Counter Key

04. Lesson 4 (8:42)
Timing Out Registration
Annual Subscriptions
DateAdd Function

05. Review (5:26)


Access - index
Excel - index
Word - index
Windows - index
PowerPoint - index
Photoshop - index
Visual Basic - index
ASP - index
Account Login
Online Theater
Lost Password
Student Databases
Change Email
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Search The Site
Code Vault
Collapse Menus
Customer Support
Web Site Tour
Consulting Services
Affiliate Program
Richard Rost
Free Lessons
Mailing List
Video Tutorials
Learning Connection
Idiot's Guide to Excel
Volume Discounts
Payment Info
Terms of Sale
Contact Info
Support Policy
Email Richard
Mailing Address
Phone Number
Fax Number
Course Survey
Blog RSS Feed    Twitter

YouTube Channel    LinkedIn