Introduction

Welcome! Move List Items & Print Checks

S M L XL FS | Slo Reg Fast 2x | Bookmark

Welcome to Microsoft Access Developer Level 5. In this course we will continue building on skills from Level 4, including how to pay and print commission checks, move items between two list boxes in a form, and convert currency values to text for check printing. We will discuss creating and using a pay commissions form, working with list box item selection and movement, and developing reports to print checks with dollar amounts written in English. We will also cover the importance of upgrading Access versions and reference the structure of the beginner, expert, advanced, and developer series.

Navigation

Prev: D05
Next: Lesson 01

Keywords

Access Developer, pay commissions, print checks, list box item transfer, convert currency to text, spell number function, global module, Visual Basic programming, commission check form, report dollar amount in English, employee commissions

Subscribe to Introduction
Get notifications when this page is updated

Intro

Transcript

Welcome to Microsoft Access Developer Level 5, brought to you by AccessLearningZone.com. I am your instructor, Richard Rost.

Today's class is going to continue what we learned in Level 4. We are going to learn how to pay those commissions that we started tracking in Level 4. We are going to learn how to move items between two list boxes. We will have list 1 and list 2. I want to click on an item and have it move over to list 2. Almost like a click and drag kind of thing, but Access does not support click and drag, so I am going to show you another technique.

We are going to learn how to print checks. To do that, we have to be able to convert currency into text, so if it says $100.06, we have to be able to spell that out in English.

This class follows Access Developer Level 4. You should have completed my beginner, expert, and advanced series, plus developer 1 through 4 before getting to this class.

If you have not taken any of my classes before, there are 9 beginner lessons, and they cover all the basics of tables, forms, and queries, and simple design like that. The expert series works mostly with relationships and into some more higher-level techniques. The advanced series starts getting into event programming in macros, which is the precursor to programming, and my developer series, which is what you are in right now, starts covering Visual Basic programming. Of course, each series is broken down into different levels. Beginner had 9 levels, the expert had 32, advanced only had 6, and developer right now has 5. This is level 5. So go get 1 through 4 if you have not yet, and then we will talk about 5.

I recommend that you build the database that I am building in class, and I recommend you build it yourself from scratch, from lesson 1. But if you have not, or if you have not been keeping up with it, you can download a copy of the database right there, findthatncd.com/databases. You will need your password, the password that you were given when you purchased this class.

I am using Access 2016, but everything should work. I should change it to Access 2007 and through 2013, because 2007 really most of this stuff is the same too. There, that is better. But if you are using 2007, you might want to consider upgrading. Access 2016 is great, it is wonderful, it fixed a lot of bugs. There are not a lot of major upgrades through the versions. Access has been pretty much the same since 2007, but the little bug fixes and incremental things that they have added, those little tiny things really do make a difference in the long run. Unless you are in a company with 5,000 people using it, I would suggest upgrading.

Let us take a more in-depth look at what is covered in each lesson today.

In lesson 1, we are going to begin building our pay commissions form or our commission check form. We are going to make it so we can take a look at all of the commissions that have been approved for each of our employees, mark which ones are going to get paid, and then cut the check for them. It will actually print checks out and stuff. So that is what we are going to start doing in lesson 1.

In lesson 2, we are continuing with paying commissions. Here we will work on our form so that we have a list box on the left. We will click on an item, click on the button, and we will move the item from list box 1 over to list box 2.

In lesson 3, we are still working on our pay commissions form.

In lesson 4, we are going to start to get down to printing the actual checks out for our employees' commissions. We are going to take a look at the spell number function to convert currency to English, and we will create our first global module.

In lesson 5, we are finally going to get to printing our actual check. So we are going to make a report that prints out the check, puts the dollar amount in English, $29.16, and so on.

Thank you.

Quiz

Q1. What is the main new feature introduced in Access Developer Level 5?
A. Paying commissions and printing checks for employees
B. Adding customers to the database
C. Creating pivot tables in Excel
D. Importing data from external sources

Q2. What technique is demonstrated to move items between two list boxes in Access?
A. Click and drag
B. Using VBA to transfer items when clicking a button
C. Copy and paste
D. Double-click list item to move

Q3. Why do you need to convert currency into text in this lesson?
A. To display numbers in charts
B. To display monetary amounts in words on printed checks
C. To export data to Excel
D. To improve sorting in reports

Q4. How many series are recommended that a student should complete before starting Developer Level 5?
A. Three: beginner, expert, advanced
B. Four: beginner, expert, advanced, and developer 1-4
C. Two: beginner and developer
D. Five: beginner, intermediate, expert, advanced, developer

