Check Register
By Richard Rost
6 years ago
Microsoft Access Check Register Database
In this Microsoft Access tutorial, I will teach you how create a basic check register. We will build a query to calculate the transaction amount, and to show whether or not each transaction has cleared the bank. We will build a continuous form for check register data input with a form footer and totals.
Shane from Sacramento, CA (A Platinum Member) asks: Any tips for keeping track of my expenses in a simple check register for Access? I'd like to be able to track which items have cleared the bank, so I know how much is actually in my account vs. what the bank says is in my account. A running balance would also be nice.
Members
I'll show you how to calculate running balances, both in a printable check register report and on our data entry form using the DSUM function.
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!
 Links
Subscribe to Check Register
Get notifications when this page is updated
Intro In this video, I will show you how to set up a simple check register in Microsoft Access to track your checking account transactions and balances. We will create a table for storing transaction details, including debits, credits, and whether items have cleared the bank. You will learn how to use calculated query fields to determine your actual and bank-cleared balances, design a continuous form to display your register, set up form footers for totals, and use After Update events to keep your calculations current as you enter data.Transcript Welcome to another TechHelp video brought to you by AccessLearningZone.com. My name is Richard Rost.
In today's video, I'm going to show you how to set up a basic check register in Microsoft Access to track your checking accounts. I'll show you how to track which items have cleared the bank so you can get a good idea of what your actual balance is versus what the bank says your balance is.
Today's question comes from Shane from Sacramento, California, one of my platinum members. Shane says, any tips for keeping track of my expenses in a simple check register for Access? I'd like to be able to track which items have cleared the bank so I know how much is actually in my account versus what the bank says is in my account. A running balance would also be nice.
Well Shane, setting something like this up in Access is very easy to do. Of course, there are lots of programs out there like Quicken and QuickBooks that do this for you, but one of the benefits of using Access is you can custom tailor it to get exactly what you want. You can enter the information exactly like you want it. You can generate reports exactly like you want it. That's why we use Access - to build stuff ourselves.
So let me show you how to set up a simple check register using Microsoft Access. Let's begin by creating our check register table.
So create table design. Start off with an ID. That will be our autonumber, of course. Every table should have one. Pretty much.
Let's do a check number. This is a short text field, and I like to use text for that because sometimes I've seen companies that had checks that had letters in them. Or some companies use it for a code. Or you could put anything you want in that field. That's just so you know what check you wrote.
Let's put in a check date. That will be a date/time. Normally I would default this to something, but I kind of don't want people having a default in that way. It forces them to type in the actual date of the check.
Let's throw a description in there. Again, short text. I like to put is cleared as a yes/no, and we'll default that to no. We'll check these off when the actual check is cleared from the bank.
Now, there are two ways to handle the amount. In my previous classes I used to just put amount in as one field. And for credits, like a deposit, I would put in a positive number. And for debits, like a check being drawn, I'd put in a negative number.
I did that in my original class when I covered this, which was back in Access Expert 29. And that's fine. There's nothing wrong with that technique. However, I've found that since most banks, on their websites and in your check register, your actual printed check register book and in ledgers, they usually show it as two columns - one column for debits and one for credits. People are used to entering them that way.
So I'm going to show you how to do it that way in this class. It's easier on your end user to think, "okay, put it in the debit column" instead of putting in a negative number. It's just a matter of data entry. As far as the computer cares internally, it doesn't really matter. We'll make a query field that will calculate it accordingly for a register.
So I'm going to put in debit in here as a currency and credit as a currency, and they'll both be entered as positive numbers.
Let's save this as my check register, checkregT we'll call it. Primary key defined, yes, that's our ID up top. Let's put in some sample data.
No check number, check date let's put in a one one, description opening balance. It's cleared, and we started it with a thousand dollars as a credit. So you have to make sure you put something in the right column now too.
Then I took a check out, check number 101 on one two, and that was for my gas bill. Hasn't cleared yet, and it was for a hundred dollars.
Check number 102 on one five, that was the electric bill, and that was for two fifty.
Check number 103 came out on one ten, let's say. That was for water, and sixty-five dollars. Actually, let's keep these numbers easy so they're easy to calculate in our brains.
Then I had another deposit. Let's say one fifteen, deposit. That was for two hundred dollars. Again, see, I'm typing it in the wrong column. Be careful. But I think that's still easier than telling people to remember to put these in as negative numbers. So this is fine, we're good.
Let's save that.
Now, what we're going to do for calculations in our forms and reports and stuff is we're going to make an amount field that's going to essentially be what the amount of the transaction was.
We're going to show in our calculation that credits will be positive and debits will be negative. That way we can easily just sum up one column and we do that in a query.
So, create query design. I'll bring in my checkregT, the one table that we got. I can close that. Bring in the star, that brings in all the fields.
Now for our first calculated query field. I'm going to put right in here Amount, that's going to be the total amount of the line. Each line item is going to be credit minus debit. What that will do is all the credits will be positive numbers, and then the debits will show up as negative numbers because the credit will be zero on that line minus whatever.
Let's save this as our checkregQ, and let's take a look at what we got. There we go. This shows the amount of each line item. Now we can calculate our balances properly.
If you've never done calculated query fields before like this, I've got other videos on how to do that - what calculated query fields are. I'll put links to them in the description below the video. Go look for those. If you don't know calculated query fields, go watch that video first and then come back and finish this one.
I'm also going to calculate over here the cleared amount because I know if each line is cleared. In order to calculate my actual balance versus the balance that the bank might be telling me, I need to know what the bank has cleared.
For that, I'm going to come over here and type in ClearedAmount. For that, we're going to use an if function - immediate if, iif - that says if this, then that. So, if IsCleared, comma, Amount, zero. Again, I've got videos on how to use the immediate if. I'll put links to that below. Essentially, it says if this is true, then set this value. Otherwise, set that value.
So if IsCleared, we want this column to be whatever that amount is. Otherwise, it's not cleared yet, make this column zero. That's the if function.
Let's save it and see that in action. Close it, reopen it. There we go. You can see the stuff that's cleared shows up over here in the cleared amount. That way, if you're looking at your actual bank statement on your website, the bank's website, you'll see what they know about. They might not know about these checks yet. They might not have been presented.
When you check one of these on, notice that it automatically changes. That's why we use the if function for that.
Let's close that.
Now let's make a continuous form so that we can view our check register in a form, because we don't want our end users playing with our queries. Plus, in our forms, we can add footers and have totals in those footers.
So, go to Create and then Form Design. I like to put a little splash of color in my backgrounds first. Let's do green. Let's do a light green. There we go.
We don't need to make that this big, so we can slide that up, bottom up a little bit, like so. Resize the form.
Set the record source for the form so we know where we're getting our data from. Let's go right here to checkregQ. We'll use that query. That way we can pull in those new calculated fields that we just made.
Also, while we're in here, set the default view to continuous forms. That's where I can see multiple records on it at a time, and it looks more like a spreadsheet. If you don't know what continuous forms are, I've also got videos on that as well.
A lot of my videos require knowledge from previous videos. So I will point you to those videos if you don't know what I'm talking about. That's why a lot of my classes are like level one, level two, level three, because level three requires the knowledge from levels one and two. But YouTube doesn't really work that way. So for the TechHelp videos, I'll tell you what other videos to watch first if you don't know what I'm doing.
Now, let's go to design and add existing fields. Let's bring all of these fields into our form. This is going to look like a spreadsheet.
I'm going to turn on my form header and footer. Right click on that detail band and go to form header and footer. Again, I have videos for form headers too.
Now, I'm going to chop off all of these labels, just the label portions over here and this guy. So I'm going to hold down the shift key and click on that one too. I just selected all the labels. I'm going to cut them out, control X, and paste them up top here in the form header. Click on the form header and paste them.
This is where the headers are going to go across the top. I got the ID, the check number, the check date, the description. Let's put IsCleared all the way at the end because it's a checkbox. It will look different.
Let's go debit, credit, amount, and amount cleared. I'm just going to make that say clear to save space. That's okay. I'll put IsCleared at the very end.
I'm going to select all of these and size them just a little bit so they're on the grid like that. Let's make them all black so they're easier to see. Format, foreground color, black. I like to use the standard colors. I don't like the theme colors. I don't want my colors changing. I like them the way that I set them.
Now let's just slide these guys up underneath the labels. Now ID is an autonumber. The user can't change that, and it's probably not going to ever get that big. So I'm going to highlight it and make it gray. That just visually tells the user, "you can't change that."
There's the check number - again, doesn't need to be super big. Check date will go here, and we really only want a short date for that field. So I'm going to double-click on it and set the format to short date like that. In fact, you might not even need to see four digit years.
Instead, I'm going to come up here and in the format just type in mm. Oh, wrong field, right there. mm/dd/yy. Or, of course, if you're over in Europe, you go dd/mm/yy. Whatever you like. I don't need a four digit year on that one.
Description can go here. That could be a little longer, but for now, we'll just leave it that size.
So debit goes there. Let's resize that a little bit. I don't know how big a check you ever write out, but it might not usually be that big. I'm going to slide that right about there, maybe. That seems to be about right.
I like to make all these number fields about the same width. I'm going to put them all underneath each other like this. Credit, amount, and cleared amount, make sure they're all sized the same like that, then slide them out.
Finally, the clear checkbox can go there. We could probably get rid of that because this says clear, so we kind of know what that means. Or leave it if you want. Whatever you want to do.
Everybody else looks good. Let's bring that bottom up like so.
Let's save it. I'm going to save this as my checkregF.
Let's see what we got. Close it, and then we open it again. Looks pretty good.
Couple of changes I'm going to make. All the dollar amounts are aligned to the right. That's okay. Let's make sure we line these up to the right as well, and I want all of this stuff over here lined up to the left. I just think it looks better with the text. And we got this annoying alternating background color. I really hate that. I can't stand that.
So, design view. Let's take these labels here and size them out correctly, like so. Then I'm going to select all of them. Format, align, right. Just like that.
These guys are all aligned right too. They should be by default, but we'll do it like that. Then I'll select all of these guys and align them to the left. Perfect.
While I'm thinking about it, this and this are also calculated fields, so let's make those gray. Let's take the calculated fields out of the tab order. So I'm going to click on this one, shift, that one, shift, that one. In Access use shift. I think control works as well. I always use shift though. Yeah, control works fine too.
Right click, properties. Then we're going to find tab order. Excuse me, it's the tab stop property. Tab stop and make that no. Then when we're tabbing, our tab doesn't stop on those.
Let's see how the end over here looks. This is good. We'll put a little checkbox right next to cleared.
Let's save that.
Oh, our alternating background color. Double click on the detail tab. Go to Format. Here's the background color I set. I'm going to make the alternating background color the same thing. That looks fine on reports, but I don't like it on forms.
Let's save it. Close it. Open it back up again. That's perfect.
Probably make checknum a little smaller and make the description field a little bit bigger, but I think we're good for now.
Let's do it real quick while I'm thinking about it. Do that. Slide this stuff over. We have a little bit of room on the right. We have plenty of space. I like to keep these forms so they fit in my video window, so I'll slide this stuff over like that. We can make the description bigger. There we go.
Save that. Close it. Perfect. That looks beautiful.
One thing I also noticed is that we want to move these labels over just a tiny bit because this column, one of the things about the currency format is that it leaves a little space over here for that negative number. So maybe just put a tiny, tiny bit of space in there so that these guys all line up right over the numbers. I like to do this. Just bring these in just a touch like that. Like one little pip. It looks better, or they're not so far off to the right.
Let's put some totals down here on the bottom. Total of my debits, total of my credits. Total of the amount would give me the actual amount of my account, and then the cleared amount would give me the amount the bank thinks I have. So we're going to put four form footer totals.
I'm just going to copy this debit one, copy and paste it down here. Slide it right underneath debit. If you want to leave a little room in there, like that, you can even put a line in here to make it look cool. Where's that line tool? Here's the line right there. Click and drag a little line underneath like that. Make that look cool like that.
Now open up the properties for debit. Go to All. If I leave it as debit by itself, I'm going to see the current record that we're on. Let me show you, so you understand what's going on here. Save changes, yes. Open it up.
Anything down here is still bound up here, so I'm bound to the debit field. If I click on electric, you see the current record down here. That's how, in some of my other videos, I show you how, like when we do contacts, you can click on a contact and see a big notes field in the footer.
So I don't want to see the current debit amount. I want to see a total of all of the debit amounts on the form.
Back at the design view, what we're going to do for that is use the sum function. So =Sum([Debit]). And I'm going to change the name of this to SumDebit.
See what I did? Sum of debit, and then SumDebit for the name of it.
Now, save that, close it, open it back up again. There's the sum of all the stuff in the debit field that's currently on the form.
Let's do the same thing for the other fields. This is one of the reasons why I wanted to put these in a query underneath the form. Because now I can sum these up, and the form will treat them just like their fields in the table.
So take this, copy, paste, paste, paste. Let's slide these up here, and we'll just adjust what fields these are based on.
This is credit, so this will change to credit, and then SumCredit.
Let's slide this over. This is amount, so SumAmount. And this is cleared amount, SumClearedAmount.
These two we can make gray. Actually, these are all gray, so let's make these all calculated like that. It just visually tells the user you can't change that. That's all.
Save it, close it, reopen it. Now you can see there's $400 debits, $1200 in credits. My actual balance is $800. That means I've got $100 left in my account after all of my checks are cleared, and the bank should think at this point I've got $1000.
Write another check on 1/16. This was for rent and $1200, let's say.
Now one thing to notice is that this total down here won't update until you move to a different record. So if I change this, let's say my rent was actually $900. If I tab, notice the footer hasn't updated. You have to leave and go to a new record by tabbing or clicking before this stuff gets updated. Same thing if you mark stuff cleared. If I click on this gas, for example, let's say this check was cleared, the total down here doesn't update until you leave that record.
Now, with a little bit of programming, you can force that to update itself as soon as you change this value. You can use what's called an After Update event.
Let me show you. Right click, design view. Let's do it to that checkbox here. Double click on it, go to Events, go to After Update, and then click on the dot dot dot button. This is an event that runs after you update that field. I have videos on this also, the After Update event. Go look in the description down below.
Click on that. You might get a little prompt that says what kind of builder do you want? Pick Code Builder. That will bring up IsCleared_AfterUpdate. This is what happens when you update that field. Literally all you need in here is one line of code:
Me.Refresh
That's it. That says redo all the calculations on this form after this field is updated.
Save it, close it, open it back up again. Now if I click on this, notice that the bottom instantly changes.
You can do the same thing for these two fields over here. Right click, design view. You have to do them one at a time, unfortunately. Pick debit, event, after update. Now you're in the After Update event for the debit.
Me.Refresh
You can do the same thing for credit too. You can pick it while you're in here. You don't have to keep going back to the form. Drop this down, find credit. Now you're in the credit BeforeUpdate. Pick After Update from this. This is the reason I don't like doing this, because it leaves code fragments in here. Get rid of that.
So now we have me.refreshes in all three of those fields, the calculations. Those are really the only ones you really care about.
Save changes. Open it back up, and now if I change this to 1200 and hit tab, the footer instantly recalculates.
That's nice to do, especially when you're dealing with calculations like this.
Let's say this bill was only 220. See? It automatically instantly refreshes it.
Let's say you get your bank statement in. You look at it, you compare it with this one, you can click off the stuff that's cleared like that. Now this stuff hasn't hit yet, so you better make it a deposit.
On 1/16, let's say that same day you were good, you made your deposit. Or let's say you got a customer check and you want to put in here "customer check," whatever. You don't have to put the word deposit in there if you don't want to. That could be anything you want it to be.
Let's say you put a credit in there of $2000. So now you're good. I wouldn't put that check number in here. This is your check number. Because in one of my classes I show you how, even though this is a text field, you can still write a loop that will count up and see if you have any missing checks. If you go from 104 to 106, 107, 108, and then you might be like, well, where did 105 go? You can convert this over to a number and then run a loop and see what's missing. That's a more advanced class.
Another thing you can do is when you do get your statement and this is going to get pretty big pretty fast, especially if you write a lot of checks, you can come over here and filter this based on just the unclear stuff. So when you get your statement in, you can right click on one of these fields over here and go is zero. That will show you just the unclear stuff, and the totals will update too. So you can see this is all unclear. I have $2200 worth of stuff that's not cleared. This makes the list shorter.
You can now go down this list, compare it with your bank statement and go check, check, check, check, and just check off the stuff that's on your bank statement, and then when you're all done, unfilter, and now you'll see everything again, including all the stuff that's filtered.
Want to learn more about building a check register in Microsoft Access? The extended cut for members covers running balances. We'll build a printable report with a running balance on it. Then we'll add that to your account form so you can see a running balance right on the check register form.
With this example, I didn't just stop there. The extended cut wasn't the end of it. I actually decided to keep going, and I built the whole check register seminar that's available on my website. It's over five hours long and it covers lots of additional stuff about building a check register database.
Lesson one covers what you learned in this video. Lesson two is the running balance information from the extended cut for members. Lesson three covers printing checks. Lesson four is about batch printing checks. In lesson five, we set up a list of payees and categories. In lesson six, we'll make the database handle multiple accounts. Lesson seven adds all kinds of extra formatting and other features. Then we cover reporting. In lesson eight, we build a pie chart to show expenses by category. In lesson nine, we build a grouped expense report and a transaction report.
Once again, that is my check register seminar. You will find a link to that in the description below the video. If you are interested in just the running balance information, that can be found in the extended cut for members only.
How do you become a member? Click on the join button below the video. Silver members and up will get access to all of my extended cut TechHelp videos, live video and chat sessions, and other perks. After you click the join button, you'll see a list of all the different membership levels that are available, each with its own special perks.
But don't worry, these TechHelp videos are going to keep coming. As long as you keep watching them, I'll keep making more, and they'll always be free.Quiz Q1. What is the primary benefit of building your own check register in Microsoft Access instead of using a program like Quicken or QuickBooks?
A. Access allows you to custom tailor the register to your needs
B. Access is more expensive and complex than Quicken or QuickBooks
C. Access automatically connects to your bank for updates
D. Access requires less data entry work than other software
Q2. Why is the Check Number field set as short text in the design of the check register table?
A. Because check numbers can include letters or codes
B. Because numbers should always be saved as text
C. Because Access does not support number fields for check numbers
D. Because text fields run faster in queries
Q3. When entering transactions, how are debits and credits handled in the check register table for easier data entry?
A. By entering both as positive numbers in separate columns
B. By entering debits as negative numbers and credits as positive numbers in a single column
C. By entering all values as negative numbers
D. By ignoring debits and only tracking credits
Q4. What does the 'IsCleared' field represent in the check register table?
A. Whether the transaction has cleared the bank
B. Whether the record has been printed
C. Whether the transaction was approved by a manager
D. Whether the transaction is a deposit
Q5. In the check register query design, how is the Amount field calculated?
A. By subtracting Debit from Credit (Amount: [Credit] - [Debit])
B. By adding Debit to Credit
C. By using only the Credit value
D. By multiplying Debit and Credit
Q6. What is the purpose of the ClearedAmount calculated field in the query?
A. To show the value of cleared transactions only
B. To show the value of uncleared transactions only
C. To display the current check number
D. To create a running balance for each transaction
Q7. Why are continuous forms preferred for the check register?
A. They allow multiple records to be displayed like a spreadsheet
B. They restrict users to viewing one record at a time
C. They prevent calculations from being displayed
D. They are the only form type in Access
Q8. How do you prevent users from stopping on calculated fields when tabbing through the form?
A. Set the Tab Stop property of calculated fields to No
B. Remove the fields from the form entirely
C. Lock the entire form
D. Change the background color of all fields
Q9. What is the main reason for coloring calculated fields gray in the form?
A. To visually indicate to the user that these fields cannot be edited
B. To highlight them for faster data entry
C. To make them easier to print
D. To align them with check numbers
Q10. Why might the totals in the form footer not update immediately after editing a debit, credit, or cleared field?
A. Because the record must be saved or changed before calculations refresh
B. Because Access has a bug in updating totals
C. Because queries cannot recalculate totals
D. Because only reports can show updated totals
Q11. Which event is used to refresh the calculations immediately when a debit, credit, or IsCleared value changes?
A. After Update event
B. Before Insert event
C. On Load event
D. On Click event
Q12. What code is added in the After Update event to refresh the form calculations?
A. Me.Refresh
B. Refresh.Form
C. Update.Form
D. RunQuery
Q13. When filtering the check register for uncleared transactions, what value is used to identify them?
A. Zero in the ClearedAmount field
B. One in the ID field
C. Description equals deposit
D. Debit greater than Credit
Q14. Which Access function allows for the calculation of totals such as SumDebit or SumCredit in the form footer?
A. =Sum([FieldName])
B. =Avg([FieldName])
C. =Count([FieldName])
D. =Max([FieldName])
Q15. What is a key topic covered in the extended cut or full seminar not covered in the main video?
A. Running balances and printable reports
B. How to link Access directly to your bank
C. Automating check numbering
D. Merging check registers from multiple banks
Answers: 1-A; 2-A; 3-A; 4-A; 5-A; 6-A; 7-A; 8-A; 9-A; 10-A; 11-A; 12-A; 13-A; 14-A; 15-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 video from Access Learning Zone focuses on how to set up a basic check register in Microsoft Access to help you track your checking accounts. I'll explain how to monitor which transactions have cleared the bank, which lets you see the difference between your available balance and what your bank displays. This is especially helpful for people who want to maintain an up-to-date understanding of their account's actual status.
The request for this video came from a user looking for tips on managing expenses within a simple Access check register. They specifically wanted a way to note when items have cleared the bank, so the actual balance in their account can be compared with the bank's records. Having a running balance was also on their wish list.
Creating a system like this in Access is straightforward. While programs like Quicken and QuickBooks offer this functionality out of the box, one benefit of using Access is the ability to customize both the data entry and reporting tools to your exact needs. That level of customization and control is a big reason many of us turn to Access in the first place.
Getting started involves building the check register table. Begin in table design view, and define an ID field as an autonumber. This should serve as the table's primary key.
Include a check number field as short text. I recommend using text here, since some check numbers might contain letters or codes. Next, add a check date field as date/time. I suggest not assigning a default value to this, which encourages users to enter the actual check date.
Add a description field set as short text for transaction details. Then add an 'IsCleared' field as a yes/no type, defaulting to no. This box gets checked once the item is cleared by your bank.
When it comes to handling the amounts, there are two typical approaches. Previously, I've created just a single amount field, using positive numbers for credits (like deposits) and negative numbers for debits (like checks written). However, most people are accustomed to seeing two separate columns, one for debits and another for credits, as it's shown in most bank registers and checkbooks. For usability, creating separate debit and credit fields (both as currency types and entered with positive values) makes data entry clearer for the user.
After you've set up these fields, save the table (for example, call it checkregT) and input some sample data. Enter a few mock transactions to represent an opening balance, bill payments by check, and deposits.
To properly calculate balances, create a query for your check register table. In this query, add a calculated field, which you'll call 'Amount'. This field subtracts the debit from the credit for each entry, so credits appear as positive and debits as negative amounts. This structure allows easy summing of the transaction amounts to determine your running total.
If you're new to calculated fields in queries, I have videos that go into their setup in more detail, and you can find links to them on my website.
Additionally, you'll want to calculate the 'ClearedAmount' for each transaction. This involves creating another calculated field in your query, where you use an immediate if statement (IIf) to show the transaction amount only if the item is marked as cleared; otherwise, the value will be zero. This approach lets you quickly distinguish between all transactions and those that have cleared according to your bank, giving you a way to compare your records to theirs.
Once you have your query with the necessary calculated fields, save it (perhaps as checkregQ). This will serve as the record source for the forms you'll build.
Next, design a continuous form bound to your query. Continuous forms allow you to display multiple transactions in a spreadsheet-like format. You can pull in all fields from the query, arrange them as needed, and organize the form for ease of use. It's helpful to move labels for each field into the form header, making your data grid clear and organized.
For auto-number fields, such as your transaction ID, consider graying out these fields to visually indicate to users that they aren't editable. Arrange text and number fields for optimal readability, adjusting widths and alignments as needed. For currency fields, you might need to tweak label placement so headers line up neatly over the numbers, since the currency format introduces some extra space for negative values.
In the form's design view, add a footer and set up total calculations for your debit, credit, amount, and cleared amount fields. Copy and paste controls as needed, and use the Sum function to display totals. Gray out calculated totals to indicate they're not directly editable. These totals allow you to quickly see your overall outflow, inflow, account balance, and bank-cleared balance.
An important detail to note is that, by default, these totals won't automatically update until you leave the current record. To improve this, you can add a simple bit of VBA code to the After Update event of key fields (like IsCleared, debit, and credit). Adding 'Me.Refresh' in these events makes the totals update instantly whenever a change is made.
As your register grows with more entries, you can use filtering to view only uncleared items, which makes it easier to reconcile your checkbook against your bank statement. Filtering by IsCleared shows just uncleared transactions and updates the displayed totals, so you can quickly see everything outstanding.
For those who want to expand on this example, the extended cut of the lesson covers how to add a running balance (the classic checkbook style) both in a printable report and directly on the form itself. I also offer a full seminar on building a check register in Access, which spans over five hours and includes lessons on printing checks, batch check printing, managing payees and categories, handling multiple accounts, additional formatting options, as well as creating pie charts and grouped expense reports.
If you're interested in the running balance feature, that's covered in the extended cut available to members. Membership gives you access to all extended TechHelp videos, live sessions, and extra perks. For details about joining, you can find information and a list of membership levels on my website.
Of course, all TechHelp videos like this one remain free to watch and will continue to be released regularly.
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 Creating a check register table in Access
Choosing field types for check register data
Setting up debit and credit columns
Entering sample data for checks and deposits
Creating a calculated amount field in a query
Using queries to calculate cleared amounts
Building a continuous form for data entry
Customizing form layout and formatting
Adding form headers and footers
Configuring tab order and tab stops
Right-aligning and left-aligning fields in forms
Setting up currency and date formats in forms
Adding footer totals using the Sum function
Refreshing form totals with After Update events
Filtering register entries by cleared status
Using conditional formatting for calculated fields
Copying and customizing controls for footer totals
Implementing Me.Refresh in VBA for instant updates |
