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
- Order List Form
- On Double Click
- Bill-To, Ship-To
- Public v.
- 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
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
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.