Access 2007-2019
Access 2000-2003
Access Seminars
TechHelp Support
Tips & Tricks
Access Forum
Course Index
Topic Glossary
Insider Circle
 
Home   News   Tips   Glossary   Templates   Forums   Help   Logon   Order   Contact Us  
 
 
 
Courses - Microsoft Access 301
Description: Advanced Access
Running Time: 74 minutes
Pre-Requisites: Access 223 very strongly recommended
Previous Lesson: Access 223
Next Lesson: Access 302
Main Topics: Order Entry, Sales, Line Items, Sales Tax, Beginner VBA, OpenForm, Continuous Forms, Subform Totals
Versions: This course is valid for Access 2000 through 2003. If you are using Access 2007 or 2010, you will still benefit from this course. You will find that the concepts are the same, but there are cosmetic differences between the versions.

    

Order before 9/17/2019 to get a FREE upgrade to our Access 2010 version!
Click here for details

 
Microsoft Access 301
Advanced Access Development

Order-Entry System, Orders & Line Items, Calculate Sales Tax, Form/Subform, Subform Totals, Beginner VBA, Lots More. 74 Minutes.
 

AC301 Major Topics

  • Order-Entry System
  • Create Order Tables
  • Line Item Information
  • Sales Tax Rate
  • Order Form / Subform
  • Beginner VBA Programming
  • DoCmd.OpenForm
  • Continuous Forms
  • Subform Totals

Access 301 is the first of our "developer level" advanced Access courses. We will build a complete order-entry system (sales, products, inventory, accounts receivable, etc.) We will also learn VBA programming (Visual Basic for Application).

This is the course you've been waiting for! Don't miss it. Our goal is to create a full-featured order-entry system with printable invoices.

 

In this class, we will begin by constructing the Order Table and Order Details Table. The Order Table holds the information about the overall order, whereas the Details contains information about the items (products) in the order (a many-to-one relationship... remember that?)

 

We'll enter some sample order data so you can see how the relationship between OrderT and OrderDetailT is going to work.

 

We'll see how to create a query to calculate our line totals, sales tax rate, actual sales tax due, and more calculations.

 

Next we will create an Order Form. We'll start out by adding Sales Rep and Tech Rep combo boxes to our form - but these are both basically Employees, so we'll bind two different fields to our Employee table.

 

We'll get our introduction to VBA (that's Visual Basic for Applications) which is where the real power behind Microsoft Access is hidden.

 

Next we will create the Order Subform to show the line item details for each order:

 

You will learn how to put Totals in your subform's footer.

 

Next we'll set a default Sales Rep and Tax Rate for each Customer...

 

...and have that information automatically carry over to any new orders that are created for that customer:

 

This course is just the first in our series of creating an order entry system. By the time we're finished, you'll have a fully-functional database that you can use to run any business.

 

Access 301 Outline
 

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

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

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

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

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

 

 


Try a FREE Demo Lesson

 
 

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

 

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

Google Plus    LinkedIn

Blog RSS Feed    YouTube Channel
Richard Rost Microsoft MVP