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 303
Description: Advanced Access
Running Time: 77 minutes
Pre-Requisites: Access 302 very strongly recommended
Previous Lesson: Access 302
Next Lesson: Access 304
Main Topics: Order List, On Double Click Event, Bill To Ship To, Public v Private Subs
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 11/24/2019 to get a FREE upgrade to our Access 2010 version!
Click here for details

 
Microsoft Access 303
Advanced Access Development

Order List Form, On Double Click Event, Bill-To, Ship-To Addresses, Public & Private Subroutines, Missing Orders, More. 77 Minutes
 

AC303 Major Topics

  • Order List Form
  • On Double Click Event
  • Bill-To, Ship-To Addresses
  • Public v. Private Subroutines
  • Missing Orders

Access 303 continues our advanced Access database development with VBA programming. We continue work on our sales and order-entry system.

We will begin by creating a form to show all orders, and all orders for a specific customer.

 

Next you will learn how to deal with a problem that comes up if the user tries adding a product when there is no order yet. We'll also see how to force the user to select a sales rep first.

 

We will next learn how to double-click on the customer name to open up that customer's record using the On Double Click event. I like to make these kinds of fields blue. We'll also make a double-click event to open a specific order too.

 

In previous Access classes, I taught you that you should almost never store duplicate data in your database - like storing the customer's address on every order since you can just look that up from the customer table. Well, here's one exception: when you want to track history. For example, when this order was shipped, it might have gone to a different address. In this lesson we'll address that with Bill To and Ship To addresses for each order.

 

You can make the addresses different, but I'll show you how to copy them from one to the other if one is blank (the ship-to address will default to the bill-to unless you change it).

Next we'll use our DLOOKUP function from the last class to lookup the billing and shipping addresses from the customer table and automatically fill them in to the order form when a customer is selected.

 

It's starting to look like a real order-entry system now.

 

Next you'll learn how to create your own subroutines in VB code so that you can reuse code without duplicating it. You'll learn about public and private subs and what the difference is.

 

You'll learn how to call subroutines from inside your code, and how to access public subroutines from a different form. Very cool stuff.

 

Next we'll see how to deal with a problem when your user doesn't select a customer for an order. You need to adjust your query join types to show ALL orders, and matching customer records if they exist.

 

Access 303 Outline
 

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

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

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

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

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

 


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