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
 

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

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

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

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

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

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

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

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

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

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

2. Main Menu, Part 1
Creating a form with no data
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?
 

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

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

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

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

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

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

1. Introduction

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

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

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

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

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

7. Review
 

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
 

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

7. Review Topics
 

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

6. Review
 

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
 

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

Lesson 6. Review
 

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
 

Advanced Query Techniques, Sales Log Table & Query, Calculated Query Fields, Sales Log Form, Inner & Outer Joins, Cascade Deletes, Global Relationships, Referential Integrity, IIF, Round function, IsTaxable, Form Footer Totals, SUM Function, Query Joins: Inner Join, Outer Join

Aggregate Queries, Sales by State, Contact List, Sales Report, Work Log Table & Form, Time Sheet Form & Report, Query Grouping, Report Footer Totals, SUM function, calculating number of hours between two date/time fields, Employee Timesheet

Action Queries: Update Query, Append Query, Delete Query, and Make Table Query, Automating Queries with Macros

Crosstab Queries, Crosstab Query Wizard, Query Properties, Query Parameters Window, Begginer SQL, Find Duplicates Query, Find Unmatched Query, Top X Values, Unique Values, Unique Records, Column Headers, Output All Fields

Order Tables (OrderT, OrderDetailT), OrderQ, Sales Tax, Tax Rate, Order Form, VBA, Order Subform, Form Footer Totals, Open Button VBA

ProductT, ProductF, Product Combo, Getting data from a combo box into a form using the Column property, DLOOKUP, VB Concepts.

Creating the OrderListQ, OrderListF, On Dbl Click Event, DoCmd.OpenForm, Adding BillTo, ShipTo Addresses to Orders, Address Fields, Creating Your Own Subroutines, Public v. Private Subs, Missing Records due to Join Types, Get Sales Tax Rate , AfterUpdate event

Quote or Invoice? Changing form properties on the fly. OnCurrent event, running code moving from record to record, Invoice Query, Report, Force New Page, tidy up report, Report Setup, margins, VB section build events, image sizing, IsPaid Label, can grow/shrink, page footer, docmd.openreport

Accounts Receivable, Aging of Accounts Receivable, (Showing how many days late), AR Report, Percent discounts on orders, Msgbox with vbYesNo, Docmd.OpenQuery, Me.Refresh, BeforeUpdate event, Canceling a record update before the data hits the table, vbYesNoCancel

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.

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

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

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

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

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

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

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

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

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

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

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

0. Introduction - 4:33

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

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

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

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

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

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

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

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

9. Review - 5:27
 

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

7. Review - 2:26
 

0. Introduction - 5:29

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

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

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

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

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

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

7. Review - 4:07

0. Introduction - 2:56

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

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

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

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

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

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

7. Review - 4:43