Accounts Payable Series
By Alex Hedley
9 months ago
Build an Accounts Payable Database in Access Series
Lessons
What's Covered
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.
In this Microsoft Access tutorial I will show you how to build a payee query to display either company name or contact name for payees, design a bill table to track accounts payable details, and create a continuous form with a combo box for selecting payees. This is part 2.
In this Microsoft Access tutorial I will show you how to add buttons to open forms, apply conditional formatting to highlight bills due soon, use expressions to format fields based on payment status, create DSum calculations to show amounts due within a set timeframe, and customize your forms to display totals and past due balances for specific payees. This is part 3.
In this Microsoft Access tutorial, I will show you how to build a query to categorize your accounts payable items based on their due dates using the IIf function. You will learn how to identify amounts that are past due, due in seven days, thirty days, and over thirty days, as well as how to set up calculated fields for each group. This is part 4.
In this Microsoft Access tutorial I will show you how to build and format an accounts payable report, including how to join payee data to your existing payables query, add grouping and sorting by payee, customize report headers and layouts, and adjust formatting like label placement, column alignment, and background colors. This is part 5.
In this Microsoft Access tutorial I will show you how to enhance your accounts payable report by adding print preview buttons, creating group and report level totals with the Sum function, formatting values as currency, and adjusting report layout for better readability. We will also cover fixing copy and paste issues in Report Design View and discuss how to show or hide detail sections for summary-only views. This is part 6.
In this Microsoft Access tutorial, I will show you how to enhance your accounts payable database by adding interactive features with VBA, including filtering unpaid records by default, sorting payables by due date, marking invoices as paid with a double-click event, and customizing reports to show or hide details and filter by the current payee using checkboxes. This is part 7.
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
|
Links
|
Series
|
Recommended Courses
|
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
TechHelp Access, payee query, payee combo box, customer-payee relationship, conditional display company name, IIF function, IsNull function, string concatenation, bills table design, payeeID field, continuous form, binding form to table, combo box sort, payment tracking, form footer notes, self-join relationship
TechHelp Access, conditional formatting, DSum function, accounts payable series, form design, show due bills, filter unpaid, format due dates, amount due calculation, date function, highlight overdue bills, show bills due soon, paid date logic, payee setup, due in 30 days, past due calculation, NZ function, tab order, customer ID criteria, payee ID error, main menu metrics, sum unpaid bills, show upcoming bills, expression is rule, show by payee, auto filter unpaid, bold red due dates
TechHelp Access, accounts payable series, conditional formatting, payables form, past due report, aging report, IIf function, query criteria is null, calculated fields, date calculations, due in seven days, due in thirty days, aging accounts payable, field aliasing, grouping by due date
TechHelp Access, accounts payable report, grouping and sorting, payables query, payee ID, payee name, custom report design, report header formatting, group footer totals, record source setup, alternate row color, print preview settings, column alignment, ad hoc join, customer code best practice, sample database download, blank report template, detail section formatting, report labels, columnar report layout, header background color, report totals per client, form filter totals
TechHelp Access, accounts payable report, payables report print button, report totals past due, due in 7 days, due in 30 days, over 30 days totals, format as currency, set decimal places, group footer totals, report footer totals, payee total due calculation, hide detail section, report design tricks, sum function in reports, aggregate query for totals, show/hide details with VBA, payables summary report, troubleshoot copy paste bug, report field naming conventions, preview report before printing, report formatting tips
TechHelp Access, payee query, payee combo box, customer-payee relationship, conditional display company name, IIF function, IsNull function, string concatenation, bills table design, payeeID field, continuous form, binding form to table, combo box sort, p
TechHelp Access, conditional formatting, DSum function, accounts payable series, form design, show due bills, filter unpaid, format due dates, amount due calculation, date function, highlight overdue bills, show bills due soon, paid date logic, payee setu
TechHelp Access, accounts payable series, conditional formatting, payables form, past due report, aging report, IIf function, query criteria is null, calculated fields, date calculations, due in seven days, due in thirty days, aging accounts payable, fiel
TechHelp Access, accounts payable report, grouping and sorting, payables query, payee ID, payee name, custom report design, report header formatting, group footer totals, record source setup, alternate row color, print preview settings, column alignment,
TechHelp Access, accounts payable report, payables report print button, report totals past due, due in 7 days, due in 30 days, over 30 days totals, format as currency, set decimal places, group footer totals, report footer totals, payee total due calculat
TechHelp Access, VBA filter unpaid payables, DoCmd.OpenForm where condition, PaidDate null filter, accounts payable VBA tricks, form sort by DueDate, double-click event PaidDate, Me.Refresh to save record, conditional formatting form fields, ControlTipTex
Microsoft Access Accounts Payable, Part 2: Bills Table, Payee Combo Box, Continuous Bills Form
Microsoft Access Accounts Payable, Part 3: Conditionally Format Bill Form, Total Due on Main Menu
Microsoft Access Accounts Payable, Part 4: Aged Payables Query, Past Due, Due in 30, etc.
Microsoft Access Accounts Payable, Part 5: Aged Payables Reporting, Sorting & Grouping
Microsoft Access Accounts Payable, Part 6: Aged Payables Report, Group & Report Footer Totals
Microsoft Access Accounts Payable, Part 7: Advanced VBA Tricks, Show Unpaid Only, Report Details
     
