Accounts Payable 3
By Richard Rost
11 months ago
Build an Accounts Payable Database in Access, Part 3 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. MembersThere 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!
PrerequisitesLinksRecommended CoursesUp Next
Keywords 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
Intro In this video, we'll continue building your Microsoft Access accounts payable database by adding a button to open the payables form, applying conditional formatting to highlight bills coming due or overdue, and fixing common issues with field references in your formatting expressions. You'll also learn how to use the DSum function to show totals due in the next 30 days on your main menu and display amounts owed to each payee, with tips for handling null values and adjusting criteria for accurate results. This is part 3.Transcript Welcome to another TechHelp video brought to you by AccessLearningZone.com. I'm your instructor Richard Rost. Today is part three of my accounts payable series. If you have not watched parts one and two, go watch both of those and then come back.
We have our payee set up. We have our payment page or our bills that are due page. Let's take a moment to work on this form a little bit.
The first thing I want to do is add a button over here to open up that guy. You can use the wizards. I prefer using VBA, but since I promised I was going to try to use as little VBA as possible for the expert users, we're going to go into form design, find a button, drop it down here, form operations, open a form, our bill F, open the form and show all records. We'll call this payables. Next, give it a name, payables button, and finish.
And there we go. That actually, I think, is faster with VBA. Save it, close it, open it, click it, there we go. Later on, when we do get to a little VBA at the end of the series, I'm going to show you how you can open this form and show just the stuff that's due soon, hide the stuff that's paid, filter it, all kinds of stuff. We'll get to it.
One thing I'd like to do is put a little bit of conditional formatting on here because nothing is worse than being surprised by a bill that's due in three days. You need to realize it. This usually happens to me around the start of the month, like something might be due on 7/3, and it's currently 6/29. I don't realize, oh, crap, that's due in a couple days. So let's put some conditional formatting on here.
Let's make this one due in a couple days. Let's see, today is the 11th. Let's make this one due on the 15th. And this one's good to do on the 7th or on the 2nd.
So, design view. Click on due date, format, conditional formatting. If you've never done any conditional formatting before, go watch this video. This stuff is really powerful. You can do a lot with conditional formatting. In a few minutes, we're going to use an advanced form of conditional formatting called "expression is." You'll see that in just a few minutes. Here are two videos. I'll put links to these down below if you want to learn about these in more detail.
Let's just say if the due date is within seven days, I want to see it red. So new rule. The field value is, let's say, less than. Today's date is date() with an open and closed parenthesis. That's important to include in here so that Access knows it's the date function and not some value or the text "date." You need to open and close parentheses. Plus seven. So the date is less than seven days from now. Set the format as red with white foreground and bold. Hit OK.
Let's do a second one. New rule. Same thing. Value is less than date() + 30. Now we'll go yellow, just a yellow background. All right. It's due within a month. It's coming up. It's not due immediately. Save it, close it, open it. Where's my button? Payables. Look at that. That's perfect. That's exactly what we want.
Simple conditional formatting.
Now, once I pay this bill, let's say I pay it today. I don't want it to still show up as red. It'd be nice if that conditional formatting went away when it's marked paid. To do that, we have to reference a different field. So, we can't just use "the field value is." We have to use an expression.
Again, click on due date, go to format, conditional formatting. Let's delete these two rules. Delete, delete. Add a new rule. This time, we have to go "expression is" and put our expression in here. Now, what's the expression going to be? Take a moment, see if you can figure it out yourself. Pause the video and then come back.
Here's what I came up with. Ready? We're going to start with IsNull([PaidDate]) and [DueDate] < Date() + 7. PaidDate has to be null or empty, and the due date is less than seven days in the future. Set your colors. Make it bold. Hit OK. Let's just test this one. Save it. Close it. Open it. Make sure that's null.
Wait a minute. I'm not getting any colors. What's going on here? Let's take a look and see. This is one of my pet peeves with Access. It's been on my pet peeve list for years. Let's go back into here. Look what happened. Can you see it? Do you see what change Access made? This has been on my list for the Access team to fix for years, and it still bothers me. Notice what it did. Access put quotes around our field names. So now it's being treated as an actual text string.
I always teach in my beginner lessons that if you don't use spaces in your field names, you don't have to worry about putting brackets around everything. This is one of the exceptions, and it drives me nuts. So, come in here now and get rid of these quotes and put square brackets around the field names. So, [PaidDate], square brackets there, and then [DueDate], square brackets around that.
Now hit OK. Hit OK. Save it. Close it. Open it. And there's my color.
We'll do the same thing for the yellow now. So open this guy back up. Format, conditional formatting. I'm going to open this guy and just copy this to the clipboard, right, control C. Then we'll add a new rule. "Expression is," paste that in there. Change that 7 to a 30. Drop that down, go with the yellow. Hit OK. Hit OK. Save it. Close it. Open it. Beautiful. As soon as one of these gets paid, that color should go away.
I have to fix my tab order. Go to form design, tab order, auto. I see I got a Combo68. I hate that. Hit OK. This should be, open this guy up, go to "All," change this to PayCombo. Combos are like the snack. There we go. Looking good.
Again, one of the things I'm going to teach you a little bit later on, when we get to the VBA lessons, is how when we open up this bill form, we're going to have it start by not showing stuff that's paid. You can click a button down here that says "Show me everything," because normally when you open this up, you just want to see what's coming due.
If you know your formatting, if you know your filtering and stuff, you can do that without any coding. Or you can come in here and say, I just want to see the stuff that's not paid. Right-click here and go equals blank. There you go. But that's just more clicks every time you open the form. It'd be nice to have the form show up the way you want it. Same thing with sorting. You want to sort it by the due date. Right-click and then sort newest to oldest, for example, to show the most recent stuff up top and then also add a filter.
There is all kinds of stuff you can do. You can make these headers so you can click on the header buttons and it will sort based on that row. I have separate videos on that. There's all kinds of stuff you can do once you learn a little bit of VBA.
Let's do one more thing in today's lesson. How about on the home screen, on the main menu, let's put how much is coming due within the next 30 days. That's always a helpful metric to see on your main menu. I have so many bills due. I used to always do this, so I knew I had a thousand dollars coming up in the next week and I had to panic.
Design view for this. We're going to use the DSum function. DSum is a close cousin of the lookup and lets you look up a value in a table or query. This lets us sum up a value based on a table or query with a little criteria.
Let's take a look at our bill table. What we want to do is sum up the AmountDue where PaidDate is null and DueDate is less than 30 days in the future, or 7 or whatever you want it to be.
Let's go over here and we'll just make ourselves a little box. In fact, I'm going to just shrink this up a little bit. We don't need this so much. We'll use it later maybe. I'm going to copy and paste this guy, copy, paste. We'll put in here "Due in 30." Open up the properties for this box. Let's change the name to DueIn30. The control source, let's get rid of that. We're going to put something in there, so I can get rid of the format, make that currency. Now, control source, I'm going to shift F2 to zoom in. I find it easier to work in this unless you need the IntelliSense, because you don't get IntelliSense in the zoom window. That's why I like to go in the table first and take a look and see what.
This is going to be DSum. What field are we summing up? AmountDue. And I can't type today. From the Bill table, where PaidDate is null and DueDate is less than Date() + 30. In here you do not need the square brackets because this is going to the DSum function. Hit OK. Save it. Close it. Open it. There we go. We got $100 due in 30 days.
If we were to go back into here and mark this guy not paid, let's just get rid of this. Close it. Now, this isn't going to automatically refresh on its own. You can make a refresh button with a little VBA, or you can just hit F5 and it'll refresh. Now you can see that it's updated. We got $2,100 due in 30 days. There's also a refresh button up on the ribbon here, right there, refresh.
What if we want to do the same thing for a particular customer? If you open up a customer, you want to see how much you owe to them. I do the same thing with accounts receivable. I have unpaid invoices, how many invoices this customer has that they owe us, but you can go the same way with payees. Just borrow this box. Copy this. Let's go back to the customer form. Here it is. Design view. Let's just paste that in here. Slide down.
Let's use a little format painter, due in 30. Slide you over like so. Now, we just have to make one little modification to this guy. It's all the same stuff, but we need one more criteria. Can you think of what that might be? Or, better yet, in fact for this guy you might want to even get rid of the due date and just see how much this guy owes you. Maybe that's past due, right, where the due date is less than today's date so they owe you this money and it's past due.
But here you have to add "and CustomerID equals the current customer." So, I'm going to go outside the quotes and go "and CustomerID =" & [CustomerID]. That will throw the ID into there and concatenate it with the string, so you get CustomerID=1.
Hit OK. I'm going to change this label here to say "Past Due." Past Due. We might as well change the name of the box too. Right-click, click, PastDue. Save it. Close it.
You can put all kinds of stuff on the main menu too. I used to have "Due in 30," "Due in 7," "Past Due" up here. Then I knew, hey, I got to get on the phone, start making some phone calls with some deadbeats. Well, that was for accounts receivable. I have to know who I have to hide from because I'm a deadbeat. No, I've always paid every one of my bills precisely on time.
Save it. Close it. Main menu. Let's see who owes us money. Payables. I am the one that owes money and let's make it past due. So, let's come in here and set it to due on 5/15. Close it. Let's open up me.
Past Due, hundred bucks. That was correct. Yep.
Now, let's say Jean Lucas is past due. Well, let's say Jean Lucas is not past due, so he technically doesn't owe that 2000. I'll find Jean, go here. Jean.And there we go. Oh, oh, oh, oh. What's going on here? Why am I seeing a hundred dollars? Well, let's take a look. What did I do? Okay. This is very interesting. I'm glad I caught this.
Here's the problem. I'm just going to tell you what I did because you're going to run into this eventually, but maybe not today.
Now we're dealing with the bill table. Okay. And I'm saying in the bill table, I want to see where the customer ID equals whatever number we're on - one, four, six, whatever. Okay, that's fine. But let's take a look at the bill table. The bill table doesn't have a customer ID. The bill table has a pay ID.
So this is one of the issues you have to be careful of if you're going to name a field differently. In our bill table, it's called pay E T or pay E ID, but this basically represents a customer. So make sure in your formulas that you adjust accordingly. This has to be pay ID equals whatever customer ID you're on.
You run into the same thing if you've got a vendor table, employee table. They're all stored in your person T, but they're considered vendors or employees or whatever. You run into that. If the field is unknown, you might get whatever random data back.
But now you can see there we go. You might not want a blank field there, so we can always wrap that in NZ. If there are no records, give me a zero.
Okay. Save it. Close it. Open it. And there we go. Pretty cool stuff, huh? All right. So tomorrow we're going to start attacking our accounts payable report. We're going to do a little aged report and see who owes us money in 30 days, who owes us money now, who is past due, who do I have to sic the collection agency on, that kind of stuff.
So tune in tomorrow, same bat time, same bat channel. Or members, you're going to watch it right now because I'm going to keep recording today.
So that's going to do it for part three. I hope you learned something. Live long and prosper, my friends. I'll see you tomorrow for part four.
TOPICS:
Adding an Open Form button to a form
Naming command buttons in Access
Conditional formatting based on due dates
Using the "expression is" option in conditional formatting
Correcting field references in conditional formatting expressions
Conditional formatting to exclude paid bills
Updating conditional formatting rules for multiple criteria
Fixing tab order in a form
Renaming combo box controls
Filtering records by blank PaidDate
Sorting records by due date in a form
Using DSum to total amounts due within 30 days
Creating controls to display calculated sums
Applying currency formatting to calculated fields
Refreshing form data to reflect updates
Using DSum criteria with multiple conditions
Displaying amounts due by specific payee on a form
Modifying DSum criteria for different fields
Using NZ to handle null sums in calculated controls
COMMERCIAL:
In today's video, we're continuing with part three of the accounts payable series. We'll work on improving your bill tracking form by adding a button to quickly open payables, set up conditional formatting to highlight upcoming and overdue bills using expressions, and fix common Access issues with field references. You'll also learn how to show the total amount due in the next 30 days on your main menu using DSum and display amounts owed to each payee, while avoiding mistakes with field names in your formulas. 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 feature was added to the bills due form to allow users to open the related payables form?
A. A hyperlink field
B. A command button using the button wizard
C. A VBA macro that automatically opens the form
D. An embedded subform
Q2. When using conditional formatting to highlight due dates within seven days, which function is used to represent today's date in Access?
A. NOW()
B. TODAY()
C. Date()
D. CURRENTDATE()
Q3. What is the main purpose of using conditional formatting on the due date field in the bills due form?
A. To automatically pay overdue bills
B. To visually warn users of upcoming due dates
C. To filter paid and unpaid bills automatically
D. To generate payment reminders via email
Q4. Which conditional formatting rule was set to highlight bills due within a month (30 days)?
A. If due date is between Date() and Date() + 7, format as yellow
B. If due date is less than Date() + 30, format as yellow
C. If bill is paid, format as yellow
D. If bill amount is greater than $1000, format as yellow
Q5. What additional criterion needs to be checked in the conditional formatting rule to ensure paid bills are not highlighted?
A. [DueDate] < Date() + 7 only
B. [PaidDate] is not null
C. IsNull([PaidDate]) and [DueDate] < Date() + 7
D. [PaidDate] = Date()
Q6. What common mistake did Access make when inputting expressions for conditional formatting?
A. Swapped field names with values
B. Left out required parentheses
C. Added unnecessary quotes around field names
D. Put fields in reverse order
Q7. What is the purpose of using square brackets around field names in Access expressions?
A. Only for fields with spaces in their names
B. Required for all field names in any expression
C. Makes it compatible with VBA
D. To avoid using parentheses
Q8. When summing the AmountDue for bills coming due within 30 days, which function was used?
A. SUMIF
B. DCount
C. DSum
D. SUM
Q9. When writing the DSum criteria to sum unpaid bills due within 30 days, which of the following is correct?
A. "PaidDate is null and DueDate < Date() + 30"
B. "[PaidDate] <> null and DueDate > Date()-30"
C. "PaidDate = 0 and DueDate = Date() + 30"
D. "PaidDate not null, DueDate < Date()"
Q10. How can you refresh the main menu screen to show updated totals after paying a bill, without using VBA?
A. Reopening the database
B. Clicking the save button
C. Pressing F5 or clicking the ribbon refresh button
D. Pressing Ctrl+Z
Q11. To limit the sum on the customer form to only what a specific customer owes, which criterion is added to the DSum function?
A. "AND CustomerID = Me.CustomerID"
B. "AND CustomerNumber = 1"
C. "AND [AmountDue] > 0"
D. "AND CustomerAge < 30"
Q12. If the bill table field is called PayID instead of CustomerID, but the DSum formula uses CustomerID, what will happen?
A. The calculation will still work
B. No data will be found or random results may appear
C. All paid bills will be included
D. The database will crash
Q13. What function can be used to return a zero instead of a blank if there are no matching records in DSum?
A. IsNull()
B. Nz()
C. NullToZero()
D. IFNULL()
Q14. How can the user sort the records in the form by the due date?
A. By dragging the field to the top of the form
B. By creating an index in the table
C. By right-clicking the field and selecting Sort
D. By applying conditional formatting
Q15. Why is it important to be precise with field names when constructing criteria in Access expressions and D functions?
A. Access automatically detects all typos
B. Mismatched or incorrect field names may cause incorrect data to display
C. Precise naming makes the forms look cleaner
D. Field names are case sensitive in Access
Answers: 1-B; 2-C; 3-B; 4-B; 5-C; 6-C; 7-A; 8-C; 9-A; 10-C; 11-A; 12-B; 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 continues my series on managing accounts payable with Microsoft Access. This is part three, so if you have not yet watched the first two parts, I highly recommend doing so first, then returning here to follow along.
At this point in the series, we already have our payee information set up and a form displaying outstanding bills. I want to take a moment to enhance this form.
The first improvement is to add a button that opens up the bills form directly. While I generally prefer working with VBA, I am keeping things simple for now so as not to overwhelm newer users. In form design view, you can use the button wizard to create a command button that opens the bill form and displays all records. Name this button appropriately so you can easily reference it again.
Now, once the button is in place, save your work, close, and test the form. The button should quickly take you to your bills. Later in this series, I will demonstrate how to use VBA to refine this process, for example opening the form and showing only unpaid bills that are coming due soon, hiding anything already paid, and applying all kinds of filters. That will come later.
Next, I want to introduce some basic conditional formatting to help you stay on top of upcoming bills. There's nothing worse than being caught off guard by a surprise payment due in a few days, especially at the start of a month. By using conditional formatting, we can make it very easy to spot when a bill is almost due.
Let's say today is the 11th. I'll set one bill due on the 15th and another on an earlier date. In design view, select the due date field and open up the conditional formatting window. If this is your first time using conditional formatting, know that it is a very powerful feature in Access. There are separate videos available with more in-depth tutorials if you want to explore further.
For now, I want to highlight any bills that are due in the next 7 days in red with white, bold text. Create a new rule where the due date is less than today's date plus 7 days, using the Date() function appropriately. For bills due within the next 30 days, I'll add a second rule with a yellow background. Save these changes and test your form. Bills due soon will stand out, making it easier to avoid missed payments.
However, we want this formatting to disappear when the bill has been paid. For that, the rule must take into account another field - the paid date. This requires switching from the simple value-based rules to using an expression. The rule should check that PaidDate is null (meaning unpaid) and that DueDate is less than today plus the desired number of days. Be careful as sometimes Access puts quotation marks around your field names when you use the wizard. Remove those quotes and use square brackets around your field names instead. Once corrected, these rules work as intended - as soon as you mark a bill as paid, the highlight disappears.
While tuning up your form, you might want to set the proper tab order so navigation is smooth, and rename any automatically generated control names like Combo68 to something more descriptive, such as PayCombo.
Eventually, we will cover how to make your bill forms open up filtered so you only see outstanding items. Without any VBA, you can manually filter by right-clicking on a field and applying a blank filter, but adding a bit of automation will make this even more efficient. Sorting by due date can also be accomplished with simple clicks, but it is possible to set better default options using macros or VBA in future lessons.
Let's add a summary box to the main menu indicating how much is coming due in the next 30 days. This is a useful metric to have front and center. For this, I use the DSum function in Access, which sums a field in a table based on criteria - essentially a cousin of DLookup. In this case, we want to sum AmountDue for bills where PaidDate is null and DueDate falls within the next 30 days. Place a text box on your main menu, set its control source with this DSum expression, and format it as currency. When you test, you'll see your total for the upcoming period. If you update data, remember the box does not refresh automatically; you can manually refresh the form, create a refresh button, or use the ribbon option.
Now, suppose you want to show the total due for a particular customer or payee, such as the amount you owe a specific vendor. Copy the summary box onto the customer form. To filter by person, modify the criteria in your DSum to match the current customer or payee. Just remember that table field names must be accurate. For example, if your Bill table uses PayID instead of CustomerID, update the expression accordingly. Otherwise, Access will not find any records, or worse, pull incorrect values. You can also wrap your result in the NZ function to display a zero when there are no records due.
With these improvements, your accounts payable management system becomes much more insightful and user-friendly. You can expand your dashboard with additional metrics such as bills due in 7 days, total past due, and more. This helps you keep on top of upcoming expenses and avoid unpleasant surprises.
Tomorrow, I will continue with an accounts payable report. This will include an aging report to show who owes you money now, what will be due in 30 days, and any overdue balances.
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 Adding an Open Form button to a form
Naming command buttons in Access
Conditional formatting based on due dates
Using the "expression is" option in conditional formatting
Correcting field references in conditional formatting expressions
Conditional formatting to exclude paid bills
Updating conditional formatting rules for multiple criteria
Fixing tab order in a form
Renaming combo box controls
Filtering records by blank PaidDate
Sorting records by due date in a form
Using DSum to total amounts due within 30 days
Creating controls to display calculated sums
Applying currency formatting to calculated fields
Refreshing form data to reflect updates
Using DSum criteria with multiple conditions
Displaying amounts due by specific payee on a form
Modifying DSum criteria for different fields
Using NZ to handle null sums in calculated controls |
