Immediate Refresh
By Richard Rost
2 years ago
Instantly Update Access Form Field Calculations
In this Microsoft Access tutorial, I will show you how to immediately update calculations on a form when any single field is updated, ensuring your form data is always accurate and up-to-date.
Brady from Kirkland, Washington (a Silver Member) asks: I'm using your invoicing database for my business, and it's been very helpful; thank you. 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 100 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. So, how can I make it so that anytime any field is changed, the total updates?
Members
There is no extended cut, but here is the database download:
Silver Members and up get access to view Extended Cut videos, when available. Gold Members can download the files from class plus get access to the Code Vault. If you're not a member, Join Today!
Prerequisites
Links
Recommended Courses
Keywords
TechHelp Access, update calculations in Access, immediate form refresh, after-update event, VBA programming in Access, Access form calculations, me.refresh method, form.refresh vs form.requery, Access subform update, invoice total update, dynamic calculations Access
Intro In this video, I will show you how to immediately refresh calculations on your Microsoft Access form whenever fields such as quantity or unit price are updated. We will talk about a common issue in invoicing databases where totals do not update right away, and I will demonstrate how to use the after-update event with a simple VBA command (Me.Refresh) to fix this. You will learn where to place this code in your form and subform fields, and I will explain the difference between Refresh and Requery, so your form calculations always stay up to date.Transcript 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 calculationsQuiz Q1. What is Brady from Kirkland, Washington's concern regarding the invoicing database?
A. Calculations are slow to perform
B. The total is not immediately updated when a line item is changed
C. There are errors in the quantity calculation
D. The unit price cannot be modified
Q2. What kind of lesson is the video categorized under?
A. Beginner's lesson
B. Intermediate lesson
C. Developer-level lesson
D. Advanced analytical lesson
Q3. What is the main solution provided in the video to refresh calculations immediately?
A. Writing a complex function
B. Using an after-update event with a line of VBA code
C. Increasing the database's performance
D. Switching to a different database software
Q4. In the Tesla and Ford story recounted in the video, what does Tesla charge Ford for "knowing where to put that mark"?
A. $1
B. $10,000
C. $9,999
D. $100
Q5. The analogy of Tesla's bill to Ford is used to emphasize the importance of:
A. The amount being charged for services
B. Knowledge of where to place machinery in a factory
C. Understanding the value of expertise
D. Knowing where to put a line of code
Q6. Before adding the line of code to refresh the form, what does the presenter suggest the viewer do if they are not familiar with VBA?
A. Hire a developer
B. Watch an introductory video on VBA
C. Read a book on programming
D. Study a database course
Q7. What event triggers the 'Me.Refresh' command according to the tutorial?
A. Before a record is entered
B. After a record is saved
C. After a field is updated and the field is exited
D. Before the form is closed
Q8. What is the difference between 'Refresh' and 'Requery' as explained in the video?
A. Refresh reruns the entire form while Requery refreshes the current record
B. Refresh refreshes the current record, while Requery reruns calculations for all records
C. There is no difference; they are synonyms
D. Refresh speeds up the form, while Requery slows it down
Q9. Where should you NOT add the 'Me.Refresh' line of code?
A. Product name, because it doesn't affect the total
B. Quantity, because it affects the total
C. Unit price, because it affects the total
D. The event that prints the invoice, since it needs all information refreshed
Q10. At what point in the video are free resources on creating the invoicing database and after-update events mentioned?
A. Before explaining the problem
B. After demonstrating the issue but before the solution
C. Right after explaining the solution
D. At the end of the video as supplemental material
Q11. What is the immediate visual cue that the form has not been saved and calculations are not updated?
A. The extended price for a line item changes
B. The form's scroll bar moves
C. The total in the form footer does not change
D. A 'record dirty' message pops up
Q12. In order to resolve the immediate issue presented by Brady, where must the 'Me.Refresh' line of code be added?
A. Only to the quantity field's after-update event
B. To the after-update events of both quantity and unit price fields
C. To every field available on the form
D. Exclusively to the form's on-open event
Answers: 1-B; 2-C; 3-B; 4-C; 5-D; 6-B; 7-C; 8-B; 9-A; 10-B; 11-C; 12-B;
DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.Summary Today's TechHelp tutorial from Access Learning Zone focuses on ensuring that any calculations displayed on your Microsoft Access forms update instantly whenever a relevant field changes. This is an important issue for situations like invoicing, where you want updated totals to show as soon as you make changes, not after leaving the current record.
Let me set the stage with a real-world example. Suppose you have an invoicing database where you enter quantities and unit prices for items. When you update, say, the quantity field, the extended price for that line recalculates, but the overall total displayed in the form footer does not refresh until you leave the record. This can be a problem if you need an up-to-date total immediately — for example, if you are quoting prices over the phone. The confusion comes from seeing an outdated total at the bottom of the form, simply because the record is still "dirty" and hasn't been saved yet.
To address this, I will explain how to use a simple line of VBA code to force Access to refresh the calculations right after you update any relevant fields, such as quantity or unit price. If you are new to VBA, I recommend watching my 20-minute introduction to VBA sessions first, which cover all the basics you need to get comfortable with coding in Access.
The main technique here is understanding precisely where to place your code. I like to use the story of Tesla and Ford — Tesla charges Ford a huge fee, not for making a mark, but for knowing exactly where the mark should go. Similarly, we need to know exactly where to place our line of code so that it does its job at the right moment.
For this particular scenario, the after-update event for each field that affects your total will do the trick. When you assign the after-update event to a control, Access will run the associated code as soon as you change the value in that field and then leave it. In our context, what we need to do is use the Me.Refresh command in the after-update event for each relevant field. 'Me' refers to the current form, and Refresh forces Access to save the record and update all related calculations.
It's helpful to keep in mind that Refresh is different from Requery. Requery reloads all the records for your form, whereas Refresh just saves and updates the current record. If you're interested in more details about the difference, there is a separate video covering that topic.
So, step by step, you'll want to open your form in Design View, select each field that the total depends on, and assign an after-update event. When you set this up for, say, the quantity field, you'll write the code to refresh the form within the after-update event handler. Repeat this for the unit price field, since changes there affect the calculations too. If there are additional dependent fields in your form, add the same logic for those as needed. You do not necessarily have to add it to fields like product name if they do not factor into the calculations for your totals.
You may remember from my invoicing video that it is good practice to refresh all relevant details before printing an invoice, so that your report shows up-to-date information. In those cases, you can trigger a refresh before running the report using the same Refresh command in the relevant event handler.
Once set up, simply save your changes, test your form, and you will see that your total updates as soon as you update a relevant field. No more confusion from outdated calculations.
If you find these tips helpful and want to learn more about developing in Access, I have plenty of in-depth lessons that cover all stages of VBA programming. There are many levels available on my website to help you maximize what Access can do for your business.
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 Updating calculations immediately on field change
Explaining form footer totals not updating in real-time
Demonstrating the dirty record issue in Access forms
Using the after-update event to trigger updates
Adding after-update event to quantity field
Using Me.Refresh to recalculate form totals
Difference between Refresh and Requery actions
Adding after-update event to unit price field
Identifying which fields need after-update events
Placing code in fields dependent on calculated totals
Refreshing form data before printing invoices
Accessing design view and code builder for event handling
Testing form to verify immediate total updates |
