Account Balances 8
By Richard Rost
3 years ago
Track Account Balances & Transactions, Part 8
This is part 8 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 are going to build the transaction table and related query and form to track transactions as money comes in and goes out of our accounts. This will then update the balances on our account form.
Members
Members will learn how to create a table to store favorite transactions. There are things you do all the time like paying your credit card bills, loan payments, and mortgage, and these will all be in your saved transactions table. Then we'll make a button where we can add the positive and negative transactions to your transaction history with one click.
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
Links
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, transaction history, saved transactions
Intro In this video, we continue building our Microsoft Access account balances database by creating a transactions form, linking it to accounts, and setting up default values and formatting for transaction entries. I will show you how to copy and modify forms, set up combo boxes for account selection, handle record updates, and ensure data synchronizes correctly between forms. We will address updating queries, managing default values in tables, and using conditional formatting, as well as improve workflow for adding and editing transactions. This is part 8.Transcript Today is Part 8 of my Account Balances video series. If you have not yet watched Parts 1 through 7, go watch those first. You'll find links down below. I want you done with that. Then, come on back and we'll work on Part 8.
And we are back at it. A couple of minor things to do since our last video. A couple of things that people have brought up.
The first thing I want to do on my Add Account here is make the Save button have a little push thingy. You put a little ampersand in front of that. I don't want to make it the default button, though, because when you press Enter, it pushes that button. I don't want that. We got Cancel for this one here. You can do it at Close. You can do a little at Delete. Right now, when you push the Alt keys, it pushes that button. If we are adding an account or we are making a change, whatever, you hit Alt S and it pushes that button or Alt C and it closes that button.
Our Do Re-Calc function: if you go into the code here, do Re-Calc, this guy, make sure you add this. I'm not sure if I added it in the last video. That's going to be there. I don't know if I forgot those.
Here's a super nitpick scroll down. Actually, let me show you what it is first. It's one of my nitpicks that I didn't like. Notice how, since Pending is a text field and not an actual number field, there's a little bit of extra space around numbers. See? Right there. There is one there, because that's a text field. So let me fix that right here. Pending text equals and a space. It's a nitpick, I know. And now you have to go down and redo them all. I think you can just double click. It's kind of 300. And now you'll get that extra little space there. So you have to.
It's these little things like I started actually really using this over the past weekend. And the little tiny things that you see - oh, I'm going to change that.
Today, we're going to start working on transactions. This table. We'll be started. We built the table. We need to actually put this amount into here with a form.
Now, I've decided - as I said I was playing with this over the weekend - I'm going to get rid of sort order in here because if I really want to use that sort order, I can just grab the sort order off of this. But I think when it comes to transactions, I tend to sort by either the transaction name or the account. I want all the Regions Bank account stuff together. Or I might even want to sort by the date updated to see, like, show me the transactions in the order that they were put in the system.
So after playing with this a lot over the past week, I decided I don't really want the sort order in the transaction table. I'm never going to use it.
To do that, we need to go back to our table. I already did it in RxData. Go back to your sample data table. Turn it up. Go into your transaction T and we're just going to get rid of sort order. Goodbye.
Back in your database. One more thing. I want to also make it so that in the transaction table, the default date down here puts today's date in there. And I'm kind of throwing around in my head putting Now in there so I get the date and time.
Now normally for my accounts, I don't care so much about the time because I just want to know when's the last time I updated this account. But I'm thinking for transactions, it might be handy to put the time in there simply because as I'm adding them, if I'm doing like five transactions today, I want them to show up in the order that I entered them that day. And just Date by itself gives you the date at midnight, not the full time.
So I'm thinking, let's go back to that table again. I'm sorry, just stayed in here before. Let's go back into here and I'm going to set DateUpdated default value equals Now. Now, I'm still going to display it with just the date. In fact, I have to change mine too. Okay, back into here.
So now when you go to add a new transaction, you're going to get date and time in the field, which you can still sort by to get a proper order for the transactions and the order that you entered them in that day. I'm going to set that with something that I wanted as I was working on this stuff.
Next, to make my transaction form, I got my accounts form here. Next to it is going to be transactions. I know in this little window amount of space, but I'm going to put mine over here. I'm going to want to see the transaction name.
What have we got in here for fields? We got transaction name. I put the transaction name as the same for the plus and the minus. Remember, a transaction is going to have two items: a from account and a to account. So if you pay your Amex, it's going to have a from account, which is, let's say, your bank account you're paying it from, and your to account, which will be your Amex. $150 into Amex, $150 out from your bank account.
I'm going to want to retain those colors from the account IDs. Now, the colors are stored in the account table in my ColorScheme field. So I need to make a query that's got all the transaction data and the color scheme.
Let's go to Create Query Design, bring in my transaction table and my account table. We got our global relationships set up for that. Now bring in the star from transaction T and just the color scheme from over there.
Give it a quick run just to make sure you got the new records. If you don't have that new record on the bottom, then this becomes a non-updateable record set. I want to be able to edit this stuff in place. It's like we had the problem with the transaction table before. This one we're still good. If not, if you put stuff in here, calculations, weird relationships, that, that, that, you may have to resort to using a DLookup. Unfortunately, this one's nice and simple.
We can save this as our transaction query. If I run it, there we go.
Now we're going to make the transaction form. A lot of what's going to be in the transaction form, we can actually copy from our account form. I was kind of on the fence whether we should just copy this guy and make changes to it or build it from scratch. I'm going to copy this one. We've done a lot of the work already. I think it'd be easier just to manipulate this thing a little bit than to build it from scratch again.
Let's copy the account form. Copy, paste, Ctrl+C, Ctrl+V. This will be my transaction F. Now we have some work to do to modify this guy.
First thing, let's change this caption so we know what we're dealing with. There's the caption. This will be Transactions.
Its record source is going to now be that transaction query. As soon as you do that, notice you get all these little green guys in here. Those are all the fields it can't find now. That's okay. We're going to work it back in a second.
Now let's get rid of everything in the footer. We don't need all that stuff. Let's get rid of the sort order. We don't need him. The account name is going to be... Oh, come here. This is going to be the transaction name copy. Don't forget to put it in the name field. Slide you over to the left. All this stuff in the middle here, we don't need it. Select all of this and delete it.
We got the transaction name. We'll put this up here to transaction name for our label. Next, we need to know what account this transaction is from or to.
Let's add a combo box. Where is your combo box? Right there. Click get the value from a table or query. Where is our list of accounts? That's our account table. Next, any of the ID and the account name. Next, sort it by account name. Next, that's what it's going to look like when you drop the box down. Looks good. Next, store that value in account ID. Next, label. We're going to get rid of it anyways and then hit finish.
For the label, this is the account. Size it so it fits there. Over here, we're going to call it account combo. If you haven't watched my relational combo box video, go watch that. Put this label right above it. We'll put account in here.
I don't like to go too long without saving my work. So let's save our work. Let's close it and reopen it and see what happens. Open it up and print. Variable not defined. Well, this was that code that we used before. Let me stop this code. This was the code that we used before for setting the sort order. Remember this guy. So it goes yellow no matter where we are. I like that in this form. We don't really need it in this one though. So we're going to get rid of that code. We don't need it. In fact, we can delete all of the code from this form. We don't need any of it. We're going to add some later, but we don't need any right now. All that stuff is unique to the account form.
Save it, close it, close it. Let's open it back up again. Looks good so far.
I also want to be able to easily make changes on this form, unlike the account form. Over here, I didn't want to be able to add accounts here or delete accounts here. So that's going to be different on this one because this is my transactions. I want to be able to add and delete them freely. Make edits.
So, Design View. Let's go into the form's properties. Go to Data. We're going to turn Allow Additions on and Allow Deletions on. That's fine for this form.
Save it. Let's add the Amount field. Add Existing Fields. We've got Amount right here. Drag it right there. Get rid of this. Slide it up under here. I think we need all these. But this over this guy. Let's right-align them both. Let's bring our date over. There we go. We can now make this smaller. Shrink that up. Set our tab order. Auto. See what it looks like. Save it. Close it. Save it. Open it.
Looks good. Let's make it so that this, this, and this are tab stops, but the date isn't. I almost never have to change the date manually in here.
So, Design View. I'm going to select them all, Properties, let's make Tab Stop Yes. Then just click this one and make Tab Stop No. I think I like that better. The date is going to pop in there automatically whenever I add something.
Now let's get that conditional formatting on this combo box. Design View, pick this, Conditional Formatting. If you don't have it on your Quick Access Toolbar like I do, go to Home. It's right there. I cover this in one of my early days. Open it up. There we go. So this and this will have the format for whatever account you're in.
This is one of those situations where sometimes you want to sort by the transaction name so these are together. That's why that sort order on this form doesn't always make sense. Over here, I want to keep stuff grouped together. I want all my Regions stuff grouped together, all my PayPal stuff grouped together. It might not share the same name, but I like the custom sort. I want my PayPal up top, Amex is a little bit below it. So that's why I got the custom sort order over here.
Credit, I want that red format from the other guy. From this. Now unfortunately, remember we cannot copy conditional formatting across forms unless they've changed it. They sneak changes like that in there all the time. Let me see if we can do it now. We couldn't before. So they pick like pending the conditional formatting, pick an amount. I don't think it changed. Let's see. No, still can't do it. Still can't do it. So what you have to do is copy this guy, paste it over here, maybe in the footer. Now that it's over in this form, you can format paint the conditional formatting over it now. We have to right-align again. You can delete this one now. Then there is the format. You can't do conditional formatting across forms. You have to copy that control over here and then do the conditional formatting. Note to Access team: that would be a nice little thing to add. I got a list. I got a giant list.
Now for me, at least, these are designed to be open side by side. So this form and this form, I'm going to have them open on my system, side by side. I like to work with them. I've been working with a spreadsheet side by side like I did in the Excel version. But here I like to see if I make changes over here, I want it to update over here back and forth.
Now let's add something over here. How about Payment to Capital One? We got another error message coming up. What's going on here? The After Update event you entered as a property setting. So we got something in the After Update event still that we have to fix. So hit Escape. There'll be a couple little things we have to change. Let's see what that is. In Events, oh, our DoRecalc. Remember, that DoRecalc doesn't exist in this form. So we got a couple more things to clear out of here. Get rid of that. Get rid of that. Let's double check these other fields real quick. No. No. No. Now we should be golden.
One more time, open it up. I'm going to move it over here and save it right there. That should stay in its spot.
So let's try making that payment again. How about Payment to Capital One? Payment to Capital One, and we'll drop this down. Oh, I don't have Capital One in this list. There are events you can put in here if something's not in this list to add it automatically. Right now we're still in the simple stages. I'm just going to hit Escape a couple times, come over here and we're going to add an account. We're going to add Capital One, credit card. What type is that? Credit card account number, whatever. I've been cheating in my database. I've been storing the expiration date and my CVV code in the routing number. Now, this is just for me. It's a personal database on my personal machine. I don't recommend you do this. But all the time I'm always online and it's like I have to go find my credit card, so I've been keeping it in my database. But you didn't hear that from me. Don't do that. You shouldn't do that. Let's make it pink. Let's save it.
Now we have Capital One. Let's see if it updated over here. It didn't update over here. That's a problem too. So what you could do is you could put an event over here if you want to that, well, you could add it to this save button because that's the only way you can get out of here. It's the only way you can change or delete an account or add an account. This is going to run a lot less frequently than this is. So I'm going to say, what is this, my transaction F? I'm going to say Design View, in my Save button, I'm going to say down here, we're going to say Forms!TransactionF!AccountCombo.Requery. I named it AccountCombo. I'm pretty sure I need to. Let's go check. Well, it's going to be .Requery either way. So let's go peek it. What do we call it? I do AccountCombo. Yes. Okay, just to make sure.
Now, problem also is going to exist where if this form is not open, this line will throw an error. I do have code in my Code Vault that helps you check to see if a form is open. But in this particular case, we can get around this with a simple On Error Resume Next. For that one line, I don't mind. And even for the Close line after this, it's okay here.
Anyone that says you can't use On Error Resume Next, don't listen to those people. They're not trustworthy. No, no, there are better ways to handle it. Checking to see if this form is open is definitely a better way. But in this particular case, it's not going to hurt anything. Trust me. There is what's best for the database from an academic standpoint, from a theory standpoint, and then there is what works fine for your database. In this case, I've done this many times. It works fine here.
Just make sure if you have more stuff after that line, you do an On Error GoTo 0, which turns that error handling off. Because you don't want a line that's 15 down from that spot that causes an error and you don't realize it. So be very careful with your On Error Resume Next. I got a whole video on that.
Save that. Let's close it. What we just did was, if we add an account over here, it's going to show up in our transaction F.
One more thing I want to do real quick is I like to have these side by side. So I'm going to have it so that whenever this form opens, it opens up my transaction form too. Design View, I'm going to go into here. I'm going to go to this guy's On Load event, and just open up the other form: DoCmd.OpenForm "TransactionF". That's it. Just open them side by side. You can save it in the right spot for your screen so that they'll always show up right next to each other.
I also have code in Code Vault that opens up a form to the right of another form. It's called OpenFormRight or below it. You can match the size. You can have it follow it around. So if this guy moves, this guy will move too. All kinds of cool stuff in the Code Vault for the Gold members, of course.
I have a now Capital One show up in there. There it is. Let's add another account just to make sure our code works. Add account. Let's add Capital One savings account. That'll be a savings account. We'll also make that pink. Save it. Now that should also show up in here. There it is. So now our code's working. Whenever this form closes, it refreshes this box.
I see my Pending isn't defaulting when I create a new record. So let's make sure we do that when we close it. We have to open an account in here. Right click Design View, save, build event. No, I think this is a matter of the table. In the account, let's see here. We got Pending, Pending Text. Yeah, we should make Pending Text zero with a space after it. So again, let's go back to our table, sample data. I got my database open over on the side. Sample data. AccountT, right-click Design View. Pending Text needs a default value of zero space. Let's see if it keeps that space. No, it doesn't. See? It doesn't keep the space. That's one of my pet peeves with Access - it doesn't let you keep leading or trailing spaces.
In that case, let's put it inside quotes like that. Save it. Take a peek. Yes, there we go. It saved it with that. So, if you're putting it in Default Value and you want to have that space in there, put it inside quotes. If you actually want quotes in that default value for text, make them double double quotes.
Let me go pop this up in my database real quick. All right, so accounts. If I add a new one, let's do a PayPal Credit. The credit account, blue. Save it. There we go. We got our Pending is looking good. Now I can take you and move you up. Up, up, up, PayPal people, perfect.
Again, the Date I don't like pre-populating because I want to know that I haven't checked that account yet.
Now, when we add or delete records from the transaction list, this guy should update automatically. So if I come in here and I add just a test transaction, if I put in here, let's put in Capital One Credit, which right now, there's no transactions in here at all. I'll pick Capital One Credit. I'll put $1,000 in. Look at that, it updates. Because that's a new record. Likewise, if I delete it, it also updates, because that's a new record. Well, a record's changing.
But if I come in here to edit an existing record, how about this Amex Platinum one? Right there, you can see the 150. If I come over here and I change that to 100, it's not updating. I just noticed that's not the exact thing that I want to look like. I will fix that in a minute.
How do we update this when it's not updating? We need a couple of little After Update events. We need the AccountCombo and this Amount over here, which should not be Credit, it should be Amount. Let's fix that.
We have to put After Update events in this Amount and in this guy here. Event, After Update. Come in here. We're going to make our own DoRecalc in this form, which will be different from the other one.
Private Sub DoRecalc. Why am I making a sub out of it? Because I'm going to call it from two different places. In here, I want a Me.Recordset.Requery. That's going to require the current form. Make sure the records are saved at the table. You can do a Me.Refresh too, but I think this works a little better. Then we're going to refresh the other form: Forms!AccountF.Recordset.Requery. We'll need On Error Resume Next, so we don't need to bother to check to see if the other form is open or not.
We're going to call that DoRecalc from the AccountCombo After Update. And we're going to call it from the Amount After Update.
Yeah, you know what we could probably do? DoRecalc the old-fashioned way too. Let's make it a function. Private Function like that. We'll do our DoRecalc like we did with the other form. So here After Update equals DoRecalc. And we'll do it for you too.
Save it, close it, close it, open it. See, when you save changes, it doesn't stick. That's why I like my OpenFormRight code, because it always forces it to go right there instead of having to manually save everything.
Let's change that Amex Platinum, which is right there, that $100. Let's change it to $200 and save. It updates. Look at that, isn't that a beautiful thing?
All right, let's address that format issue. The format issue is because we actually are using something in the Format property. This guy. Copy that. Then in the transaction form, it's got just Currency there. We want to specify our format. Save it, close it, close it, open it, pick up again. Now we got the format. We got to put it there. I'm definitely putting the OpenFormRight in the extended cut because that's annoying.
There you go, there's your transactions form. If you want to add another transaction, you just come down here. What's the transaction? Let's say you're putting money in your IRA. So you got another transfer to IRA here. Transfer to IRA. The From account is going to be, again, your Regions Checking.
That's interesting. Maybe we'll just go with - I wasn't thinking it did ReQuery. Let's just use a Refresh there instead. Let's go back here. Let's do a Me.Refresh. Let's see if that's any better. I don't want that moving around on it. Let's try that again. Get rid of this delete.
One more try here. See, I'm still discovering stuff as I go along too. I've been working with this on my own the past couple of days, but I'm still coming up with stuff. Transfer to IRA. This would be my Regions Checking. That's good. That's exactly what I wanted. Here, we'll put $1,200. Regions Checking, $1,200. Looks good.
Except, I forgot to put a negative $1,200. There we go, that's correct. Remember, your From account is negative. So now I got the transfer to IRA. Where is it going? Fidelity. That's going to be my plus $1,200.
Now when this stuff clears: let's say this guy gets reported as paid or you see it as posted. You'd come over here. This, obviously, is going to be $500 less now. So let's say this gets updated to a thousand. This guy you can take off in here because it's posted.
Now, just looking at this, you've got to deposit some money in there pretty soon, because you got money coming out. There's your actual. That's why I like this actual column. If you don't make a deposit pretty soon, you can have a problem.
Some transactions are immediate. Like if I transfer from savings to checking or for one of my other accounts, if it's an internal transfer on the bank's website, it's immediate. So I can go on there and I can say, all right, I'm going to take $800 out of this account. So this is now a thousand, and now this is going to be $1,800. Now everything should be kosher. Well, except I have transaction - let's get rid of this. Let's make it so my accounts aren't so bad. I was just pretty much typing numbers in willy-nilly. There we go. Let's say this guy's got $500 in it. My savings account's got $2,000. Now my numbers are good.
You have to make sure this column isn't red. And yes, I know I'm going to get emails from all the actual accountants out there that this is not the perfect way to do it. I get it. This is just for me. I've been doing it this way in a spreadsheet for 10 years and it works great for me. So that's why I'm sharing it with you guys. I've been wanting to move it into Access instead of keeping it in Excel for the longest time.
A lot of transactions you'll put two entries over here. Some transactions you only put in one. Like I know with PayPal Credit, for example, when you pay PayPal Credit, it'll come out of your PayPal account almost immediately. So if you pay like $200, then right away your balance will change. That'll become 800. So you just change it right there. And then over here, you'd put in Payment to PayPal Credit and PayPal Credit. You'd put in here that you're expecting $200. You want it to end up looking like that.
Now, I know there are a lot of transactions that you do on a regular basis - same ones over and over again. You pay your Amex bill, you pay your Capital One bill, you make a deposit in your IRA, you pay your mortgage.
In the extended cut, we're going to do saved transactions. You do transactions that you do all the time. Here's your accounts. Here's your transactions that are currently waiting to go through. It's time to pay some bills. So you open up Saved Transactions. Here's your Amex payment. You click Process. It asks you for the amount because you don't always know the amount until you check your account. Okay, I owed $240. So put 240 in there, hit enter. Boom. It puts the 240 in here. Here's the From account. Here's the To account. Minus 240 from your checking account, plus 240 to Amex. Isn't that cool?
Same thing, got to pay Capital One. Click. How much do you owe? $85. Boom, put it on there. Mortgage payment too. Click. It knows it's $2,900, that's a default amount you specified. Enter. Boom. Put it right down there. Isn't that cute?
That is going to be covered in the extended cut for the members. It's a long one today too. It's like 35, 40 minutes. Silver members and up get access to watch all the extended cut videos. Gold members can download these databases and use my Code Vault. All members get some free training every month. They get a free class. So check it out.
But that is going to be your TechHelp video for today, Part 8. If you like this series, I need to hear from you. Post some comments down below if you guys want to keep me going. If you want to see Part 9, Part 10, Part 11, I'll keep building it as long as you guys are interested. Honestly, over time, these things kind of die off a little bit. More people will watch Part 1 and then fewer watch Part 2, then fewer watch Part 3. People drop off. So I need to know how many of you guys are watching it. As the views get lower, speak up, say something. Otherwise, I'm going to finish this in a template or in another video somewhere. So if you want to keep seeing me post these free videos here on my YouTube channel, I have to hear from you.
But that's going to be your TechHelp video for today. I hope you learned something. Live long and prosper, my friends. I'll see you next time.
If you enjoyed this video, please give me a thumbs up and post any comments you may have below. I do try to read and answer all of them as soon as I can. Make sure you subscribe to my channel, which is completely free. Click the bell icon and select All to receive notifications when new videos are posted.
Want to learn more? Click the Show More link below the video to find additional resources and links. YouTube does a pretty good job of hiding it. It's right down there. See this part of the description here. The name, the video is up here. There's a little Show More down there, right down at the bottom. It's kind of hard to find. But once you click on that, you'll see a list of other videos, additional information related to the current topic, free lessons, and lots more.
YouTube no longer sends out email notifications when new videos are posted like they used to do. But if you'd like to get an email every time I post a video, click on the link to join my mailing list. You can pick how frequently to get emails from me, either as they happen, daily, weekly, or monthly.
If you'd like to become a paid member of my channel and receive all kinds of awesome perks, click on the Join button. You'll see a list of all the different membership levels that are available, each with its own special perks, including my extended cut videos, access to my Code Vault (lots of VBA source code in there), template downloads, and lots more. I'll talk more about these perks at the end of the video.
Even if you don't want to commit to becoming a paid member but you'd like to help support my work, please feel free to click on the tip jar link. Your patronage is greatly appreciated and will help keep these free videos coming. I have some puppies to feed. But don't worry, no matter what, these free 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.
If you really want to learn Access and you haven't tried my free Access Level 1 course, check it out now. It covers all the basics of Microsoft Access, including building forms, queries, reports, and more. It's over four hours long. You can find it on my website or on my YouTube channel. I'll put a link down below you can click on. And did I mention it's completely free? The whole thing -- free, four hours. Go watch it.
A lot of you have told me that you don't have time to sit through a four-hour course. So I now have a quicker Microsoft Access for beginners video that covers all the basics faster, in about 30 minutes. And no, I didn't just put the video on fast forward. I'll put a link to this down below as well.
If you like Level 1, Level 2 is just a dollar. That's it, one dollar. And that's another whole, like, 90-minute course. Level 2 is also free for paid members of any level, including supporters. So if you're a member, go watch Level 2. It's free.
Want to get your question answered in a video just like this one? Visit my TechHelp page and send me your question there. Members get priority, of course. While I do try to read and respond to all of the comments posted below in the comments section, I only have time to go through them briefly a couple of times a month and sometimes I get thousands of them. So send me your question here on the TechHelp page and you'll have a better chance of getting it answered.
While you're on my website, be sure to stop by my Access Forum. We have lots of lively conversations about Microsoft Access and other topics. I have a fantastic group of moderators who help me answer questions. Shout out to Alex, Kevin, Scott, Adam, John, Dan, Juan, and everybody else who helps on the site. I appreciate everything you do. I couldn't do it without you.
Be sure to follow my blog, find me on Twitter, and of course on YouTube. Yes, I'm on Facebook too, but I don't like Facebook. Don't get me started.
Now, let's talk more about those member perks. If you do decide to join as a paid member, there are different levels: Silver, Gold, Platinum, and Diamond.
Silver members and up get access to all of my extended cut TechHelp videos, one free beginner class every month, and some other perks.
Gold members get all the previous perks plus access to download the sample databases that I build in my TechHelp videos, plus access to my Code Vault where I keep tons of different functions that I use, the code that I build in most of the videos. You'll also get higher priority if you submit any TechHelp questions. Now, answers are never guaranteed, but you do go higher in the list to meet the algorithm, and if I like your question, you have a good chance of it being answered. You'll also get one free expert-level class each month after you've finished the beginner series.
Platinum members get all the previous perks plus even higher priority for TechHelp questions. You get access to all of my full beginner-level courses for every subject, and I cover lots of different subjects like Word, Excel, VBA, ASP, lots of different stuff, not just Access. These are the full-length courses found on my website. You get all the beginner ones. In addition, once you finish the expert classes, you get one free developer class per month. So, lots of training.
Finally, you can also become a Diamond sponsor. You'll have your name or your company name listed on a sponsors page that will be shown on each video as long as you're a sponsor. You'll get a shout out in the video and a link to your website or product in the text below the video and on my website.
That's it. Once again, my name is Richard Rost. Thank you for watching this video brought to you by AccessLearningZone.com. I hope you enjoyed. I hope you learned something today. Live long and prosper, my friends. I'll see you again soon.Quiz Q1. Why did the instructor decide to remove the sort order field from the transaction table?
A. Because transactions are usually sorted by transaction name, account, or date updated
B. Because sorting in Access is not possible without a sort order field
C. Because the sort order field caused errors in record updates
D. Because the sort order was required only for the sample data table
Q2. What is the main reason for setting the default value of the DateUpdated field in the transaction table to "Now" instead of just "Date"?
A. To record both the current date and time for accurate sorting of daily transactions
B. To prevent users from changing the date manually
C. To ensure all transactions are pre-dated at midnight
D. To auto-calculate the next transaction date
Q3. When copying the account form to create the transaction form, what was the recommended approach?
A. Copy the account form and modify it to suit the transaction needs
B. Build the transaction form from scratch only
C. Use a template form from Access
D. Use only datasheet forms for transactions
Q4. Why was it necessary to create a query joining the transaction table and the account table?
A. To display the color scheme for each transaction based on account data
B. To merge all tables into a single flat file for reporting
C. To calculate running balances automatically
D. To allow non-updateable recordsets in the form
Q5. What is the main function of setting Allow Additions and Allow Deletions to Yes in the transaction form's properties?
A. To let users freely add and delete transaction records
B. To prevent users from modifying transactions
C. To restrict changes to the account form only
D. To make the form read-only
Q6. Why should you be careful when using "On Error Resume Next" in VBA code?
A. Because it can hide errors that occur after the intended line
B. Because it always stops code execution on errors
C. Because it prevents any errors from ever occurring
D. Because it removes all error messages from the form
Q7. Which method is used to update a combo box so newly added accounts show up in the transaction form?
A. Use the Requery method on the combo box
B. Use the Refresh method on the form
C. Close and reopen the entire database
D. Compact and repair the database
Q8. What is the suggested way to copy conditional formatting from one form to another in Access?
A. Copy the control with formatting, paste it in the new form, and use Format Painter
B. Use the Save As option for conditional formatting
C. Export the format settings as XML
D. It is not possible to copy conditional formatting in any way
Q9. What is the purpose of creating an After Update event for the Amount and AccountCombo controls in the transaction form?
A. To recalculate values and refresh related data when these fields are changed
B. To prevent modifications to these fields
C. To validate that only numeric data is entered
D. To automatically delete records when updated
Q10. In the context of bank transactions, why does a transfer between accounts require both a negative and positive entry?
A. To accurately reflect money moving out of one account and into another
B. To keep all account balances positive at all times
C. To increase the account balance in both accounts
D. To automatically pay off credit card bills
Q11. Why might the instructor want to have both the account and transaction forms open side by side?
A. To enable real-time updates and visibility between accounts and transactions
B. To save screen space on smaller monitors
C. To prevent users from making mistakes
D. To ensure that only one form is edited at a time
Q12. How does the instructor handle saving a Pending Text field with a value of zero and a space in Access?
A. By entering the value in double quotes in the default value property
B. By typing zero and a space directly in the default value property
C. By leaving the field blank
D. By setting the format to currency
Q13. What problem does the instructor solve by adding code to the Save button after adding a new account?
A. Ensuring the new account shows up immediately in the list of available accounts for transactions
B. Automatically closing both forms
C. Preventing duplicate accounts from being created
D. Sorting the accounts alphabetically
Q14. When editing an existing transaction's amount, what was missing that required an After Update event?
A. The transaction list and account balances were not updating automatically
B. The account name was changing unexpectedly
C. The date was not being saved
D. The form was closing unexpectedly
Q15. What is the instructor's recommendation for remembering or referencing frequently used transactions, such as monthly bills?
A. Use saved transactions for quick entry and processing
B. Manually type them each time
C. Store them only in the account table
D. Ignore recurring transactions
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 TechHelp tutorial from Access Learning Zone is Part 8 of my Account Balances video series. If you have not yet reviewed Parts 1 through 7, I recommend going through those first, as each part builds on the concepts and development covered earlier.
In this installment, I am going to address a few minor details based on feedback and my own observations from using the database more intensely. The first tweak involves the Add Account form's Save button. I want to assign a keyboard accelerator to this button using an ampersand but deliberately avoid making it the default button to prevent the Enter key from triggering it unintentionally. I also use accelerators for the Cancel and Close buttons, making keyboard navigation more efficient. Now, if you press Alt+S, you activate the Save button, or Alt+C for canceling, streamlining the process.
For the DoRecalc function, make sure that you have the appropriate code in place. If you missed any updates I discussed previously, double-check to ensure everything is current. It's always good to verify your work as you progress.
One subtle issue I discovered concerns the Pending field in the accounts table. Since this is a text field instead of a number field, you get an extra bit of space around values when displaying them. To keep displays tidy, adjust the output so that there is no unintended spacing. Sometimes it's the small aesthetic details you pick up on after actively using your own tools that lead to meaningful improvements.
This session pivots toward handling transactions. We've already created the basic transactions table, so the next step is to design the corresponding transaction form. After practical experimentation, I decided to remove the 'sort order' field from the transaction table. Sorting can always be managed by transaction name, account, or date updated, and I found custom sort order for transactions to be unnecessary. To remove it, simply delete this field from the transaction table in your sample data.
Additionally, in the transactions table, I want the default date for each new record to populate with the current date and time rather than just the date. Using the Now function as the default ensures each transaction records both date and time, allowing entries to remain properly sequenced throughout the day. Even though only the date is displayed on forms, you still capture the time component for more accurate record keeping.
Now, concerning the transaction form's design, I decided it would be more efficient to copy the existing accounts form, as this saves time over building from scratch. After duplicating and renaming it to TransactionF, I change its caption to 'Transactions' and set its record source to the transactions query. This causes some existing controls (especially those referencing nonexistent fields) to become invalid, but they can be removed or updated accordingly.
At this stage, remove unneeded controls like sort order, and adjust remaining field names and labels. Make sure to add a combo box for selecting accounts, pulling values from your accounts table. Set up the combo box to store the selected account's ID and arrange the form so that its fields are logically grouped and accessible. I always recommend saving your work frequently as you refine controls and layouts.
On the transactions form, unlike the account form, I want users to be able to freely add, edit, and delete entries, so Allow Additions and Allow Deletions should be enabled in the form's properties. Add the Amount field and arrange all fields neatly, ensuring the tab order is logical—typically, date fields don't need to be tab stops, and you can turn that off for ease of use.
Conditional formatting is also helpful for quick identification of certain transaction types or values. Unfortunately, Access still does not allow you to copy conditional formatting across forms directly. To bypass this, you can copy the control itself between forms and use format painter for styling, or manually recreate the formatting rules.
Given my preferred workflow, I often have both the account and transactions forms open side by side so updates in one reflect immediately in the other. This is especially practical when you're transferring money or making adjustments. To automate this, add code to the On Load event of the accounts form to also open the transaction form. There are advanced routines in my Code Vault for more sophisticated positioning, but a simple open command works well for most users.
When updating account or transaction data, you may run into problems with stale lists, especially after adding a new account that does not appear in the accounts combo box. To fix this, add code to requery the transactions form's account combo box whenever a new account is saved. Robust error handling is essential here; it's fine to use a line like On Error Resume Next for this specific action, as long as you reset error handling immediately after, to avoid masking genuine errors later.
As part of tidying the interface, revisit the Pending Text default value. Access does not retain leading or trailing spaces if you enter them plainly, so enter the value in quotes to maintain the desired appearance. This way, new accounts will properly display the Pending state as you intend.
To keep account balances up to date, ensure that AfterUpdate events are set up on the relevant controls in both forms. For any changes to the amount or selected account in a transaction, the calculations and displays need to be refreshed in both the transactions form and the accounts form. Place common update code in a dedicated DoRecalc function, called from both AfterUpdate events as needed.
Pay close attention to formatting issues for currency and other values. Make sure uniform formatting is applied across all relevant fields and forms for clarity and consistency.
To summarize, creating transactions in this system typically involves two entries: one for the source account (a negative value) and one for the destination account (a positive value). Some transactions, particularly those processed immediately such as internal transfers, only require a single entry because the effect on balances is instant. The setup I present mirrors my own spreadsheet-based accounting system, which I have relied on for many years before bringing into Access.
For repetitive transactions, such as monthly bill payments or scheduled transfers, I have plans for a Saved Transactions feature. In the extended cut of today's video, I demonstrate how to use saved transactions: select a saved record, enter the amount, and the relevant plus and minus entries are automatically posted to the transaction log, significantly speeding up routine data entry.
If you enjoy this series, please let me know in the comments. Your feedback helps determine whether I continue producing additional parts in this series. Viewer engagement is a key factor in deciding how I prioritize future content.
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 keyboard shortcut keys to buttons on a form
Setting default values for fields in tables
Removing the sort order field from the transaction table
Setting the DateUpdated field to use Now for date and time
Building a transactions query to include account color
Copying an existing form to create a transactions form
Changing form captions and updating field labels
Setting record source for a form to a new query
Customizing form controls and removing unneeded fields
Adding a combo box for selecting account in transactions
Setting up the combo box to pull account names from a table
Enabling Allow Additions and Allow Deletions on a form
Adding Amount and Date fields to the transactions form
Setting and adjusting the tab order for form fields
Applying conditional formatting to controls using account colors
Handling issues with copying conditional formatting between forms
Adding event code to requery combo box after adding new accounts
Using On Error Resume Next to handle form references
Opening transaction and account forms side by side on load
Refreshing combo box data after accounts are added or updated
Setting default values in text fields to include a space
Configuring events to recalculate values after form changes
Using After Update events for recalculation of balances
Writing code to refresh related recordsets between forms
Applying currency formatting for Amount fields
Handling dual entry transactions with from and to accounts
Demonstrating updates and deletions in transaction records
Explaining how negative and positive amounts represent transfers
Describing workflow for entering typical banking transactions
Updating displayed balances based on transaction updates |
