Free Lessons
Fast Tips
Topic Index
Home   Courses   Index   Templates   Seminars   TechHelp   Forums   Help   Contact   Join   Order   Logon  
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.

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



The following is a paid advertisement
Computer Learning Zone is not responsible for any content shown or offers made by these ads.

Access - index
Excel - index
Word - index
Windows - index
PowerPoint - index
Photoshop - index
Visual Basic - index
ASP - index
My Account
My Courses
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
Mailing Address
Phone Number
Fax Number
Course Survey
Email Richard
[email protected]
Blog RSS Feed    YouTube Channel

Copyright 2024 by Computer Learning Zone, Amicron, and Richard Rost. All Rights Reserved. Current Time: 6/15/2024 7:11:02 AM. PLT: 0s