Q5. Where can students download a copy of the database if they have not kept up with building it?
A. AccessLearningZone.com/download
B. findthatncd.com/databases
C. msaccess.com/resources
D. developeraccess.com/files

Q6. What programming language does the developer series begin to focus on?
A. Python
B. SQL
C. Visual Basic for Applications (VBA)
D. C#

Q7. What is one improvement Access 2016 offers over previous versions according to the video?
A. Brand new user interface
B. Major new features every version
C. Bug fixes and incremental improvements
D. Cloud-only support

Q8. What is the purpose of the global module created in lesson 4?
A. To create tables automatically
B. To store the spell number function for converting currency to text
C. To backup the database
D. To import data from Excel

Q9. What is the recommended way to follow along with the course?
A. Watch all lessons first and take notes
B. Read the course transcript only
C. Build the database yourself from lesson 1
D. Look up the solutions online

Q10. In lesson 2, which user interaction is used to move items between list boxes?
A. Double-click
B. Typing the item manually
C. Clicking a button
D. Right-clicking the item

Answers: 1-A; 2-B; 3-B; 4-B; 5-B; 6-C; 7-C; 8-B; 9-C; 10-C

DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.

Summary

Today's video from Access Learning Zone continues the lessons from Access Developer Level 4. In this Level 5 course, I am going to show you how to pay the commissions we started tracking previously. We are going to cover a variety of new techniques, such as working with two list boxes that allow you to transfer items from one to the other. Normally, you might expect to be able to drag and drop between these lists, but since Access does not natively support drag-and-drop, I will demonstrate an alternative approach.

Another major part of this course involves printing checks. For this, we need to be able to convert currency amounts into their English-language equivalents. For example, if you have a dollar amount like 100.06, we will look at how to spell that out in words. This is an important skill when producing check reports.

Before starting this course, you should have completed the earlier series: the beginner, expert, advanced, and developer levels 1 through 4. The beginner courses introduce tables, forms, queries, and basic design concepts. The expert series moves into relationships and more advanced topics. The advanced lessons are where we start to use event programming and macros, which are the foundation for learning Visual Basic for Applications. The developer series, which this course is part of, focuses on actual VBA programming. The beginner series has 9 levels, expert has 32, advanced includes 6, and developer now has 5 levels with this course. If you have not done the earlier developer levels, I recommend you go through 1 to 4 first.

As you go through this course, I highly recommend building your own database as we work through the material. The best approach is to construct it yourself, starting from lesson 1. However, if you have fallen behind or just want to catch up, you can download a copy of the database from findthatncd.com/databases. You will need the password you received when you purchased the class to access these files.

I am demonstrating all these techniques using Access 2016, but most of what we cover will also work in Access 2007, 2010, and 2013. While there have not been dramatic changes between versions, the later versions include some helpful bug fixes and improvements that make development smoother. Unless your work environment depends on a much older version, I suggest upgrading to Access 2016 if possible.

Here is what we will be focusing on in each lesson of this course. In lesson 1, we will begin to build the form to pay commissions. This includes reviewing all of the approved commissions for your employees, selecting which ones you want to pay, and then preparing to issue checks. Lesson 2 continues this process by developing the functionality to move items between two list boxes on the form. You will be able to select an item in the left list box, click a button, and have it transfer to the right list box. Lesson 3 carries on this development, refining the pay commissions form.

In lesson 4, we will start setting up the process to print actual commission checks. We will look at the function that converts currency values into words, and also create our first global VBA module to manage this functionality.

Finally, in lesson 5, we will put everything together to generate and print your commission checks. The report we create will include the dollar amount in both numeric form and spelled out in English, such as "twenty-nine dollars and sixteen cents."

For a complete video tutorial with step-by-step instructions on all of these topics, visit my website at the link below. Live long and prosper, my friends.

Topic List

Building a pay commissions form
Managing and approving employee commissions
Marking commissions to be paid
Printing checks for commission payments
Moving items between two list boxes on a form
Transferring selected items from one list box to another
Converting currency amounts to English text
Using a spell number function in Access
Creating a global VBA module
Designing a report to print checks with amounts in words

Article

Welcome to Microsoft Access Developer Level 5. In this lesson, we will be building on the skills learned in prior levels of the Access Developer series. We are focusing on advanced topics, specifically paying employee commissions, moving items between two list boxes, and printing checks with amounts written out in English.

