|
This class continues our look at programming in Visual Basic for
Applications in Microsoft Access (VBA, or Access VB). We will
begin by adding notes (long text) to our order details. We can have
notes for the order as a whole, but not for each line item. This
information is stored in the product table. We will use DLOOKUP
to pull it when we add the item to the order, and allow the user to edit
it.
We will learn how to use
the OnDblClick (on double click) to allow the user to
make the field larger to see and edit the notes better. We'll modify the
object's height and width properties. We will learn about
variable scope - how certain variables only work inside a
particular procedure, but others can work over an entire form as long as
it's open.
We will then add these notes to the printed invoice as
well.
Next we will go back and
work on our employee commissions form. Before we just
had it so that the manager could type in an employee, a date, an amount,
and log some commission. Now we will actually tie those commissions into
the order system, so that you select an employee from a combo box, pick
an order, and then the system will automatically calculate the
commission amount and then track which orders have had the commissions
paid.
We will start by setting up brackets for
each employee so that the commission rate can be
variable based on the amount of each order. So, for
example, if employee 3 has an order that's $90 then she gets paid a 3%
commission. However, a $350 order will get a 4% commission.
Then, we'll make it so
that on the commissions form, you select an employee,
then a list of their unpaid (commissions) orders will show up. Click an
order and the data is all automatically calculated. Click the LOG button
to save the info in the commissions table, and the list updates.
This is the 4th class in the Access Developer series. If you're
serious about building quality databases with Access, and you want to
learn how to automate your database with VBA Programming then don't miss out on
this course. Of
course, if you have any questions about whether or not this class is
for you, please contact me.
Complete Outline - Access
Developer Level
4
00. Intro (3:09)
01. Add Notes to Order Details (22:15)
DLOOKUP Long Text Memo
Notes Field in OrderDetailT
Add to
OrderDetailF
Make Size Change in OnGotFocus
Height, Width
Properties
Change Detail Section Height
Variable Scope -
Sub vs Form
Form Scope Variable to hold Height, Width
OnDblClick Event to Grow
02. Commissions by Order Amount 1 (22:33)
Modify CommissionT
Base Commission on Order
Employee
Commission Table
Set up Brackets for Commission Ranges
Fix
Sales Rep Combo Box
Query to Link Orders, Commissions, Cust
Order Picker Combo Box |
03. Commissions by Order Amount 2 (43:50)
Change Order Combo to List Box
SQL Statement to Load List
Limit by Sales Rep
Query Employees w Unpaid Commissions
Limit Employee Combo to Unpaid Only
Lookup Order Total for
Selected Order
Look that up in Commission Table
Log to
Table with SQL INSERT INTO
04. Review (4:06) |
|
