Today I'm going to show you how to immediately refresh any calculations on your form anytime any single field is updated. Today's question comes from Brady in Kirkland, Washington, one of my Silver members. Brady says, "I'm using your invoicing database for my business. It's been very helpful. Thank you." You're welcome.

However, whenever someone changes a quantity or a unit price, the total on the bottom doesn't update until I leave the record. This can be confusing because if someone types in a hundred for the quantity and they don't think to leave the record, they look down below and see a price that isn't right and they might quote that on the phone with the customer. How can I make it so that any time the field is changed, the total updates?

He's talking about it right here, you can see it, right? I did a little sample of my invoicing. I put a hundred at three fifty, the extended price for that line is thirty five thousand, but since the record is still dirty, I haven't left that record yet, the total down here in the form footer has not yet updated. So that's what I'm going to show you how to do today.

Now this will be a developer-level lesson, that means if you haven't done any VBA programming before, go watch my intro to VBA video. It's about 20 minutes long. It's free. It teaches you everything you need to know to get started. Today we really only need one line of code, but it's knowing where to put that line of code that is important. Actually, we need the same line of code in two different places.

It's like the Tesla and Ford story, where Tesla bills Ford $10,000 for fixing one of his machines, and Ford demands it to be itemized, and he says, "Alright, one dollar for marking the wall with a piece of chalk and $9,999 for knowing where to put that mark." Right, so we got to know where to put this line of code, and that's the important thing for today. I'll put a link to this story down below, by the way.

And while you're on my website, if you want to learn how I built this invoicing database, here's a free video to watch that, and also we're going to use the after-update event which is how we're going to fire this event. Watch this video too; these are all free videos. They're on my YouTube channel, they're on my website too. Watch those and come on back. Okie dokie, here I am in my TechHelp free template. This is a free database you can get on the website if you want to.

And in this database, we have customers, and customers can have orders. Right now notice this customer's order is currently at $4,200. But I'm on the phone, and I'm talking, and he wants to sell. Let's make 2,000 of these shield generators, okay? 2,000, and I hit tab or enter, and the extended price updates to 700,000, but notice down on the bottom, it still says 4,200. That's because this calculation does not refresh until you leave the record. At that point, this record no longer becomes dirty; it gets saved to the table, and any calculations on that form then update.

So what we have to do is, we have to say, "Okay, anytime I update any of these fields on which this is dependent, we have to force the system to refresh that record," which means save it and rerun the calculations. How do we do that? We do that with an after-update event on each of these fields. That fires when you leave the field when you've updated it and left the field.

So let's go to design view, let's slide this over here so we can see it. Let's pick one of these guys. Now watch, the first time I click on this, it clicks on the subform object because remember, this is a subform inside this form. So the first time you click here, find the event tab, go to after update, hit the dot, dot, dot button. That will bring up your code builder.

Now we're in the quantity after-update event. It's real simple, me.refresh. "Me" is the current form that you're working on. Alright, refresh just says save the record and recalculate any calculations that need to be. Alright, which is different from re-query. I got a whole separate video on refresh versus re-query, I'll put a link to that down below. Re-query actually re-queries the entire form, all of the records, but refresh is just the current record. Alright, save it.

I'll come back out here, let's close this and go back into it. Alright, now notice 700,000 right, quantity is 2,000, let's make it 5, hit tab and look at that. It saved the record, updated the calculation. But that's not the only place we need it because the unit price can also change, right? So we need to put it in there as well. So, go back to design view.

Alright, click that, gets a subform, click for the unit price after update, dot, dot, dot, and again, me.refresh. Okay, and yeah, you got to put it in a couple of different places. Basically, any field in here that this guy's dependent on. Do you need to put it in product name? No, not really. I mean, you can if you want to, but you don't have to.

And if you recall, when I created the invoicing video, I showed you that it was important to refresh the whole thing before printing the invoice. Go into the build event here, you'll see we've got a me.refresh right there because you need to refresh all of the information on this invoice on this order before you open up the invoice report. I talked about that in the invoicing video.

Okay? So that's pretty much all you need to do. And now we can close it, save it, open it, and we've lost our position. We'll move it over here and hit save. Alright, we'll change this to one at 50 bucks. And there we go. Perfect.

If you like learning about this kind of stuff, I got tons and tons of different Access VBA developer lessons on my website. I'm currently up to, I think, 45 levels. So if you want to learn how to program Access and really get the most out of it, come check them out. I'll put a link there. And 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.

TOPICS:
Updating form calculations on field change
Silver member question overview
Invoicing database issue explanation
Real-time form calculation needs
Understanding developer-level VBA requirements
Introduction to VBA prerequisite
Line of code placement importance
After-update event utility
Me.refresh command function
Refresh vs re-query concept
Add after-update event to subform fields
Placing after-update event in unit price field
Refreshing form before printing invoice
TechHelp free template mention
Customer orders overview
Subform design view and code builder access
Importance of placing code in dependent fields
Saving changes to VBA code
Testing updated form calculations