Accounts Payable
By Richard Rost
10 months ago
Build an Accounts Payable Database in Access, Part 1
In this Microsoft Access tutorial, I will show you how to set up an accounts payable tracking system, including how to manage payees in a streamlined table structure, create and categorize payees, and update your forms with new fields for company names and categories. We will also use a bit of optional VBA to dynamically display or hide category options based on payee status.
Members
There is no extended cut, but here is the file download:
Silver Members and up get access to view Extended Cut videos, when available. Gold Members can download the files from class plus get access to the Code Vault. If you're not a member, Join Today!
Prerequisites
Links
Recommended Courses
Up Next
Keywords
TechHelp Access, accounts payable database, vendor payments tracking, payee management, business expenses tracking, payee category table, company name field, combo box payee category, continuous forms, relational combo boxes, query criteria for payees, is payee checkbox, hide control with VBA, on current event, blank template prerequisites, ISO date format, filter payees, payee category combo visibility, entity T unified table, accounts receivable vs payable, storing vendor information, tax categorization payees, payees record navigation VBA
Intro In this video, we'll talk about building an accounts payable tracking system in Microsoft Access to manage vendor payments and business expenses. You'll learn what accounts payable means, how to organize all your contacts in a single table, and how to add fields for payees and categories. I'll show how to set up a payee category table, update your forms with company names, add an is payee checkbox, and create a combo box for selecting categories. We'll also cover using a bit of VBA to automatically show or hide controls on your form, depending on whether someone is a payee. This is part one.Transcript Welcome to another TechHelp video brought to you by AccessLearningZone.com. I'm your instructor, Richard Rost. Today, we're going to talk about accounts payable - how to track vendor payments, bills, business expenses, all that stuff in Microsoft Access. Over the next couple of videos, we're going to build a nice little pretty accounts payable system just for you.
What is accounts payable? When I first started my first business, I had no idea what these terms meant: accounts receivable, accounts payable. Let me take a minute just for those of you who might not know what these terms mean.
In business, accounts receivable are payments that customers owe you that you can expect to receive. Accounts payable are the opposite - payments that you owe to somebody else, money expected to go out.
Think of payables like rent, utility bills, vendor invoices, service providers - anything where you've received a bill and need to pay it. Even if you're not running a business, you can still think of your monthly list of bills you have to pay: your cable bill, your phone bill. Those can be considered payables, and you can use this database for your personal expenses too if you want.
So that's what we're going to build in this little TechHelp series, and this guy looks way too happy to be writing a check. I don't know.
Before we get started, most of this will be done at the expert level, which I consider to be sandwiched in the middle of beginner and developer. That means it's a little bit beyond the basics. I'm going to tell you what prerequisites you need in just a minute, but it's not quite developer-level material. You don't need to know VBA to do most of the stuff I'm going to show you. I am going to sprinkle in a few advanced tidbits here and there for the advanced users, but most of what we're going to do you can make functional without any VBA. VBA makes it cool though.
A few prerequisites for you: If you have not watched my video on how I build my blank template, this covers all the basics - how you make a main menu, a customer form, all that stuff. It's the starting point for most of my videos, including this one. I talk about all my preferences and the way I have things set up, overlapping windows instead of tabbed, all that stuff. So go watch this video first.
You're definitely going to need to know how to use query criteria. Continuous forms (that's where you have forms with multiple records on them). Relationships are a big one - understanding how tables relate to one another. This separates Access from things like Excel.
Relational combo boxes - that's getting a list of values from another table. Another one I like to throw in most of my videos is this ISO date format. I use the ISO date format because I have students all around the world, and a lot of times people have problems because I do stuff specifically to my date format, which, since I'm in America, is month-day-year, whereas a lot of the world goes day-month-year. So I just decided to switch everything to this. It's the same for everybody. Go watch this if you're curious.
These, by the way, are all free videos. They're on my website, and they're on my YouTube channel. Go watch those and then come on back. There may be some more sprinkled in here and there, and if so, I'll give you the links when we get to it.
The first thing we need is a place to store information about our payees, the people that we need to send money to. You might be thinking, well, we can just create a separate table just for payees. That sounds reasonable.
But if you look at how a lot of databases are built, you'll often see separate tables for things that basically contain the same data: customers, employees, vendors, utility companies, aunts and uncles, whatever. The problem is all of those tables end up storing basically the same kind of information: name, company, address, email address, phone number, basically duplicating tables for no good reason. You might have one or two different fields. Vendors, for example, might have a few different fields than employees. But basically, it's mostly the same stuff.
Instead, I find it's better to store them all in a single table. In this case, we're going to use the existing customer T table that we have in the blank template, and we'll just add a field or two to indicate whether or not someone is a payee. That way, if you want to pull a list of payees, we can just filter out for those records. Same goes for vendors; you can add an is vendor field.
Here's a perfect real-world example that happened to me: Sometimes your customers can also become payees. Back when I lived in Buffalo, I had my computer business there. I sold computers, did networking service, all that stuff. I had a training room. I had a customer, Mike Mazman from Buffalo Embroidery, great guy. He would buy computer stuff and services from me, and in return, I would order shirts and other items from him for my employees back when I actually still had employees.
Rather than store Mike's information in two different tables - a customer table and a vendor table - it made sense to keep it all in one table and just mark him as a payee.
Quick shout out to Mike. I haven't talked to Mike in a few years now since I moved to Florida, but if you happen to be in the Buffalo area, check him out. I'll drop a link to his website down below. Make sure you tell him I said hi.
Let's head over to the database. This is the TechHelp free template. You can grab a copy of this off my website if you want to. Here I got a customer list. Now I just call everybody customer, but this is going to include everybody we deal with: customers, vendors, and if you want to rebuild this from the ground up and call it like entity T, I've done this in other databases. That way you don't get confused: Well, this person isn't a customer, that's the electric company, they're buying stuff from us. No, it's just everybody's in customer - it's just the name.
Now let's go edit our customer table because we're going to put some stuff in here to indicate whether or not this customer is a payee. Come down to the bottom. I thought about hijacking is active, but we're going to keep that for something separate, so I'm going to make this is payee.
You can have payees who are no longer active, but you might want to indicate that they used to be payees. So, is payee - that'll be a yes or no. I'm going to make the default value no, because by default when you add new people, they're probably not payees until you mark them as payee.
We also might want to know what type of payee this is. This might help later on when tax season comes around; you can figure out, well, we spent this much on utilities, this much for cost of goods sold, all that stuff. So here, we're going to put a payee category ID. That's going to be a number of type long integer. That's going to relate to a payee category table that we'll make in just a few minutes. Here we're just storing the number, the ID - basic relationships 101.
One more thing we're going to add; in my experience, a lot of payees, I don't have a personal contact there, like Verizon. In a lot of these cases you deal with companies. So, we're going to put a company name in here, something that I did not put in the TechHelp free template because we just assumed it was all consumers.
Later on, I'll show you how to make a query that will pick either the company name or the person. So, if there's no company name there, you'll get the person's name. We'll do that later.
Save it (Control S), close it, and let's make that payee category table. So, create table design, payee category ID, that's my auto number. We have the description and maybe notes, long text, whatever else you want to put in here about that category.
We'll save this as my payee category T or table. Yes for the primary key. We'll put some sample records in here.
We've got utilities, how about communications (Communications - I spelled that wrong). Communications for your cell phone and your internet service and all that stuff. Cost of goods sold - I would just put COGS. I know someone's going to ask later what is COGS. Rent or mortgage or whatever, put your list of your payment category types in here. Vendors would be a big one. That's good enough.
Save it, close it. Now let's put that stuff on our customer form. Right click, design view. First thing I'm going to do is throw the company name up top here, like so.
I'm going to copy first name, copy, paste, and slide it up here, and get it in there. We'll put in here company, like that. Double click here, change the control source to company - it's way on the bottom. I'm going to copy and paste that into the name field. Make sure your control source and your name are usually the same thing for text boxes; they almost always are. For things like combo boxes, sometimes I make it different.
We also now have to adjust our tab order. Make sure you put the company name first. I think I have customer ID first. Go to form design, tab order. I think I have customer ID, so it's not a tab stop. Let's see, company name - click, click again, drag it up top, and let's put it right in front of first name like that.
Save it, close it, open it, and good, we're sitting on company. I'll put in here Computer Learning Zone, and we've got a bunch of other people in here, Enterprise Inc, and so on. There's our company.
Now we've got to indicate whether or not this person is a payee. For the purposes of class, I'm going to get rid of this stuff - we don't need this here. Let's get rid of that.
I'm going to add in my is payee. Don't bring in is payee category ID; we're going to make a combo box for that. But I do need is payee. Let's grab a format painter and make this black. I'm just going to make the label payee, and we're going to slide it over here like that.
Now let's make a category combo box to go next to it. Find your combo box, drop it down here. I want the combo box to get values from a table or query. Which table or query has your list of categories? There's the category table right there. Bring over both fields.
How do you want to sort it? Description is good. That's what it looks like. The key column is hidden. Next.
We're going to store that value. We're picking a category ID, a payee category ID. We want to store that value in the payee category ID of the customer table.
Next, what label do you want? Doesn't matter, we're going to delete it in a second. There it is. I'm going to delete that label that comes in with it and stick this right next to payee, just like that.
So, I've got the checkbox for whether or not they're a payee. Besides this, a little bit, save it, close it, open it. Let's mark me as a payee, and I'm going to be utilities. Let's mark James Kirk as a payee and he can be a vendor. See, all this works pretty straightforward.
The end of Troy will be from Beta Z. The company. She's a payee and she's communications, of course. Or that would be Warf. I don't know. Who would be communications? Anyways, we've got that done now.You might not always want to see this combo box if they are not a payee. In fact, right now there is nothing to stop someone from doing this, and that is not mine. So, wouldn't it be nice if we can hide this if this person is not a payee? If this box is checked, either hide this box or disable it - one of the two. I like to hide stuff.
Now, this does unfortunately involve a little bit of VBA. I said at the top I was not going to be doing a lot of VBA. So, if you are not interested whatsoever at all about VBA, you can stop watching right now.
You are not going to miss anything, but I am going to throw in a few tidbits here and there, like how to do this with a little tiny bit of VBA for the advanced students.
If you want to learn some VBA and you do not know it yet but you are curious, go watch this video. It will teach you everything you need to know to get started in about 20 minutes.
You can come back and have fun. So, we are going to need a little event when someone clicks on this button and when you go from record to record to hide this box.
One thing I do not like about what the wizard does is it does not give you an opportunity to name that box. Right now it is combo 32 or 33.
So, I am going to rename this to payee category combo. I like to call them combos because sometimes in your VBA, you will treat combo boxes differently than you will just text boxes. That is why I like to name those combo. It is not a big deal. You can name it as I do if you want to, but that is just a me thing. That is a Rick thing.
Now, when this guy is clicked in the after update event based on the value here, we are going to show or hide that box. But we also have to show or hide it when we move from record to record.
Since we are going to call the same code from two different places, we are going to make our own little subroutine that does that.
I am going to go to the check box here. Go to events. Go to after update. It is the event that fires after you update the value in that check box. I will bring up your code builder.
This guy right here.
We are going to say update payee combo.
What is that? What is update payee combo? Well, we are going to make it ourselves. I am going to copy that. Come right above here.
Type in private sub update payee combo. This is the stuff that is going to run when payee after update runs. Come right in here.
And this is going to be a real simple if then statement. It is going to be if is payee then payee combo or payee category combo - and spelling is important - dot visible equals true.
Else. I am just going to copy this so I do not have to retype it again. Payee combo dot visible equals false.
End if. Real simple if then else end if statement. Very easy to read.
Let us see it in action. Debug compile once in a while. That just makes sure all your code is good.
Back over here. Save it. Close it. Open it. Click the button.
Oh, look at that. Click the button, and it goes away. Comes back. Goes away. Comes back. Goes away.
But that is not the only place we need that.
When I move from record to record, I also need it to run. That is the on current event.
The on current event is a form level event. Click right there in the little box. Go to events, find on current. Click that dot dot dot, same thing. Now we do not want to copy all that code and put code in multiple places.
Bad. Very bad. Do not do it. So, I am going to just take update payee combo, copy that, and stick that right there.
So, it will do the same thing in two different places. Ready. Save it. Close it. Close it. Open it.
Let us move from record to record. Look at that. See? One of these guys who pays said that one is. This will be fire fly.
So, there are our payees and as we move from record to record, it shows or hides that combo.
Pretty cool stuff. If we turn that off, we turn that off. One thing you might also want to do is blank that value.
You can set that value to zero, which will take it off. But you might want to, you might not want to. That is up to you. You can set the value equal to zero.
If they turn that off, but then later on in the future, you might turn it back on again and you might want to know that.
Utilities was what was in there before. That is up to you.
We have lots more coming. This is probably going to be at least a four or five video series.
We are going to cover all the different little aspects you need to have a functional working accounts payable system.
That is all for today. So, tune in tomorrow, same bat time, same bat channel.
If you are a member, you can keep watching now because I am going to keep recording. One of the benefits of being a member is you can watch stuff as soon as I post it.
You do not have to wait for it to go public. Plus, you get all my extended cut videos and all kinds of extra stuff.
So, sign up now. If you would like to learn more about building fully functional accounts payable systems, check out my Access Payables Seminar.
It covers everything I am going to show you in this payable TechHelp series, plus a whole lot more. There is the link - it is on my website. Check it out.
That is going to do it for today, folks. That is the end of part one. I hope you learned something. Live long and prosper, my friends.
I will see you tomorrow for part two.
TOPICS:
Explanation of accounts payable in business
Single table for customers and payees
Adding is payee field to customer table
Adding payee category ID to customer table
Creating the payee category table
Adding company name field to customer table
Updating customer form to include company name
Adding is payee checkbox to customer form
Creating payee category combo box on customer form
Setting up combo box to pull values from category table
Adjusting tab order on form for company name
Attaching payee category to selected payees
Using VBA to show or hide payee category combo box
Writing an event to update visibility of combo box
Using the after update event for is payee checkbox
Using the on current event to update combo box visibility
Naming controls appropriately for VBA reference
COMMERCIAL:
In today's video, we're learning about accounts payable and how to set up a system to track your bills and vendor payments in Microsoft Access, no matter if you're running a business or just managing your personal expenses. We'll start by talking about what accounts payable means, then I'll show you how to make your database more efficient by putting all your contacts - customers, vendors, and payees - into one table, using extra fields to organize them. You'll learn how to create and use a payee category table, add a company field to your forms, and set up a combo box for selecting categories. Plus, you'll see a simple VBA trick to show or hide that combo box automatically based on whether someone is marked as a payee. This is part one of a full series, so stick around for more. You'll find the complete video on my YouTube channel and on my website at the link shown. Live long and prosper my friends.Quiz Q1. What is the main purpose of an accounts payable system in Microsoft Access as described in the video?
A. To track payments customers owe you
B. To track bills, vendor invoices, and business expenses you need to pay
C. To increase your customer base
D. To automate payroll for employees
Q2. What is the difference between accounts receivable and accounts payable?
A. Receivables are money you owe to others; payables are money owed to you
B. Receivables are money customers owe you; payables are money you owe to others
C. Both represent money you will receive
D. Both represent money you need to spend
Q3. Which level best describes the prerequisite skills for following along with the tutorial?
A. Complete beginner
B. Advanced developer familiar with VBA
C. Expert (between beginner and developer) with some Access basics
D. No prior knowledge needed at all
Q4. Which of the following is NOT listed as a recommended prerequisite before building the accounts payable system?
A. Understanding query criteria
B. Familiarity with continuous forms
C. Knowledge of ISO date format
D. Experience with Excel macros
Q5. Why does the instructor recommend using a single table for all people you deal with (customers, vendors, payees, etc.)?
A. It is more secure
B. It saves storage space on the hard drive
C. It prevents duplicating similar information across multiple tables
D. It is required by Microsoft Access
Q6. How does the instructor suggest indicating whether a person is a payee in the database?
A. Creating a separate payee table
B. Adding an "is payee" field to the existing customer table
C. Using the "is active" field for payees
D. Only tracking payees manually
Q7. What additional field is added to the table to categorize payees for reporting purposes?
A. Payee type text field
B. Payment history field
C. Payee category ID
D. Preferred vendor rating
Q8. What is the purpose of the Payee Category table in the system?
A. To store a list of invoice numbers
B. To document customer satisfaction surveys
C. To list and describe payment categories such as utilities or rent
D. To track user login information
Q9. When adding the company name field to the customer table, which scenario does the video specifically mention as a reason?
A. To track competitor companies
B. Because many payees are companies without a personal contact
C. To store alternate email addresses
D. To link to employee records
Q10. Why does the instructor adjust the tab order on the form after adding the company name field?
A. To speed up form loading
B. To make data entry more efficient and logical
C. Because Microsoft Access requires it
D. To reduce file size
Q11. What is the recommended way to display the payee category selection for users?
A. As a simple text box
B. As a combo box whose values come from the payee category table
C. As a checkbox
D. As a command button
Q12. What is the benefit of hiding or disabling the payee category combo box when the record is not marked as a payee?
A. Prevents unnecessary data entry and confusion
B. Makes the form look more colorful
C. Increases the size of the form
D. Helps track login history
Q13. How does the instructor implement the hiding of the payee category combo box on the form?
A. By using a plain macro with no code
B. By using a small amount of VBA to set the Visible property
C. By deleting the combo box when not needed
D. By creating a separate form for payees
Q14. When should the update routine for showing/hiding the payee category combo box be called?
A. Only when opening the form
B. Only after changing the is payee checkbox
C. Both after updating the is payee checkbox and when moving from record to record (on current event)
D. Only once per session
Q15. What advice does the instructor give regarding copying code in multiple places versus using a subroutine?
A. Copying code is fine if it is a small routine
B. Always use a subroutine to avoid duplicating code
C. Place all code inside the form's open event only
D. Only use macros for updating controls
Q16. According to the instructor, what are some of the reporting benefits of having a payee category for each payment?
A. Easier tax reporting and expense breakdown by type
B. Faster query execution
C. Improved password protection
D. Automated email reminders
Q17. What should you do if you are not interested in learning any VBA, according to the instructor?
A. Ignore the entire series
B. Stop watching during the VBA segment, but you can still use the system without it
C. Skip the Access basics prerequisites
D. Only use Excel
Q18. What is the purpose of debugging and compiling code regularly in Access VBA, according to the video?
A. To make the database faster
B. To verify that your code has no errors
C. To reduce file size
D. To update form layouts
Q19. If a person is no longer a payee, what optional step does the video mention regarding their category value?
A. Automatically delete their record
B. Set the category value to zero, or leave it for historical purposes
C. Change their name to "inactive"
D. Move them to a new table
Q20. What is the main advantage of using combo box control names like "payee category combo" instead of just leaving the default name?
A. It makes the database prettier
B. It helps in VBA code readability and management
C. It increases performance
D. It is required by Access for combo boxes
Answers: 1-B; 2-B; 3-C; 4-D; 5-C; 6-B; 7-C; 8-C; 9-B; 10-B; 11-B; 12-A; 13-B; 14-C; 15-B; 16-A; 17-B; 18-B; 19-B; 20-B
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 TechHelp tutorial from Access Learning Zone covers how to set up an accounts payable system using Microsoft Access. This is an essential tool for tracking vendor payments, bills, business expenses, and more. Whether you need to manage your business bills or track personal expenses, this database solution will suit your needs.
Let me begin by clarifying some basic terminology, in case anyone is new to this aspect of accounting. Accounts receivable refers to money that customers owe your business, money you expect to receive. In contrast, accounts payable is money your business owes to others such as vendors, utilities, or service providers. Even if you are not running a business, you can use the same logic to manage your personal bills, treating every expense as a payable.
In this series, I am going to walk you through the process of building a simple yet effective accounts payable system in Access. Most of what I cover today will be at the expert level, which I define as that middle ground between basic user and full developer. No VBA experience is required for most of the functionality, though I will occasionally highlight some more advanced tips for those who want to experiment with VBA and custom automation.
Before we start building the accounts payable tool, there are some prerequisites that will help you follow along more easily. Make sure you have watched my tutorial on building the blank template. This video covers essentials such as creating a main menu, setting up customer forms, and other preferences like using overlapping windows. Familiarity with query criteria, using continuous forms, and understanding table relationships in Access are also important, since these concepts separate Access from simpler tools like Excel. You should also know how to create relational combo boxes, which let you pull data from other tables, and be aware of the ISO date format I recommend to avoid confusion with international date standards. All these tutorials are free and available on my website and YouTube channel, so check them out before proceeding if you need a refresher.
Now, to track who gets paid, we need to store information about our payees. A common mistake in database design is creating separate tables for customers, vendors, utility providers, and others, but this usually leads to redundant data since all these tables store basically the same information - names, addresses, contact details, and so on. Instead, it is more efficient to keep all these contacts in one table and simply add a few fields to specify whether a person or company is a payee, a vendor, or another entity type. In this example, we will modify the existing customer table in the blank template, adding fields to indicate payees. Filtering will then allow us to generate a list of payees or vendors whenever needed.
Let me give you a real-world scenario: At one point, I had a customer who was also a vendor. He would buy computers and services from me, but I also bought uniforms for my employees from his business. It makes sense to keep him as a single record in the database and simply flag him as both customer and payee, which is easy if everything is stored in one table.
Next, in the Access database, I am going to edit the customer table. I will add a new field called "is payee," which will serve as a yes/no indicator for whether that contact is a payee. By default, I set this to "no," since most people added to the database will not immediately be payees.
To add further detail for reporting purposes, I am also going to add a "payee category ID" field. This number will link to a new table we are creating: the Payee Category table. This way, I can organize payments into categories such as utilities, cost of goods sold, rent, and more. Keeping detailed categories is especially useful for business accounting and preparing for tax season.
Another addition is a "company name" field in the customer table. Many payees are actually companies rather than individuals, so this is necessary. Later on, I will cover how to generate queries that display either the company name or, if there is none, default to the person's name.
After making these updates, I save my changes and move on to create the Payee Category table. This table uses an auto number for the primary key and includes fields for category description and any additional notes. I populate it with sample records like "utilities," "communications," "cost of goods sold" (abbreviated as COGS), "rent," and "vendors." Once done, I save and close the table.
On the customer form, I add the new company name field, placing it at the top and making sure the tab order prioritizes company name before first name. For the "is payee" status, I add a checkbox, and then next to it, I include a combo box that is populated with payee categories from the Payee Category table. Using the combo box wizard, I make sure the selected category ID is stored in the corresponding field in the customer table.
It is a good practice to keep your control source and field names consistent for text boxes, although for combo boxes, I sometimes make an exception. I also check the tab order again to ensure the new fields fit naturally within the form's workflow.
Testing the form, I add sample companies and people, mark several as payees, and assign each a relevant payee category. Everything works as intended.
However, I notice the category combo box is always visible, which might be confusing if a record is not actually a payee. Ideally, I want the combo box to only show if the "is payee" box is checked. To achieve this, I add a small bit of VBA. If you would rather avoid VBA altogether, you can still follow the remainder of the tutorial, but for those interested in a dynamic and interactive form, here is how the process works.
I rename the combo box for clarity, as the wizard-generated names are not helpful for future edits or VBA use. The goal is to write a subroutine that toggles the visibility of the payee category combo box based on whether the "is payee" checkbox is checked. This routine will run both when the checkbox is updated and each time we move to a new record, thanks to placing a call to the subroutine in both the after update and on current events of the form.
When implemented, toggling the "is payee" checkbox now instantly shows or hides the payee category selector. This keeps the form tidy and makes data entry clear for users. Optionally, you can clear out the payee category if the payee status is unchecked, but whether you reset this value or retain it for future use is up to you.
This video is just the first part of a multi-part series, where I will continue to cover other important elements for an effective accounts payable system in Access. If you are a member of the site, you can access the next lessons immediately, along with extended cut content and other premium material.
For those looking to build a comprehensive payables database, I also offer a detailed Access Payables Seminar, available through my website. The seminar covers everything discussed in these videos, plus much more.
That concludes today's lesson. For step-by-step instructions and complete video tutorials on everything discussed here, visit my website at the link below.
Live long and prosper, my friends.Topic List Explanation of accounts payable in business
Single table for customers and payees
Adding is payee field to customer table
Adding payee category ID to customer table
Creating the payee category table
Adding company name field to customer table
Updating customer form to include company name
Adding is payee checkbox to customer form
Creating payee category combo box on customer form
Setting up combo box to pull values from category table
Adjusting tab order on form for company name
Attaching payee category to selected payees
Using VBA to show or hide payee category combo box
Writing an event to update visibility of combo box
Using the after update event for is payee checkbox
Using the on current event to update combo box visibility
Naming controls appropriately for VBA reference |