Intro In this video, we'll talk about how to set up an accounts payable tracking system in Microsoft Access. I'll explain what accounts payable means, discuss best practices for organizing payees and vendors, and show you how to modify your customer table to handle payees using new fields and a payee category combo box. We'll also step through building the payee category table and making simple form customizations, including showing or hiding controls with a bit of VBA. This is part 1.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, 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. So let me take a minute 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 or to come in. 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.
That's what we're going to build in this TechHelp series. Also, this guy looks way too happy to be writing a check.
Now 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 this 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 - and 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, and why I use overlapping windows instead of tabbed, and 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), and relationships - a real big one. Understanding how tables relate to one another 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. 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. So go watch this if you're curious.
These, by the way, are all free videos. They're on my website. 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. Now you might be thinking 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 in them than employees. But basically, it's mostly the same stuff.
So instead, I find it's better to store them all in a single table. In this case, we're going to use the existing customerT 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 isVendor 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. In return, I would order shirts and other items from him for my employees (back when I actually 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've got a customer list. Now I just call everybody customer, but this is going to include everybody we deal with: customers, vendors. If you want to rebuild this from the ground up and call it something like entityT, 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 in customers. 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 isActive - we're going to keep that for something separate. Instead, I'm going to make this isPayee.
You can have payees who are no longer active, but you might want to indicate that they used to be payees. So isPayee will 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. Here we're going to put a payeeCategoryID. That's going to be a number of type Long Integer, and 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, and this is because at least 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 companyName 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. Go to Create -> Table Design. Enter payeeCategoryID (AutoNumber), description, and maybe notes (Long Text, whatever else you want to put in here about that category). Save this as payeeCategoryT (or table). Yes, for the primary key. Put some sample records in here.
We've got utilities, communications (I spelled that wrong before - communications for your cell phone, your internet service, and all that stuff), cost of goods sold (I would just put COGS - I know someone is going to ask later "What is COGS?"), rent or mortgage, whatever. Put your list of your payment category types in here. Vendors. 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.
I'm going to copy the first name, copy-paste, and slide it up here. We'll put in here "Company" as the label. Double-click here, change the control source to company (it's way on the bottom). 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 have to now adjust our tab order. Make sure you put the company name first. Actually, I think I have customerID first, which is not a tab stop. Go to Form Design -> Tab Order, find company name, and drag it up top, right in front of first name.
Save it, close it, open it - good, we're sitting on company. Enter "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 have 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. I'm going to add in my isPayee. Don't bring in isPayeeCategoryID yet; we're going to make a combo box for that. But I do need isPayee. Grab the format painter and make this black. Label it "Payee" and slide it over.
Now let's make a category combo box to go next to it. Find your combo box, drop it down. 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. 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 payeeCategoryID. I want to store that value in the payeeCategoryID of the customer table.
Next, what label do you want? Doesn't matter; we're going to delete it in a second. Delete the label that comes in with it and stick this right next to payee. So I got the checkbox and whether or not they're a payee. Resize as needed, save it, close it, open it.
Mark me as a payee and I'm going to be utilities. 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 Worf. I don't know who would be communications. Anyway, we've got that done now.
You might not always want to see this combo box if they're not a payee. In fact, right now there's nothing to stop someone from doing this, and that's not ideal. Wouldn't it be nice if we could hide this? If this person is not a payee, if this box is not checked, either hide this box or disable it - one of the two. I like to hide stuff.
Now, this unfortunately involves a little bit of VBA. I said at the top I wasn't going to be doing a lot of VBA, so if you're not interested at all in VBA, you can stop watching right now. You're 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 VBA for the advanced students.
If you're not interested in VBA, just stick around anyway and watch it - this is fun stuff. If you want to learn some VBA and you don't know it yet but are curious, go watch this video. It'll teach you everything you need to know to get started in about 20 minutes. You can come back and have fun.
We're 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 don't like about what the wizard does is it doesn't give you an opportunity to name that box. Right now, it's combo32 or 33. I'm going to rename this to payeeCategoryCombo. I like to call them combos because sometimes in your VBA, you'll treat combo boxes differently than text boxes. That's why I like to name those "combo" - it's not a big deal, you can name it what you want, but that's just a me thing.
When this guy is clicked, in the After Update event, based on the value here, we're 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're going to call the same code from two different places, we're going to make our own little subroutine that does that.
Go to the checkbox. Go to Events. Go to After Update - that's the event that fires after you update the value in that checkbox. Bring up your code builder.
We're going to say UpdatePayeeCombo.
What is UpdatePayeeCombo? We're going to make it ourselves. Copy that, come right above, type in "Private Sub UpdatePayeeCombo". This is the stuff that's going to run when isPayee After Update runs. Come right in here.
This is going to be a real simple If Then statement. It will be:
If isPayee Then
PayeeCategoryCombo.Visible = True
Else
PayeeCategoryCombo.Visible = False
End If
Real simple If Then Else End If statement. Very easy to read.
Let's 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. Look at that - click the button and it goes away, comes back, goes away, comes back.
But that's not the only place we need that to run. When I move from record to record, I also need it to run, and 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 ..., same thing. We don't want to copy all that code in multiple places - that's bad. So I'm going to just take UpdatePayeeCombo, copy that, and stick that right there.
So it'll do the same thing in two different places. Ready? Save it, close it, open it. Let's move from record to record. Look at that. See? One of these guys is a payee, that one is. This will be Firefly.
So there's our payees, and when we move from record to record, it shows or hides that combo. Pretty cool stuff. If we turn that off and turn it back on, 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, or you might not want to - that's up to you. You can set the value equal to zero, so if they turn that off, but 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's up to you.
We've got lots more coming. This is probably going to be at least a four or five video series. We're going to cover all the different little aspects you need to have a functional working accounts payable system.
But that's all for today. So tune in tomorrow - same bat time, same bat channel.
If you're a member, you can keep watching now because I'm going to keep recording. One of the benefits of being a member is you can watch stuff as soon as I post it - you don't 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'd like to learn more about building fully functional accounts payable systems, check out my Access Payables Seminar. It covers everything I'm going to show you in this Payables TechHelp series, plus a whole lot more. There's the link; it's on my website. Check it out.
That's going to do it for today, folks. That's the end of part one. I hope you learned something. Live long and prosper, my friends.
I'll see you tomorrow for part two.Quiz Q1. What is the primary focus of this TechHelp video series?
A. Building an accounts payable system in Microsoft Access
B. Creating a payroll management system in Excel
C. Managing customer sales in QuickBooks
D. Designing a website for payees
Q2. What does "accounts payable" refer to?
A. Money customers owe you
B. Money you owe to others
C. Inventory you have in stock
D. Loans you have paid off
Q3. Which of the following could be considered a payee in an accounts payable system?
A. Your employer
B. Your customer
C. Your cable company
D. Your employee
Q4. Which skill is NOT listed as a prerequisite for this video?
A. Query criteria
B. Continuous forms
C. Table relationships
D. Advanced VBA programming
Q5. Why does Richard recommend using a single table for all entities (customers, vendors, etc.)?
A. It is easier to create forms
B. It prevents data duplication
C. It uses less storage space
D. It is required by Microsoft Access
Q6. How does the system indicate whether a person or company is a payee?
A. By their last name
B. With a new field called isPayee
C. By their city and state
D. With a password
Q7. What kind of field is used to identify the type of payee (e.g., utilities, rent)?
A. Short text field
B. AutoNumber field
C. payeeCategoryID (Long Integer relating to category table)
D. Date/Time field
Q8. Why was a companyName field added to the customer table?
A. To store personal contact details
B. For storing company names when payees are businesses
C. For storing invoice numbers
D. For duplicate detection
Q9. What is the purpose of the payee category table (payeeCategoryT)?
A. To track payments received from customers
B. To categorize types of payees for reporting
C. To store invoice line items
D. To track employee schedules
Q10. When creating a combo box for payee category on the customer form, what is it used for?
A. Lookup and store payeeCategoryID for a payee
B. Enter payment amounts
C. List company phone numbers
D. Store customer comments
Q11. What does the UpdatePayeeCombo subroutine do in VBA?
A. Calculates totals of all payees
B. Hides or shows the payee category combo box based on isPayee status
C. Deletes records if they are not payees
D. Sends payment reminders
Q12. What event is used to trigger showing or hiding the payee category combo box when moving records?
A. On Click
B. Before Update
C. On Current
D. On Open
Q13. Why does Richard recommend naming the combo box as payeeCategoryCombo?
A. For alphabetic order in the database
B. To distinguish it from text boxes in VBA code
C. Because it is required by Access
D. For sorting purposes
Q14. What format does Richard use for dates in his templates?
A. US format (month-day-year)
B. European format (day-month-year)
C. ISO date format
D. Long text format
Q15. Why might you want to NOT store a payee in multiple tables (e.g., customers and vendors)?
A. It makes searches faster
B. It reduces confusion and duplication since one person might be both a customer and a payee
C. It is required by the IRS
D. It improves email support
Answers: 1-A; 2-B; 3-C; 4-D; 5-B; 6-B; 7-C; 8-B; 9-B; 10-A; 11-B; 12-C; 13-B; 14-C; 15-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 focuses on accounts payable and how to track vendor payments, bills, and business expenses in Microsoft Access. Over the next several lessons, I will show you how to create a comprehensive and user-friendly accounts payable system that you can use for both business and personal financial tracking.
Let me begin by clarifying some basic accounting terms for anyone new to this topic. In the business world, accounts receivable refers to money owed to you by your customers, meaning payments you expect to receive. Accounts payable, on the other hand, represents the money you owe to others, so payments you are expected to make.
Examples of payables include rent, utility bills, vendor invoices, or services you have used and need to pay for. Even outside of business, think of your monthly expenses like your phone bill or cable bill; those are also payables, and this database can be helpful for personal use as well.
This series will guide you through building such a system in Access. Most of what we will cover will be at the expert level, which means it is a step above beginner but not as advanced as full developer material. You do not need to know VBA to get most of this working, but I will include some advanced tips along the way for those who are interested.
Before you begin, there are a few prerequisite skills you should have. If you are not familiar with how I build my blank templates in Access, I suggest watching my introductory video on that subject. It covers essential topics like creating a main menu, the customer form, and the basic setup preferences I use. You should also know how to use query criteria, continuous forms (which display multiple records at once), and, most importantly, be comfortable with relationships in Access and relational combo boxes that pull data from other tables.
Another point worth mentioning is that I use the ISO date format in my tutorials since I have students from around the world, and date formats can be a source of confusion. The ISO format avoids those issues. You can refer to my video on that topic if you want more information.
These prerequisite videos are free and available on my website or YouTube channel. Feel free to check them out and return here when you are ready. I may mention additional videos as we go along, and I will provide links when relevant.
The first thing we will need in our database is a way to store information about payees, which is anyone you need to send money to. You might think it makes sense to create a separate table just for payees, but if you look closely, you will see that many tables like customers, employees, or vendors end up storing almost identical information such as name, address, phone, and email. Creating multiple tables just duplicates data without a good reason. Occasionally there may be a couple of unique fields, but for the most part, the data is the same.
Instead, I recommend storing all these contacts in a single table and adding fields to indicate each person's role. In this case, I will use the existing customerT table from the blank template and simply add a field to specify whether a person is a payee. If you want a list of payees, you can filter for that field. You can do the same thing to distinguish between vendors and other roles.
Here is a real-world example. When I owned my computer business in Buffalo, one of my customers, Mike from Buffalo Embroidery, would buy products and services from me. At the same time, I would buy shirts and branded materials from him for my business. Mike was both a customer and a payee. Storing his information in multiple tables would have been a hassle, so I kept everything in one table and marked whether he was a payee.
Let us go into the database using the TechHelp free template, which you can download from my website. In this template, everyone is categorized as a "customer," but that list actually includes customers, vendors, and everyone else you work with. If you want to name the table something more generic, like entityT, you are free to do so, but for consistency in this lesson, I will stick with customerT.
Next, let us edit the customer table to add the necessary fields. I am going to add a yes/no field called isPayee, which will mark whether a given record is a payee. The default value will be No, since most new records are not payees unless you specify otherwise.
I will also add a field called payeeCategoryID. This numeric field will link to a payee category table, which we will create next. This is simply standard relational database design, linking records in one table to categories in another for reporting and sorting purposes.
One more addition: many payees are companies rather than individuals, so I am adding a companyName field. The original template did not include this, assuming most records were individuals, but businesses like utility providers should have their company names entered. Later, I will show you how to write a query that prefers the company name but falls back on an individual's name if the company field is empty.
After making these changes, save and close the table. Next, create the payee category table. This table will include fields for payeeCategoryID (AutoNumber primary key), description, and optionally a notes field. Fill in some example categories like utilities, communications (for phone or internet services), cost of goods sold (COGS), rent or mortgage, and vendors.
Now, update the customer form to display these new fields. Place the companyName field at the top of the form, making sure it appears first in the tab order for easier data entry. For indicating whether someone is a payee, add the isPayee checkbox. Do not bring in payeeCategoryID directly; instead, create a combo box that pulls its values from the payee category table. This combo box will let users select the appropriate category for each payee.
After setting up the combo box, arrange it neatly on the form beside the isPayee checkbox. Now you can mark records as payees and assign them to categories. For instance, you might identify one record as a utility payee and another as a vendor.
At this point, there is nothing stopping someone from selecting a payee category for records that are not payees, which is not ideal. It would be better if you could hide or disable the combo box unless the isPayee checkbox is selected. This requires a bit of VBA, but it is fairly simple.
To achieve this, you will want the combo box for payeeCategoryID to be visible only when isPayee is checked. This involves writing a small subroutine that changes the visibility of the combo box based on the checkbox's value. Attach this code to the After Update event of the isPayee checkbox and the On Current event of the form, so that the combo box visibility updates both when the checkbox is clicked and when navigating through records.
Remember to give your combo box a clear, proper name like payeeCategoryCombo to avoid confusion in your VBA code.
Once you have this set up, the payee category combo box will appear whenever a record is marked as a payee and will disappear otherwise. You may also want to consider whether to clear the payeeCategoryID value when deselecting isPayee; this is optional and depends on your workflow.
There is much more to cover, and this tutorial is just the first in a series where we will build out all the features necessary for a complete accounts payable system.
For more information on building comprehensive accounts payable systems, I invite you to check out my Access Payables Seminar, which covers this series in more detail and adds even more topics.
That concludes part one of this series. If you would like to see complete video tutorials with step-by-step instructions for everything discussed here, you can find them on my website at the link below. Live long and prosper, my friends.Topic List Introduction to accounts payable concepts
Differences between accounts payable and receivable
Using a single table for customers and payees
Adding an isPayee field to the customer table
Creating a payeeCategoryID field for categorizing payees
Adding a companyName field for organizational contacts
Setting up the payeeCategoryT table with sample data
Modifying the customer form to include company name
Updating tab order on customer form
Adding an isPayee checkbox to the form
Creating a combo box for selecting payee category
Storing selected payeeCategoryID in customer table
Making the category combo box appear only for payees
Writing VBA to hide or show combo box based on isPayee
Using After Update and On Current events for form controls
Renaming controls for VBA clarity
Demonstrating form functionality for marking payees
Explanation of handling payee category values when toggling |
