This class continues our look at programming in Visual Basic for
Applications in Microsoft Access (VBA, or Access VB). We will
begin by creating a commission check table to store employee
commissions. A manager will select an employee from a combo box that
shows only employees that have commissions outstanding that need to be
paid.

Next a list of the orders
that the employee still needs to receive commission for will show up in
one list box. The manager can then click on them individually and move
them over to a second list box (approved). This is as close as you can
get to simulating "drag and drop" in Access without the
need for third-party external libraries. We'll also calculate totals for
each box.

We will next process the list of approved
commissions by adding them to the Commission Check
table, putting multiple items into a single check. We'll do this with a
few SQL statements.

Now we'll begin working on
our printable check report. We will first need a function to
convert a currency value like $105.25 to "one hundred five
dollars and twenty five cents." We'll use a function for that, which
will be our first use of a Global Module. We'll see how to look up the
largest check number in the table to add one to it for the next one.

Finally, we'll actually
format and print the checks. I'll talk about easy ways
you can go about doing this without wasting a ton of time (or blank
checks).

This is the 5th 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
5
00. Intro (3:58)
01.
Pay Commissions 1 (15:21)
Add Commission Check ID to Com
Table
Create Commission Check Table
Commission Select Query
Commission Check Employee Query
Commission Check Form
Combo Box to Select Employee
02.
Pay Commissions 2 (20:38)
Commission List Box
Is Selected
Copy Between Two List Boxes
Drag and Drop Discussion
Click and Drag
Create ComList2
SQL UPDATE to Select Items
Totals for each List Box
DSUM Function |
03. Pay Commissions 3 (20:17)
MsgBox vbYesNoCancel vbYes
SQL INSERT Check into Table
Add EmployeeID to CommissionT
Database Theory v Practice
Update CommissionT for Changes
04.
Printing Checks 1 (15:23)
Convert Currency or Number to
English
SpellNumber Function
Create a Global Module
Look up Largest Check Number in Table
Create New Check Number
05.
Printing Checks 2 (20:30)
IsPrinted
Create Check Report
Blank Laser Printer Checks
Process Current Check
Print All Checks
Mark Printed
DoCmd.OpenReport acViewPreview
OnNoData Report Event
Mark ALL Commissions Selected
06.
Review (4:27) |

|