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

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.

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