Accounts Payable 7
By Richard Rost
11 months ago
Build an Accounts Payable Database in Access, Part 7
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
In the extended cut, we will cover how to set up and manage recurring billing, including setting up a payee, entering amount, choosing frequency (monthly, weekly, etc.), tracking processed dates, and automatically adding recurring bills to your bill table. I will also show you how to track and calculate different terms for payables.
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
Keywords
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, ControlTipText double-click mark paid, hide detail section report, checkbox to toggle report details, referencing values open form, Form!billF!HideDetails, report OnLoad event hide details, interactive reports VBA, checkbox filter PayeeID report, payee-specific payables report, DoCmd.OpenReport where condition, PayeeCombo filter reports, recurring billing VBA, process recurring bills automation, terms handling accounts payable, advanced payment reports, automatic discount calculation, write check amount text VBA, pay categories between dates, building interactive payables forms
Intro In this video, we continue with Part 7 of the Accounts Payable series in Microsoft Access and move into developer-level topics using VBA. I will show you how to filter your payables form to display only unpaid items by default, replace embedded macros with VBA code, change the form's sort order, set up double-click shortcuts for marking invoices as paid, and enhance the user interface with color cues and tool tips. We will also make reports interactive by adding checkboxes to show or hide details and filter results by the current payee, all using practical VBA techniques. This is part 7.Transcript Today is Part 7 of my Accounts Payable series, and we have switched from expert to developer levels, so we are going to be doing some cool tricks using VBA today.
If you have not watched Parts 1 through 6, go watch those first, and if you have never programmed in VBA before and you want to learn how, or if you just need to refresh your knowledge, go watch this video. It will teach you everything you need to know to get started with VBA in about 20 minutes.
As we were going through the rest of the videos, I made a couple of notes on things I want to show you when we get to some VBA - some tricks you can add just to make the database a whole lot more familiar. For example, when you click on the Payables button, it is going to show you everybody. Over time, this list is going to get very long, and you really do not want to delete old stuff.
I do not delete data. You can mark it inactive or whatever, but would not it be nice if this by default just showed us the stuff that was not paid yet? And yes, you can come in here and right-click and put a manual filter on, but I just wanted to make it so that when I click the button by default, it shows me stuff that is unpaid. If I want to see the rest of it, I can just turn that filter off.
We are going to come in here, go to Design View, and edit this button. Now, the button was made with the wizard, so it has an embedded macro in there. We are just going to delete that. Click over here, and then hit Delete. Boom, that macro is gone.
Now, click back in here again and hit the dot dot dot button, and if you follow my instructions in the VBA video, the code builder pops up. Let's give it a little extra blank space. I have my code builder by default. If you see the little pop-up window that asks you which of the three builders you want, pick the code builder.
This is going to be real simple in here. All we are going to say is DoCmd.OpenForm. What's the form name? It's billF in quotes, and then comma, comma, comma, we want to use a where condition. Never mind. And what's our where condition? Well, it is IsNull([PaidDate]), and that's it. So open the form and just show us the records where PaidDate is null.
Save it, debug compile once in a while, close it, close it, open it, click it, and where I am seeing the same records. Do you know why? Because all these are unpaid. Let's mark a few of them paid.
Click here. I am going to go Control+Semicolon - that is going to put today's date in there. We will do one more. Control+Semicolon. We are going to make a shortcut for this too in a second.
Now close it, and then reopen it, and look at that. I am missing some because it is only showing us the ones that are unpaid. You can click on the Filtered button to turn that off and it shows everybody again.
Let's also change the due date. I want to see the most recent ones at the bottom, so I want to see the oldest stuff at the top. Go to Design View. I am going to go in here. This does not require VBA. Just go into the form's properties, go to Data, find the Order By. Right now it is builtE.DueDate descending, so it is backwards. Let's get rid of that. Make it regular. Just DueDate. In fact, you do not even need the builtE. You can just put DueDate in there like that. Because we only have one table in here, this guy's bound to a table.
Save it. Close it. Open it. There we go. We have our sort. We have our filter. We can turn that off. Looks good.
Now I like to make a shortcut for things like double-click here to make that paid. What I am going to do is I am going to put a double-click event in here. I am going to go to Events. I am going to find On Double Click. I am going to hit dot dot dot. It opens the screen up. I am now in the PaidDate double-click event. In here I am going to say PaidDate - that is the field - equals today's date.
You can put the open and close parentheses there, but they are not needed. In fact, VBA is going to get rid of them for you. I do not know why, but it just does. Sometimes if you have a function that does not have any parameters, it just gets rid of them. I wish it left them, but it does not.
After that, I want to refresh the record so it saves immediately to the table. I am going to say Me.Refresh. Me is the current object that you are on; in this particular case, the object is that form billF. It is just going to refresh the data, which means save everything to the table underneath and recalculate if it needs to be recalculated.
Save it. Debug compile. Close it. Close it. Open it. Double-click. And boom, we are done. It puts it there and it saves the record.
I also like to make fields that have events and stuff in them, so you can double-click. I also like to change their color just a little bit. It visually tells the user they can double-click on this thing and it is going to do something.
Come in here. Maybe go with a light blue. I like to use light blue. Then I will also make a tool tip text. Go to Other and go to Control Tip Text right there, and say "Double click to mark paid." That way, the user can just hover over it and they will get a hint as to what is going to happen.
This is blue over here. What does this mean? Double click to mark paid. Double-click.
Let's make a bunch of these "not paid" because we are going to be using them for something else in a minute. Get rid of that. Delete that. Then we'll get rid of you.
Everybody is unpaid.
Next up in the report. Let's put all those details back on. I showed you how to hide them last time in the detail section. Go to Format and Visible is No. Let's put that back to Yes.
Now, I would like to make that an option that the user can select when they go to open up the report. Let's put a checkbox right there.
Form Design. Checkbox. Drop it right there. Let's call this guy HideDetails. Make it so we can actually read it. Name the checkbox itself. Come up to the top here. There you are. Let's call it HideDetails. Set the default value on the data table. Set the default value so it is off unless you check on it.
So this checkbox is HideDetails. What is its full name? Forms!billF!HideDetails. That is knowing how to reference a value on an open form. That is kind of a whole separate video on that. I will put a link down below.
Now, we do not want to put that code in the button; we want to put it in the report. When the report opens, we want to take a look at this box and say, okay, if this guy is checked, hide the detail section.
Go to the report. Right-click, Design View. Go to the report properties, find Events, and find the On Load event. When this report loads, we are going to look at that value.
Dot dot dot. Now I am in the Report Load event. In here, I am going to say: If Forms!billF!HideDetails Then (True is assumed - equals True is always assumed), I am going to make the detail section not visible. The detail section is always Section(0). There is a name to it too. I think it is acSectionDetail. I just call it Section(0).
So now I am going to say Me.Section(0).Visible = False. In this case, "Me" is the object that we are on, which is the PayablesR. "Me" is always whatever object you are working in. We are in the report PayablesR report module. There are four modules: report modules and global system modules.
Else. Always end an "Else" for stuff like this. We are going to take this guy, copy it, and paste it, and switch this to True. If that box is unchecked, the detail section, Section(0), is visible.
Debug compile. Close it. Close it. Save it. Close it. Open it. I have the details. Now, check the box. Open it. The details are gone. See how nice and pretty. That is how you can make your reports interactive.
One more thing we can make interactive: what if you only want to see the payables for a specific payee, like just Jean-Luc's stuff? Let's add another checkbox.
I tried copying and pasting it earlier. Copy. Paste. Oh, it worked this time. Sometimes it works and sometimes it does not. It is very flaky. I was playing around with it earlier and it would not copy that. Let's call this CurrentPayeeOnly.
This is handy for accounts receivable. I show this in my accounts receivable videos, because then you can print it out. Here are all your invoices due, and send it to the client. This is handy if you want to cut one check for the customer, and you want to print it out and include it.
Let's name this checkbox CurrentOnly. Set the default value to No.
Now, this code will go in the button.
Build event. Oh, we are back to a macro. This macro was made with the report wizard. Click on this, Events, get rid of that macro. Delete.
Let's go back in, make some code. Code builder is always better.
Now, in here, we are going to say: If CurrentOnly, that is our checkbox, is true, then DoCmd.OpenReport "PayablesR". How do you want to see it? acViewPreview. Yes, messing around earlier, I forgot that one, which I always do. I hit it and of course now I wasted a sheet of paper because the default is to send it right to the printer, which I hate that too.
Where Condition? Now, what is the where condition? I only want the current payee. So PayeeID on the report equals - what field am I looking at here on this form? Click back over here. It is this guy, the PayeeCombo. You could probably use PayeeID and get away with it because it is in the underlying query, but sometimes it does not work. You are going to look at the field name - PayeeCombo. Otherwise, if it is not, we are just going to show everybody. So erase that and get rid of the where condition. That is all. Show me everybody.
Save it. Debug compile once in a while. Close it. Close it. Payables. Show me everybody. Looks good. Got the details. Got everybody.
Now let's go see just Jean-Luc. Oh, someone's beaming in. Hold on a second. See if it is more tribbles. Nope, no tribbles. Just more visitors for the starbase. Just kidding. That is why I will really chime if you are just watching for the first time.
So, Jean-Luc. Current payee only. Click the button. Look at that. There is just Jean-Luc. You could say, give me Jean-Luc and hide his details, and look at that. Just his details. See? So knowing where to put the right code and knowing how to talk to the database a little bit with some VBA - you see, very few lines of code.
What?One, two, three, four, five, six for each of those things. So that's what - 10, 12 lines of code to do something that's really cool. It saves you from having multiple reports. Or it saves you from having to right-click and change things and do stuff in here. Just check a box, and then the code handles the rest of it.
Do you like learning VBA? Do you like this stuff? Do you want to see all the cool tricks that I can show you to do with your database? I have tons of lessons on my website, my Access Developer Series. I'm up to around 52 now. Check this out - you'll find a link down below.
If you want to learn lots more about how to do payables with your database, I have an entire seminar. It's about eight hours long. It covers everything you want to know about doing accounts payable in your database. You name it, it's in here. There are all kinds of topics. Check it out - there's a whole list of stuff included.
How to take like 2 in that 10 to automatically take your discount, doing recurring billing (which we're going to do in the extended cut a little bit for the members), and more advanced payment reports. How to write checks, how to actually convert that into $18.97, for those of you who still actually write checks. I do once in a while. How to show your pay categories between dates, all kinds of topics.
This one's 11 hours long, not 8 - 11 hours long. Check it out - you'll find a link to it on my website down below.
Now, members: in the extended cut, we're going to cover recurring billing. You can set up a payee, how much you owe, what the frequency is - whether it's monthly, weekly, or whatever. It will track the date it was last processed, the next date it is due will be calculated automatically. Once a week, or whenever you log on, you click "Process Recurring Bills" and it will automatically run through these and add them to your bill table.
We will also talk about different terms. That's all covered in the extended cut for the members.
Silver members and up get access to all of my extended cut videos - not just this one, all of them. There are hundreds of them by now. Gold members can download these databases that I build in the TechHelp videos.
Everybody gets some free training and everyone's happy. So what are you waiting for? Join today.
That is going to do it for this accounts payable series. I hope you learned something. Like I said, if you want to learn more about payables, check out my full seminar. It's on my website. You'll find a link down below.
That's going to do it for today. I hope you learned something. Live long and prosper, my friends. I'll see you next time.
TOPICS:
Opening a form with a filter for unpaid items using VBA
Removing an embedded macro from a form button
Setting up DoCmd.OpenForm with a WHERE condition
Filtering records where PaidDate is null
Manually marking invoices as paid with Control+Semicolon
Changing the default sort order in a form
Using Order By property to sort records by DueDate
Creating a double-click event to mark a bill as paid
Writing VBA in the On Dbl Click event of a form field
Refreshing a record with Me.Refresh after updating
Changing the color of a field to indicate interactivity
Adding a tool tip to a control for user guidance
Restoring hidden report detail sections
Adding a checkbox to hide or show report details
Referencing form values in report events
Writing VBA in the On Load event of a report
Using Me.Section(0).Visible to show or hide report sections
Adding a checkbox to filter payables by current payee
Referencing another control on a form in VBA
Opening a report filtered by selected payee using VBA
Switching from macro to VBA for report buttons
COMMERCIAL:
In today's video, we are continuing with Part 7 of the Accounts Payable series and stepping up to the developer level. You will learn how to enhance your Access database using VBA with practical tricks like filtering unpaid bills by default, sorting your records by due date, and making quick updates to payment fields with double-click shortcuts. We will also add user-friendly touches like color-coding interactive fields and adding helpful tool tips. You will see how to make your reports interactive by letting users hide or show details or filter by specific payees, all with simple checkboxes and VBA code. In today's Extended Cut, we will cover setting up recurring billing with automatic bill processing based on payment frequency, plus working with different payment terms. 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 was the main goal of editing the Payables button with VBA in this lesson?
A. To delete old payable records automatically
B. To only show unpaid items by default when the button is clicked
C. To generate a report of all payables
D. To export the data to Excel automatically
Q2. According to the video, what is the recommended way to handle old and paid records in the payables list?
A. Delete them permanently from the database
B. Archive them in a separate table
C. Mark them inactive but keep them in the database
D. Export them to a CSV file and remove from Access
Q3. What VBA command is used to open a form with a filter applied, as demonstrated in the video?
A. DoCmd.FilterForm
B. DoCmd.OpenTable
C. DoCmd.OpenForm
D. DoCmd.RunMacro
Q4. What is the correct where condition to display only unpaid records in the form?
A. [PaidDate] = 0
B. [PaidDate] IsNotNull
C. IsNull([PaidDate])
D. [PaidDate] = "Unpaid"
Q5. Why is it important to use Me.Refresh after updating a field in VBA?
A. To sort the records on the form
B. To immediately save the changes and recalculate as needed
C. To clear the current filter
D. To print the current form automatically
Q6. What does visually changing the color of a field with an event (like double-click to mark paid) communicate to the user?
A. The field is read-only
B. The field contains invalid data
C. The field has a special action when interacted with
D. The field is hidden from reports
Q7. How can the user be given a hint about a field's special interactive action in Access forms?
A. Add a comment in the VBA code
B. Use the Control Tip Text property
C. Change the default value of the field
D. Hide the field from the user
Q8. When making report details optionally visible using a checkbox, where is the code to control visibility placed?
A. In the checkbox's Click event
B. In the report's On Load event
C. In the button that opens the report
D. In the main form's Open event
Q9. What section of a report refers to the "detail section" that can be hidden or shown?
A. Section(1)
B. acSectionReport
C. Section(0)
D. Section(2)
Q10. What VBA approach is used to make reports display only payables for the currently selected payee?
A. Using a default macro for the button
B. Adding an extra query to the report's Row Source
C. Applying a where condition referencing PayeeCombo in the form
D. Sorting the report by payee name
Q11. What advantage does using VBA for filtering and interaction in Access forms and reports provide, according to the video?
A. It makes it impossible to update records
B. It allows for more interactive, user-friendly interfaces and saves time
C. It requires users to know SQL
D. It mandates the use of macros only
Q12. In the extended cut for members, which advanced feature is demonstrated?
A. Importing payables from QuickBooks
B. Setting up and processing recurring billing records in Access
C. Building a web-based payable entry screen
D. Linking Access to a SharePoint site
Q13. When referencing a control on an open form from VBA in a report, what notation is used?
A. Forms.billF.HideDetails
B. form(billF).HideDetails
C. Forms!billF!HideDetails
D. BillF.HideDetails
Answers: 1-B; 2-C; 3-C; 4-C; 5-B; 6-C; 7-B; 8-B; 9-C; 10-C; 11-B; 12-B; 13-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 TechHelp tutorial from Access Learning Zone is Part 7 of the Accounts Payable series, and in this lesson, we move to the developer level to work with some more advanced VBA tricks for managing your Microsoft Access database.
Before jumping in, make sure you have watched Parts 1 through 6 of this series. If you are new to VBA or need a quick refresher, I also recommend watching my beginner VBA video, which covers the essentials in around 20 minutes.
Throughout the earlier parts of this series, I made notes of some useful enhancements that would make our Accounts Payable database friendlier to use. One such improvement is simplifying the way we filter our list of payables. As your data grows, the list becomes increasingly cluttered, and it is not ideal to delete older records. My approach is never to delete data; instead, mark records as inactive if you must. But what about setting things up so, by default, users only see unpaid items? While you can always apply a manual filter, it is much more efficient to make this automatic so that clicking the Payables button immediately displays only unpaid entries, and users can disable the filter to view everything if needed.
To set this up, I switch to Design View and edit the button that was created using the wizard. The default setup has an embedded macro, which I remove. After deleting the macro, I open the code builder by selecting the appropriate builder option, as explained in my VBA introduction video. The key is to use a VBA command to open the form (in this case, billF), accompanied by a where condition that filters for records where PaidDate is null. This ensures the form shows only unpaid records by default. Be sure to save and compile the code, test it, and confirm that it works by marking some payables as paid, using Control+Semicolon to quickly enter today's date. You will see that after making some records paid, reopening the form hides those records until the filter is removed.
Sorting is another simple improvement. To view older invoices at the top, adjust the form's Order By property to sort on DueDate in ascending order. This does not require VBA. Save and reopen the form to confirm the new sort order.
For added convenience, I like to provide a shortcut for quickly marking items as paid. Instead of manually typing the date, I add an event to the PaidDate field so that double-clicking it enters today's date automatically. To do this, I set up a double-click event that assigns today's date to the PaidDate field and then refreshes the record, committing the change immediately. This saves time and makes the interface more user-friendly.
It is helpful to visually cue users about interactive controls. I recommend changing the background color of the field where the double-click event is set, perhaps to a light blue, and adding a control tip text such as "Double click to mark paid." This lets users know about the shortcut.
Next, I turn my attention to making reports more flexible. In the last session, we learned how to hide or show the details section in reports. Now, we will give users a checkbox to control this setting. I add a new checkbox named HideDetails to the form and set its default value to unchecked. When preparing to open the report, I reference the HideDetails value to decide whether to display or hide the report details. This is handled in the On Load event of the report. If HideDetails is checked, I make the detail section invisible by setting its Visible property to False. Otherwise, the details are visible. Again, remember to save, compile, and test the solution.
Another improvement is allowing users to filter the report to only display payables for a specific payee. I add another checkbox, CurrentOnly, and set its default to unchecked. The logic goes into the code triggered by the report-opening button. When the box is checked, the report opens filtered for just the selected payee (referencing the PayeeCombo field from the form); otherwise, all payees are shown. This is particularly useful if you want to generate statements or payables for a single vendor or customer.
With these enhancements, you now have an interactive interface that allows users to mark invoices as paid, toggle report details, and filter payables by vendor, all with just a few lines of VBA code. These adjustments make the management of payables much more efficient and user-friendly, eliminating the need for multiple reports or manual filtering.
If you enjoy learning VBA and want to see more examples, my website has a full Access Developer Series with dozens of lessons and techniques for building robust Access applications. For those wanting a deep dive into payables, I also have a full seminar that covers every aspect of accounts payable in Access, from discounts to recurring billing, advanced reports, check writing, and more. It is quite comprehensive.
Also, in today's Extended Cut, we will explore recurring billing. This allows you to set up a payee, track amounts owed, configure billing frequency (monthly, weekly, etc.), and let Access process these on schedule, adding them to your bill table as needed. I will also cover different payment terms and other advanced topics in the Extended Cut available for members. Silver members and up get access to all the extended cut lessons; Gold members can also download the database files I use in my TechHelp videos.
You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below. Live long and prosper, my friends.Topic List Opening a form with a filter for unpaid items using VBA
Removing an embedded macro from a form button
Setting up DoCmd.OpenForm with a WHERE condition
Filtering records where PaidDate is null
Manually marking invoices as paid with Control+Semicolon
Changing the default sort order in a form
Using Order By property to sort records by DueDate
Creating a double-click event to mark a bill as paid
Writing VBA in the On Dbl Click event of a form field
Refreshing a record with Me.Refresh after updating
Changing the color of a field to indicate interactivity
Adding a tool tip to a control for user guidance
Restoring hidden report detail sections
Adding a checkbox to hide or show report details
Referencing form values in report events
Writing VBA in the On Load event of a report
Using Me.Section(0).Visible to show or hide report sections
Adding a checkbox to filter payables by current payee
Referencing another control on a form in VBA
Opening a report filtered by selected payee using VBA
Switching from macro to VBA for report buttons |