To start, make sure that you are comfortable with the concepts from my Beginner, Expert, and Advanced series, as well as Developer levels 1 through 4. If you are not yet familiar with those topics, I recommend starting from the basics. The beginner series covers tables, forms, and queries. The expert series dives into relationships and higher-level database design. The advanced lessons introduce macro events and step lightly into programming, while the developer series moves into Visual Basic for Applications (VBA) for powerful custom solutions. Each level builds on the last, so a solid foundation is essential.

Throughout this lesson, I recommend that you work along with the examples by building your own database as shown. If you need, you can download a copy of the database from findthatncd.com/databases using the password provided at the time of purchase. While I am using Access 2016, most features and code in this lesson will work in Access 2007 through 2013 as well.

Let us begin by talking about how to pay employee commissions, which we started tracking in the previous level. Our goal is to create a form where you can view all approved commissions, select which ones to pay, and then print checks for those amounts.

First, we will design a pay commissions form, sometimes called a commission check form. This form will show all commissions due for each employee. Imagine you have a list box on the left that displays all unpaid, approved commission items. You want to mark which ones you are going to pay out this cycle, and effectively move them to another list box that shows the commissions you will pay.

Microsoft Access does not natively support click and drag between list boxes. However, we can achieve a similar effect with buttons. Each list box on the form will display a list of commission items, and you can select an item, then click a button to move it from one box to the other.

To set this up, you will create two list boxes on your form, listbox1 and listbox2. The first will show all unpaid commissions for the selected employee. The second will show the commissions selected for payment. To move an item, add a button between the list boxes. When this button is clicked, the selected item moves from the first list to the second.

Here is an example of how you might handle the move in VBA. Imagine the first list box is named lstUnpaid, and the second is named lstToPay.

Private Sub btnAddToPay_Click()
Dim selectedID As Long
If Not IsNull(Me.lstUnpaid) Then
selectedID = Me.lstUnpaid
' Add the selected record to the payment list, e.g. by setting a flag in the table
CurrentDb.Execute "UPDATE CommissionTable SET ToPay = True WHERE CommissionID = " & selectedID
Me.lstUnpaid.Requery
Me.lstToPay.Requery
Else
MsgBox "Please select a commission to move."
End If
End Sub

In this example, the button click event first checks if a commission is selected. If it is, it updates a field in your commission table (perhaps a Yes/No field called ToPay) to True for that commission. Then both list boxes are requeried, so the change is reflected on the form.

Once the commissions to be paid are selected, you move on to check printing. The form will eventually have a button for printing the checks for the selected commissions.

Printing checks involves formatting the check amount as ordinary currency, as well as spelling out the dollar amount in English. For example, if the amount is $100.06, you need to write "One hundred dollars and six cents".

To accomplish this, you will use a custom VBA function, often called SpellNumber or similar. Here is a basic example of such a function that converts a currency value into its English words:

Function SpellNumber(ByVal MyNumber)
Dim Dollars, Cents, Temp
Dim DecimalPlace, Count
ReDim Place(9) As String
Place(2) = " Thousand "
Place(3) = " Million "
Place(4) = " Billion "
Place(5) = " Trillion "
' Convert MyNumber to STRING and find decimal place.
MyNumber = Trim(CStr(MyNumber))
DecimalPlace = InStr(MyNumber, ".")
If DecimalPlace > 0 Then
Dollars = Left(MyNumber, DecimalPlace - 1)
Cents = Mid(MyNumber, DecimalPlace + 1) & "00"
Cents = Left(Cents, 2)
Else
Dollars = MyNumber
Cents = "00"
End If
Temp = ConvertHundreds(Dollars)
SpellNumber = Temp & " Dollars and " & Cents & " Cents"
End Function

You must also create supporting functions like ConvertHundreds to handle blocks of three digits at a time. There are many ways to expand and improve this function for better grammar and edge cases, but this provides a starting point.

Once you have the SpellNumber function set up in a module, you can use it in your check report. For example, in a text box control on the report, you would set the Control Source property to an expression like =SpellNumber([CheckAmount]) where CheckAmount is the field holding the dollar value.

Finally, when printing checks, you will design a report in Access that looks like a real check. Place controls for the date, payee name, numeric amount, and spelled-out amount. When you select the commissions to pay and print, the system will produce a check ready for signing and mailing.

All of these steps combine to provide a practical system for tracking, selecting, and paying employee commissions efficiently, while also covering techniques for moving data between list boxes, updating tables with VBA, and formatting monetary values for official documents.

Taking the time to work through these lessons and implementing them in your own database will deepen your understanding of Access forms, VBA, and custom business workflows. This knowledge will prove valuable for many projects you undertake in the future.