Accounts Payable 6
By Richard Rost
11 months ago
Build an Accounts Payable Database in Access, Part 6 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. 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, 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
Intro In this video, we continue building our Microsoft Access accounts payable system by focusing on enhancing the payables report. I'll show you how to add a print preview button, create and format group totals using the Sum function, customize currency formatting without decimals, and bold key footers. We'll cover adding report-level totals both for each payee and for the entire report, discuss naming your calculated controls for clarity, rearrange the report layout to add a total due column, and explore hiding the detail section for a summary-only report. This is part 6.Transcript Welcome to another TechHelp video brought to you by AccessLearningZone.com. I'm your instructor, Richard Rost.
Today is part six of my accounts payable series. If you haven't watched parts one through five yet, go watch those and then come on back.
Last time we worked on our payables report, which is looking pretty good. Let's put a button on here. Actually, let's put the button on here to print the report, so design view, and we'll put it right down here.
Form design. Button, job the button. Report operations. I like to preview it before I print it, so I'm going to go to the preview report, the payables text, and we'll just put in here next and then payables report button, and there we go. Save it, close it, open it, open it. Beautiful.
Let's go back. Whoops. That's the problem of maximizing. If you don't un-maximize it, then your form stays maximized too.
Let's take a peek now at what we got. I want to make some totals.
We can turn this guy off by the way. Don't click that X. I hate how they did this, one of my pet peeves of Access. Hey, Access team, change this. This is too close and similar to that. This means to close the grouping pane. This deletes that grouping level or sorting level. I hate that. Do something different, put it over here maybe.
Make sure you click on that guy to close that grouping pane.
I am going to copy one of these guys. Copy, click, paste. We had this discussion in the forums by the way. Let me delete that. Somewhere, somehow with Windows 11 and later versions of Access, you can't copy and paste anymore. See, copy, click, paste. It won't let you copy multiple text boxes. I don't know when this happened. I don't know how to fix it. It's some kind of bug. It's floating around and people are aware of it, but you have to do it one at a time. It stinks. Copy, click, paste. Slide you over here. If you want to put a line in here you can.
Report design. This is the kind of fluff that I usually add later, but while I'm thinking about it sometimes I do it. So I'll put a little line right there and drag it out. There we go.
Okay, now this guy is... let's take a look at its data. It's past due.
Okay, so this guy is going to be equal to the sum of past due. Not DSum, just the regular Sum function. It's summing everything above it. So for a group footer, it's everything in that group.
While you're at it, change this. Let's call this group past due. Or how about let's go Payee Past Due, because this is for the payee past due. That makes more sense.
Save it. Let's take a peek, make sure that's working. Okay, good. 100. All right, zero, zero.
We just need to do a little formatting now, right? So back to here and let's go format as currency. Make sure that works. Yep, looks good.
A lot of times for these reports I set decimals to zero, because I just hate seeing all these .00s all over the place. Unless it's important to you. For me, personally, usually for a report like this, just like the IRS, I don't care about pennies.
So I am going to select all of these. I'm holding down the shift and clicking individually. We're going to set decimal places to zero. And then take a peek and that looks better.
Maybe for these footers here we'll bold.
All right, copy this guy, copy, paste. See, copy, click, paste. There we go. It's not you, it's not me, it's just weird.
All right, equals the sum of Due in Seven, and this will be Payee Due in Seven. And then we'll bold it.
Now, then we'll do the same thing for these guys. Copy, paste. See, copy, paste. There we go.
Equals the sum of Due in 30, Payee Due in 30. Bold it.
One more, copy, paste. What did it do the first time, then? There.
Equals the sum of that, and it's the Payee Over 30. Bold it, save it, close it, open it, open it, and looks good. Beautiful.
We have to format as currency, of course. Right here, right-click, design, maximize, click, click, click. Format. These guys don't have a dedicated format in here because they're inheriting the format from the query, but you can, if you want to, come in here and just force that to currency as well.
All right, save it for preview, looks good.
How about a total down here at the bottom for the entire report?
Well, it's going to work pretty much the same way. We're going to put it in the report footer, not the page footer. It's possible to do page footer calculations, but it's not easy. It requires some tricks.
You can just put these guys down here.
Again, it's a bug somewhere. Copy, paste... oh, it worked that time. See, sometimes it works, sometimes it doesn't. It's really weird. I'm glad that it worked that time though.
All right, we'll stick that there and I'm going to leave room for a thick line on top of it. I'm going to take this guy, copy it, click down here, paste... see, copy, paste, and it's not pasting. Copy, paste, paste... oh, it's driving me crazy. Make a new line. Access team, get on this. Driving me crazy.
Okay, then we'll go to format, shape outline, and we'll make it thicker.
Now as it is right now, that should be good enough. See, there's your totals.
There's one thing that I like to do though. You don't have to do it, but I like to name these guys properly because they come in as Text15, Text16, and so on. Let's call this Report Past Due. This one will be Report Due In Seven. Report Due In... it's just proper database technique, people. Report Over 30.
I'll be completely honest, I didn't always use to do this. I only usually really bother naming a text box or a control in general if I am going to refer to it, like if somebody else needs it for its value which, as you're going to see in a minute, someone will.
That's when I usually bother naming them.
All right, so we've got our totals going here for each client. We've got our totals for the report as a whole. Now we need one more, is over here. This report or this total going this way so you see the total amount of money due to that payee.
Now this one's actually, in some ways, easier. I am going to slide all of this stuff because you see my report cuts off here because I've got it set to 8.5 by 11, so 8.5 over, and it's just shy of that because I got quarter inch margins.
So we're going to take all of this and just slide it a little bit to the left so I got room for one more field in there, which means you got to get smaller, which means you guys got to get bigger, so you come a little bit further over there.
Then we're going to copy this... actually, not this. We're not going to copy this guy. We're going to copy this guy.
We're not going to actually have a number here. You could do a number here in the query. You could do the calculations here in the report or do them in the query. In fact, now that I'm thinking about it, it's usually better to do them in the query.
All right, so let's put this guy on pause. Let's close this.
Let's go back to our payee or our payables query and let's put that calculation in here because we got all the fields in here.
Over here, this is just going to be... let's zoom in, Shift+F2. I'm going to move this so we can see what's behind it. Total Due is going to be the Past Due plus Due in Seven plus Due in 30 plus Over 30, and it's literally that simple.
I like having it here because you can actually sum it up now in the report as well. So there's our Total Due. This should be this going across. Yep.
Let's clean this up just a touch by taking the Payee and moving it over here, and the Paid Dates, since it's always null, you don't even have to see that. You can turn that off because we don't need it to carry through to the report.
Looking good, looking good.
All right, so let's close this and go back to our report in design view and maximize it, and now we have another field we can work with.
I'm just going to copy and paste this guy, copy and paste this guy. It's really annoying, and we're just shy. I'm going to slide everybody over one more... these aren't pixels.
Now this is going to be the total.
We'll come into our label up top, Total.
Open up, this is going to be the Total Due.
This guy, copy, paste.
Copy, paste, there we go.
Slide you over here.
This is going to be the sum of Total Due, Payee Total Due.
Then copy, paste.
Right there.
What do we call you? Report Sum of Total Due, Report Due.
Save it, preview it, and there we go.
So you totally owe $4,551 to everybody in your database.
A lot of times people don't want to see all these details; you just want the summary information. All you really have to do is just hide the detail section if you don't want all that detail.
Watch: come in to Detail, change Visible to No.
Save it, now print preview it and now all you're getting is the totals. Same, it's just the totals for each one due. If you don't want to see the detail, just turn it off. It's easier to have it in there to make all those calculations.
You could make an aggregate query instead and then base the report off an aggregate query, but this way you've got the details if you want it. With a little VBA, you can turn that detail section on or off based on a preference in the database. We make a checkbox "Show the details or not." We'll do that when we get to some VBA stuff.
You can also control things like "just give me the detail report for one payee." There's all kinds of cool stuff you can do. You can do it now without VBA, but you have to make multiple reports and you're going to have three or four of the same exact report, just with little minor tweaks in it, instead of having one report and then doing the little tiny tweaks with VBA. It saves you a lot of time that way too.
Here's another video I mentioned last time if you want to go learn more about how these totals work. You can do all kinds of things on here: you can use Count, you can use Sum. There's a bunch of different functions you can use.
That's going to do it for part six. Now, that is the end of the expert level videos. I am going to do one, possibly two more videos with some VBA tricks for the developer students, so we'll cover that tomorrow in part seven. Tune in tomorrow, same bat time, same bat channel. Members, of course, you can continue watching right now.
In the meantime, before tomorrow, if you don't know VBA and you want to learn a little bit, go watch this video first. It's about 20 minutes long, it'll teach you everything you need to know to get started with VBA.
But as of right now, you've got a fully functional accounts payable system for your database with no VBA code required.
Tomorrow, part seven. We're going to do some more cool stuff.
That's going to do it for today, folks. Thank you for watching this TechHelp video. Hope you learned something. Live long and prosper, my friends. I'll see you tomorrow for part seven.
TOPICS:
Adding a print button to the payables report
Using form design view to add and configure buttons
Setting up preview functionality for reports
Understanding and managing the grouping pane in Access reports
Summing values in group footers using the Sum function
Renaming calculated controls in reports for clarity
Formatting report totals as currency
Setting decimal places for currency display in reports
Bold formatting for group footers in reports
Copying and pasting controls in Access report design
Summing different due periods in group footers
Adding report-level totals in the report footer
Formatting lines and shapes for report emphasis
Renaming report footer controls for totals
Adjusting report layout for additional columns
Calculating total due across multiple due periods in a query
Adding total due field to the payables query
Hiding unnecessary fields from reports
Copying, pasting, and aligning fields for new total columns
Summing total due by payee in reports
Summing total due for the entire report
Hiding the detail section for summary-only reports
Using aggregate queries as an alternative reporting method
Toggling report detail visibility using control properties
COMMERCIAL:
In today's video, we're continuing with part six of the accounts payable series. We will learn how to add a print preview button for your payables report, create group totals using the Sum function, and format your totals as currency with zero decimal places. You will also see how to add bold footers and set up report-level totals for each payee and for the entire report. We will discuss the quirks of copying and pasting controls in Access, how to name your controls for better clarity, and even how to hide report details for a clean summary view. By the end, you will have a fully functional payables report with all the essential totals and formatting, ready for future VBA enhancements. You will 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 primary function of the button added to the report in this tutorial?
A. To close the report
B. To print or preview the payables report
C. To open a new form
D. To sort the payees alphabetically
Q2. Why is the Sum function appropriate for calculating totals in group footers?
A. It only works on queries, not reports
B. It sums values within each group, such as by payee
C. It adds up all report totals, regardless of grouping
D. It only works on individual records
Q3. What formatting change did Richard suggest for numeric currency fields in the report?
A. Displaying three decimal places
B. Displaying numbers as percentages
C. Removing decimals to show whole currency amounts
D. Showing numbers as scientific notation
Q4. Why is naming controls (such as text boxes) properly on a report considered best practice?
A. It improves the visual appearance of the report
B. It is only necessary for controls that will be referenced elsewhere
C. It prevents errors in queries
D. It is mandatory for report printing
Q5. Where should you place the overall report totals in an Access report?
A. In the page header
B. In the group footer
C. In the report footer
D. In the detail section
Q6. What is the benefit of placing total calculations in the query rather than in the report?
A. The report cannot calculate totals
B. Totals are always more accurate in queries
C. The calculations become available for other objects and are easier to sum in the report
D. Calculations in queries reduce report file size
Q7. How can users choose to display only summary information and hide the detail section in a report?
A. Delete the detail section
B. Change the Visible property of the detail section to No
C. Remove all grouping
D. Sort the report by total amount
Q8. What is one potential workaround mentioned for providing different levels of detail without using VBA?
A. Creating multiple reports with slightly different setups
B. Printing the report multiple times
C. Using macros only
D. Sorting the query differently
Q9. What is the primary reason the copy-paste bug (inability to copy multiple text boxes) is discussed in the tutorial?
A. To explain a feature request to Microsoft
B. To warn users about a possible Access glitch when designing reports
C. To demonstrate keyboard shortcuts
D. For humor
Q10. Which of the following aggregate functions can be used in Access reports for calculations mentioned in this tutorial?
A. Average only
B. Count and Sum
C. Median only
D. Min and Max only
Q11. What is the advantage of using VBA to control report features, according to the video?
A. It always makes reports run faster
B. It allows toggling details on or off with a user preference, preventing the need for multiple similar reports
C. It eliminates the need for data entry forms
D. It reduces the size of the database
Answers: 1-B; 2-B; 3-C; 4-B; 5-C; 6-C; 7-B; 8-A; 9-B; 10-B; 11-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 AccessLearningZone.com continues our Accounts Payable series in Microsoft Access. This is part six, so if you have not already gone through parts one through five, I recommend you start there to ensure you have all the necessary groundwork in place.
Previously, we focused on building the payables report. In this lesson, I'm going to show you how to enhance that report with several useful features, such as adding a print preview button, displaying group and report totals, formatting data for clarity, and optimizing the report layout.
First, we will address adding a button to preview the report before printing. It's often good practice to give users a preview option to verify the data before sending it to the printer. The button can be placed at a logical spot on the form, and I find it helpful to assign it clear text like "Payables Report" so users know its purpose.
A quick note on navigation: if you've maximized your forms or reports in Access, always be cautious, because the next form you open will remain maximized until you manually restore it. This can occasionally cause confusion or disrupt your workflow.
Next, I want to talk about creating totals in your reports. First, be careful when working with the grouping and sorting pane in Access report design. Access can make it very easy to accidentally delete a grouping or sorting level instead of simply hiding the grouping pane. Access doesn't always make these buttons distinct enough, which I find frustrating, so always double-check what you are clicking on to avoid unnecessary headaches.
To add group totals, I usually copy an existing text box and paste it where the total is needed. However, a recent issue has arisen where Access, especially on Windows 11 or newer versions, sometimes does not allow you to copy and paste multiple text boxes at once. This bug is widely recognized, and unfortunately there does not seem to be a fix at the moment, so just copy and paste one control at a time. It is a bit tedious, but it gets the job done.
For formatting your report, visual boundaries like lines between sections or bolding totals can make the report easier to read. Adding a line or bolding the footer totals is usually something I do during the cleanup phase, but sometimes it helps to add these touches as you go.
When setting up totals, use simple aggregate functions like Sum to total amounts within a group. For example, if you have a group for each payee, you can use Sum to total their past due amounts, amounts due in seven days, and so on. When naming the controls that hold these totals, use descriptive names such as PayeePastDue or PayeeDueInSeven. It is good practice to name your controls clearly, especially if you will reference them elsewhere in the report or through VBA.
Formatting currency fields is another area that deserves attention. By default, Access may display currency with two decimal places. In most cases, for summary reports like these, you may not want all those decimals showing everywhere. I typically set the decimal places to zero for a cleaner look. You can do this by selecting all relevant controls and setting their Decimal Places property accordingly.
For each category (Past Due, Due in Seven, Due in Thirty, Over Thirty), copy a control from one of your previously formatted boxes, paste it, and adjust the control source and label appropriately. Make these bold if you want them to stand out as footers.
The next step is adding grand totals for the entire report. These go in the report footer, not on a page footer, since calculating totals per page takes extra effort and tricks in Access. Sometimes, Access can be unpredictable when copying and pasting controls - it works one time but not another. If necessary, create new controls from scratch.
Adding a bold, thick line above your grand totals can help visually separate the summary information from the rest of the data. Again, use meaningful names for these grand total controls, like ReportPastDue or ReportOver30, to make future reference easier.
Once you have group and overall totals, you may also want a column that sums across each row, giving you the total amount due per payee. I recommend performing this calculation in the query itself by adding a new field that sums the PastDue, DueInSeven, DueInThirty, and OverThirty columns. Having this in the query makes it much easier to display and sum up within your report. After adjusting the layout to make room for this new field, simply add it to your report and set up the corresponding totals.
If you want your report to show only summary information and hide all the underlying detail rows, you can simply set the Detail section's Visible property to No. This is useful for summary reports where only totals are needed. If you change your mind and want the details back, it is just a matter of toggling the property again.
Alternatively, if you want more flexibility, you can create a simple VBA routine that hides or shows report details based on a user preference, such as a checkbox. This way, you can use one report for both detailed and summary views without having to create multiple reports with only small tweaks in each.
If you want to learn more about working with totals in Access reports, I recommend watching my dedicated tutorial on that topic, where I cover functions such as Count and Sum in more depth.
This concludes part six of the Accounts Payable series, which wraps up the expert level content. In the upcoming lesson, part seven, we will explore some VBA techniques for those who want to take the system further. If you are new to VBA and want a crash course, I recommend watching my introductory video on VBA to get up to speed before the next lesson.
At this stage, your accounts payable system should be fully functional without any VBA code required.
For step-by-step instructions and the complete video tutorial on everything I covered here, please visit my website at the link below.
Live long and prosper, my friends.Topic List Adding a print button to the payables report
Using form design view to add and configure buttons
Setting up preview functionality for reports
Understanding and managing the grouping pane in Access reports
Summing values in group footers using the Sum function
Renaming calculated controls in reports for clarity
Formatting report totals as currency
Setting decimal places for currency display in reports
Bold formatting for group footers in reports
Copying and pasting controls in Access report design
Summing different due periods in group footers
Adding report-level totals in the report footer
Formatting lines and shapes for report emphasis
Renaming report footer controls for totals
Adjusting report layout for additional columns
Calculating total due across multiple due periods in a query
Adding total due field to the payables query
Hiding unnecessary fields from reports
Copying, pasting, and aligning fields for new total columns
Summing total due by payee in reports
Summing total due for the entire report
Hiding the detail section for summary-only reports
Using aggregate queries as an alternative reporting method
Toggling report detail visibility using control properties |
