Account Balances 4
By Richard Rost
3 years ago
Track Account Balances & Transactions, Part 4
This is part 4 of my Microsoft Access Account Balances tutorial series. In this series we will build a database to track your account balances and daily transactions.
Today we're going to recalculate the actual balance and change the DateUpdated field any time one of the other fields is edited. We'll create a DoRecalc event handler function and requery the data in place. We'll set any of those fields to zero if the user deletes them. We'll also run our DoRecalc if a field is double-clicked. We'll add the pending value to the balance on double-click. We'll work on our tab stops, and add some conditional formatting to the DateUpdated field so that we can see which accounts haven't been updated recently (current, week, month).
Members
Members will fix the DoStartup routine which I broke last week. We're going to add back in that routine to check for linked tables, but also check for accounts. And we'll upgrade our StatusBox function to change red if it's an urgent error.
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
Suggested Template
Recommended Courses
Next Video
Keywords
access 2016, access 2019, access 2021, access 365, microsoft access, ms access, ms access tutorial, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #learn, #lesson, #training, #database, recalculate in place, requery in place, tab stops, conditional formatting if date old, check status of linked tables
Intro
In this video, we continue working on the account balance database project using Microsoft Access. I will show you how to automatically recalculate your actual balance when related fields are updated, set the date the account was last checked, and use an event handler function to keep your VBA code clean and efficient. We'll also cover requery-in-place so updated records don't disrupt your workflow, set up useful tab stops, and apply conditional formatting to highlight how recent your account data is. I will also demonstrate how to handle zero values and move pending balances with a quick double-click. This is part 4.
Transcript
Today we are continuing to work on the account balance database. This is part four of, I do not know how many to come. If you have not watched parts one through three yet, go watch them now. You will find links down below.
Today we are going to recalculate our actual balance if the user changes any of the other things over here, pending or the balance reported, all that stuff. When these are changed, it will update this calculation and update the date here so you know when you checked this information.
We are going to requery it in place and create an event handler to handle that, so we do not have lots and lots of redundant VBA code. We will do some other stuff like adding the pending balance to the actual balance. For example, when you check your credit card, you have 1600 posted, 400 is pending. You go the next day, that 400 posts, you just double-click here and it moves it over there. That is all, easy.
We are going to work on our tab stops, work on some conditional formatting, and some other cool stuff today. A lot of housekeeping stuff. Get ready, here we go.
Before we get started, there are two other videos I would like you to watch first. One is on event handler functions. That is this guy right here. You can make one function and just call it like that in the event property instead of having to make separate VBA code routines for each thing you want to do. Go watch this. I will be using these today.
Go watch this video on Requery in place because when you do a requery, it will recalculate the stuff, but then it requeries the whole list. If you are down here, it pops you back up to the first one. Requery in place lets that happen without moving the focus or changing anything. Definitely go watch this video. It is a good one. I like this one.
These are both free videos. They are on my website. They are on my YouTube channel. Go watch those and then come back.
All right, welcome back. I am going to start off by opening up my database. There we go. You will see that in the last class, in the extended cut, we made it so we can automatically re-link our company file right here. I am going to switch back to Company A. You are not seeing all my data. I actually have not put that much in yet. Let us continue working on our account page.
This actual field over here is calculated, based on adding these four things up. What I want to do is, any time this is updated, like if I put a five in here, as soon as I hit tab, I want that to requery. So we are going to do requery in place. We are not just going to do a full requery because that will re-sort and reorganize everything.
We are going to use requery in place from the prerequisite that I mentioned, and we are going to make that an event handler function, which is one of the other prerequisites. I do not want to have to have tons and tons of code in my VBA.
We are going to go into design view and go to the form's code module. You can get there a bunch of different ways. I made a button up here on my quick launch toolbar to view code that puts me right in here. But you can get to that anyway. We want to go to design view. Over here, it is on the form design page too. There is a button right there; it looks like a little piece of candy, right, view code. But you can just come in here and any one of these, like on load, it puts you right into the code module. This is why I put this button up here because I go in here a lot.
We are going to make our own function in here, a public function if you want. That is fine because you could have other forms call this function if you want to later on. We will just call it public function. I am going to do recalc.
Whenever you update one of those, first I want to update the date updated value. So I know how old this data is. DateUpdated, that is my field, equals the date's date. You could put now in there if you care, if you want it to the minute, like, oh, I updated this at 3 p.m. I just care about days, honestly. I want to make sure that I do my important accounts daily. My not so important accounts, weekly. And the ones I really do not care about, I check at least once a month. We are going to have this update with some conditional formatting too.
When that is done, we are going to say Me.Recordset.Requery and you get that beep there. I do not know why, but you do. That does not come up in IntelliSense. That is the way that you requery in place. Watch that other event handler video. The requery in place video will explain what this does in more detail.
Now we are going to use DoRecalc as an event handler function. Save this. Come back out here. We are going to pick the fields that we want to have do that if their value is changed. So this guy, hold on the shift key, this guy, this guy, and this guy. Those four fields, if those are changed, I want to recalculate the balance and set the date updated.
In the After Update event, we are going to go =DoRecalc(). I will zoom in so you can see it. That is it. That is our function that we just created.
I am also going to put that in the On Double Click because what I like to do is, if I open up an account and the balance has not changed since the last time, I do not want to have to retab it. But I want that date to update to indicate that I checked it today. So I am just going to quickly double-click on the credit, or one of these fields, and it will update that as well.
Save it. Close it. Open it.
Now if I come in here and put in a 15 and hit tab, look at that. It required the calculation, the actual value changed, and the date got updated. And while I am thinking about it, let us change the tab order - or not the tab order, the tab stops. I only want to cycle through one, two, three, four fields. Because I want to go tab, tab, tab, come down to the next one as I am typing. Do that, then come down to the next one.
I am going to go into design view. I am going to select all of these, go to Other, and set Tab Stop to No. Then I am going to select just the ones that I want to make tab stops. Those four right there will set Tab Stop to Yes. I do not need a tab to here because I cannot change it. I do not need a tab to here. You can still click here if you want to change it manually. That is fine.
You can still click here and change the account name. So every day I am only going to cycle between these four things. While I am at it, I am going to take actual balance and make the background color gray, just to signify to the user that you cannot change that. That is tempting.
See now we are sitting right there, tab, tab, tab, tab, perfect.
While I am also at it, let us put that conditional formatting on the date here. I want this date to display different ways based on how old the information is.
Click on it, Format, Conditional Formatting. New rule. If Field Value is greater than or equal to the date's date (equals Date), then we will set it to a nice green color that is easy to view. That is not quite it, let us go there. Now, a little bit left. Not in your face, just a nice light gray. Maybe even a little less than that. Yeah, I am picky. Let us go here, right there. That says you are good, like that.
Oh, hello. Maybe we just needed that. Sometimes you do, sometimes you do not need that. There you go. Apply it, hit OK.
So that one is good; it was checked today. Let us bold it, too. Come back in here, click Format, Conditional Formatting, this guy, put it up.
Next one, if it is less than seven days old. New rule. Field value is less than date minus, or other way, sorry, greater than, my bad, greater than date minus 7. So that means it is not quite a week old. We will give that one a light green, but no bolding. Looks like that. That is good.
So greater than or equal to today's date, that means it is current. Greater than seven days ago, will get that color. Let us do the other ones, too. Let us do if it is less than a month. Let's go 30 days. It is easier to deal with 30 days than months. So field value is greater than date minus 30 days ago. This one will be yellow, like, check it. Been a while. Put that yellow right there. That looks good.
The last one will be if the value is less than or equal to date minus 30. New rule. Value less than or equal to date minus 30. This one will get red. I am going to go deep red. You will know you need to check.
Current, less than a week. More than a week but not quite a month, and then a month old. Apply. OK. Close it. Save it. Open it.
Let us put some sample dates in here to check. Let us make this one 7/1. That is 16 days old, that is good. I cannot tab to it, but let us make this one 6/1 - 30 days old. Perfect. Of course, now I check my planning, the balance is the same, I have not paid it. So I can just double-click on this guy and it resets the value. Looks good.
And we can sort this stuff by whatever you want to see; sort by newest, oldest. Then you can come down here and see which one you have to check. Go back to your original sort.
Shout out to one of my moderators on the website, Kevin. He caught the fact that if a user deletes this, that throws an error now. So what we are going to do is, if this ends up null, we are going to set it to zero. We can do that in our recalc thing here. We can just check all four of those fields.
So, right here: If IsNull(Credit) then Credit = 0. If IsNull(Balance) then Balance = 0. If IsNull(Pending) then Balance = 0.
And you know what? I was not paying attention before because this transaction value is going to be calculated. That is my bad. That comes out of the transaction table, so that just means we do not need events on it. We are going to get rid of this and this, and we are going to make it have that gray back. That was me not thinking. I was too busy thinking of the task at hand. And this does not have to be a tab stop. Other, tabs.
And kudos to those of you who caught that at the beginning. Sometimes my brain does not turn on until halfway through a video, and I would not have caught that if I did not look at my notes and see that Kevin found that thing.
Now, put a zero back in there. Let us put zero in pending, and now it puts it back to zero. See, if the credit is zero here, we do not get an error message. Beautiful.
One more thing I like to do with pending, and this is a little more tricky. Sometimes what happens is, say you log on to your credit card company website like Amex, and you have 1200 as your balance, 400 dollars of charges that have not posted yet. You did not make a payment today. So you go to the next day - I do not make a payment every day, usually like once or twice a week. Now that 400 from pending is going to show up cleared or posted in your balance. What I like to do is double-click here and have this get added to that.
What I am going to do is, if the double-click happens in this field, add it to the balance and make pending zero. We will need a custom double-click event for pending.
For the pending events On Double Click, we are not going to put DoRecalc like that. We are going to go into the code. We need a custom event. It is right down here. Do not forget after it, though, to do a DoRecalc - you can still call it like that here. But what I am going to say here is: if I double-click on pending, Balance = Balance + Pending, Pending = 0, DoRecalc.
So, take whatever that balance is, add it to the other one, and then zero out pending.
Save it. Come back out here. Close it. Open it.
Now, let us say I want to move this pending over there. Double-click. And it did not work.
Anybody catch the problem? Watch. Ready? Double-click. You see what the problem is? I am adding it in the wrong column. Why is my brain not working today? This is the worst I have been in a while. Balance, not credit. Jeez.
Balance = Balance + Pending.
I think I should just call it quits for the day. One more try. I have $400 sitting here. Double-click. Now that is 1600. I have to put this back before.
I think I need to get lunch or something because my brain's not firing up.
In the extended cut for the members, we are going to fix my DoStartup routine, which I broke last week. I will explain why in this video. We are going to add back in that routine to check for linked tables. So if the user starts up the database and those back-end tables are missing, it will do that check. Otherwise, if not, you click the accounts button and it just throws up an error, which we try to avoid. I decided I want to put that back in.
We are going to upgrade the status function so that if an error does exist, it will put it here and make this red instead of a normal status message, which just leaves it gray.
That is all covered in the extended cut. Mostly housekeeping status messages, and a few minor upgrades from last week, but I have to cover it, so here it is.
Silver members and up have access to all the extended cut videos. Gold members can download my databases.
So that will be your TechHelp video for today, part four. I hope you learned something, my friends.
Live long and prosper. I will see you next time.
Quiz
Q1. What is the main purpose of the DoRecalc event handler function discussed in the video?
A. To automatically link external company files to the database
B. To recalculate the actual balance and update the date when certain fields are changed
C. To perform a full requery and reset all field values to defaults
D. To update users about new features available in the account balance database
Q2. Why is "requery in place" preferred over a standard full requery in this context?
A. It deletes all data before reloading the form
B. It re-sorts the list by the most recently added records only
C. It recalculates and updates without moving the user's focus or changing the sorting
D. It always saves changes automatically before updating
Q3. Which fields are designed to trigger the recalculation and update of the "actual balance" and "date updated"?
A. Only the account name
B. Four main input fields related to balances and user input
C. All fields, including calculated and display-only fields
D. Only the transaction table
Q4. What is a key benefit of using an event handler function for recalculating balances?
A. It allows reusing code for multiple events without repetition
B. It hides errors from the user
C. It lets you disable all validations
D. It can only be used for text fields, not numbers
Q5. What does the conditional formatting on the "date updated" field help the user identify?
A. The transactions from a specific account
B. The last time the data was checked or updated using color cues
C. Which fields are required to be filled out
D. Which accounts have the highest balances
Q6. What does the red color on the "date updated" field indicate after conditional formatting?
A. The account is in overdraft
B. The data is current as of today
C. The data is at least 30 days old and needs attention
D. The field is a calculated field
Q7. Why are some fields' Tab Stop properties set to "No" in the form design?
A. To prevent users from making any edits to the database
B. To simplify data entry by cycling only through fields that require user input
C. To hide all calculated values from the user
D. To ensure data is always saved immediately
Q8. What custom behavior is added to the "pending" field using the On Double Click event?
A. Clears all form data
B. Adds the pending amount to the balance, sets pending to zero, and recalculates
C. Opens a new transaction entry dialog box
D. Subtracts pending from the balance without recalculating
Q9. Why is it important to set null fields to zero in the recalc routine?
A. To prevent errors when fields are empty and ensure accurate calculations
B. To ensure all values are always increasing
C. To convert all fields to text
D. To hide errors from the user
Q10. What happens if the user double-clicks a calculated or display-only field, such as "actual balance"?
A. The field resets itself to zero
B. Nothing, unless a specific event handler is set for that field
C. A new record is added to the database
D. The form closes automatically
Q11. When should you use Now instead of Date in updating the "date updated" field?
A. When you want the update only if the field is null
B. When you care about the time of the update, not just the day
C. When you want the field to always show tomorrow's date
D. If you only want to update on Sundays
Q12. What should be avoided when setting up event handler functions in your forms?
A. Writing a unique routine for each individual field if their actions can be consolidated
B. Making all fields tab stops for consistency
C. Using conditional formatting on non-date fields only
D. Setting all calculated fields as editable
Answers: 1-B; 2-C; 3-B; 4-A; 5-B; 6-C; 7-B; 8-B; 9-A; 10-B; 11-B; 12-A
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 our work on the account balance database. This is part four in the series, and if you have not seen parts one through three, I highly recommend that you watch those first to make sure you are all caught up. You will be able to find links to them on my website.
In this lesson, our focus is on recalculating the actual balance anytime the user updates related fields, such as pending or the reported balance. When any of these values are changed, the calculation updates immediately, and the date field records when the information was last checked.
To accomplish this, we will use a requery-in-place technique and set up an event handler so we do not clutter the database with redundant VBA code. We will also update the actual balance by incorporating the value from pending charges. This mimics the situation where, for example, your credit card has a posted balance of 1600 and 400 pending. When those pending charges clear, you can simply double-click the field to move them over. This streamlines the process significantly.
I will also show you how to refine tab stops, apply conditional formatting, and other helpful housekeeping enhancements to make the database easier and more intuitive to use.
Before diving into today's material, there are two free prerequisite videos you should watch if you have not already. One explains event handler functions, which allow you to create a single reusable function that can be called from multiple form events, keeping your code tidy. The other video covers the requery-in-place method, which lets you recalculate data in your form without resetting focus or scrolling the list back to the top. Both of these topics are crucial for what we'll be accomplishing today and are available for free on my website and YouTube channel.
Assuming you have completed those prerequisites, let's move forward. Upon opening the database, you might recall from the last class that in the extended cut, I demonstrated how to automatically re-link the company file, making it easy to switch between company data. Today, we will focus on the account management page.
On the form, the actual balance field is a calculation that sums up several other fields. The goal is to trigger an in-place requery to update the calculation every time the user changes any relevant data, such as pending or balance fields, immediately after they leave the field. Using the event handler approach covered previously, we avoid duplicating code across multiple controls.
To do this, I'll show how to access the code module for the form. There are a few ways to get there: either through a custom button on the quick launch toolbar or directly from the form's design view. Once in the module, we create a public function, for example, called DoRecalc. This function updates the DateUpdated field to the current date, letting you track when data was reviewed. You might wish to include the time as well, but for my purposes, tracking by day suffices.
Next, the function uses the Me.Recordset.Requery command to refresh the data in place, according to the methodology from the requery-in-place video. This code structure means you only need one function to handle updates across multiple fields.
To link this function to the form's fields, select the fields you want to use to trigger recalculation—such as pending, balance, and others—and assign the DoRecalc function to their After Update events. You can also assign this function to On Double Click as a quick way to confirm without having to tab through a field, allowing the date to update simply by double-clicking.
After setting this up, you will be able to enter new values, see the recalculation happen immediately, and have the DateUpdated field reflect the current date. To further streamline data entry, we adjust the tab stops so that only relevant fields are included in the tab sequence. This means you can tab through just the main input fields, skipping calculated or uneditable fields, making data entry faster and more intuitive.
The actual balance field is set to a gray background to make it clear to users that it is a calculated value and should not be edited directly.
Next, I show you how to use conditional formatting on the date field. This makes the database visually indicate how current each account's data is. For example, if the date matches today, it displays as a friendly green. If the information was checked within the past week, it uses a lighter green. For data up to a month old, it uses yellow, and anything older than a month gets red, alerting you that an update is overdue. As a further enhancement, I bold the display for current dates.
If you enter sample dates and test the workflow, you will see that old accounts highlight in yellow or red, encouraging timely reviews.
There is an important fix to mention: if a user deletes a field value, it causes an error. The solution is to add code in DoRecalc to check for nulls and set them to zero for all relevant fields. This simple safeguard keeps the form from throwing errors if data is accidentally deleted.
During my walk-through, I also clarify that transaction values are calculated from another table, so they do not need update events or to be tab stops. The actual balance and transaction value fields both have gray backgrounds, letting users know these are not editable.
Another useful feature is making it easier to manage pending amounts. Often with credit cards, pending charges eventually post to the balance. I built a feature that allows you to double-click the pending field to move its value over to the balance field and reset pending to zero—all with one action. This requires a small custom double-click event handler for the pending field.
Lastly, in today's Extended Cut for members, I address issues like fixing the DoStartup routine (which checks for linked tables on database launch) and enhancing the status message function so that if an error is detected, it displays the message in red instead of gray. These changes help handle errors more gracefully and provide clearer communication to users.
If you are a Silver member or above, you will have access to all of the extended cut videos. Gold members can download all of my database files.
That covers today's TechHelp lesson, part four. You can find a complete video tutorial with step-by-step instructions for everything discussed here on my website at the link below.
Live long and prosper, my friends.
Topic List
Updating actual balance calculation when fields change
Using a public event handler function for multiple controls
Implementing requery in place to recalculate without moving focus
Automatically updating the 'date checked' field on change
Assigning event handlers to After Update and On Double Click events
Custom double-click event for moving pending to balance
Handling null values in fields to prevent errors
Configuring tab stops for data entry efficiency
Changing background color for non-editable fields
Applying conditional formatting to the date field
Conditional formatting rules for recentness of date
Testing conditional formatting with sample dates
Debugging event logic for transferring pending balance
Ensuring actual balance updates correctly after double-click
Removing tab stops and events from calculated fields
|
