Reports Not Updating
By Richard Rost
4 years ago
Automatically Refresh Report Before Print or Preview
Do you have a problem with reports not updating when you go to print or preview them in Microsoft Access? In this video, I'll show you how to refresh the record automatically before printing. We'll get rid of the embedded macro created by the command button wizard and write two lines of VBA code to handle the job.
Pre-Requisite
Links
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, #fasttips, microsoft access reports not updating, My report does not display all of the data, Do Access reports automatically update, How To Fix Access Database Not Updating Issue, microsoft access reports are not updating automatically, me.refresh, docmd.openreport
Intro In this video, we'll look at how to fix the common problem of reports not updating properly in Microsoft Access. I'll show you how to make sure your data saves and refreshes automatically before printing or previewing a report. You'll learn the differences between using embedded macros and VBA code for this task, and see a step-by-step example of editing a button to ensure your report always shows the latest information.Transcript Welcome to another Fast Tips video brought to you by AccessLearningZone.com. I am your instructor, Richard Rost. Are your reports not updating properly in Microsoft Access? In this video, I'll show you how to automatically refresh them before you print or preview them.
Here I am in my TechHelp free template. This is a free database you can download a copy of from my website if you want to. If you want to see how I built it, go watch this video. It's free. It's on my website. It's on my YouTube channel. I'll put a link down below for you.
In this database, I've got customers, and customers can have orders, and an order can have a note on it. When I go to print the invoice, the note shows up right there. So this is an order note. But what happens if I change this? If I type in something like "more notes added," like that, and then I click the invoice button, look at that. The old order note is still showing up there. What's going on?
The problem is if you look right there, you'll see that little pencil. That means that this record is what's called dirty. That means this data that you see in the form has not yet been saved to the table. If you open up the order table and find order one, look, all you can see right there. It's locked because it's dirty. And over here it still says this is an order note. This hasn't been committed to the table yet. Save changes? No.
This isn't your fault because I built this button with the Command Button Wizard to open up a report. The wizard doesn't refresh this record for you automatically, so we have to do it ourselves.
Now, there are a couple ways you can do it. You can leave this record, and by clicking down here in the subform, that technically saves this guy. Now you can see that it's been updated.
Before you can close this form and come back into it, that's all a pain. I want to be able to update something here, and then when I click this button, it automatically refreshes for me.
Now, when you make this button using the Command Button Wizard, unfortunately it puts something in here called an embedded macro. I hate macros. I really can't stand them. In fact, I have another whole video coming up that explains why I don't like them. I'm not going to go into all the reasons here, but basically they're really hard to deal with.
You can try to edit this macro. Click the little dot-dot-dot button. It opens up the macro editor here. You could drop this down here and try to find refresh. There's the refresh. Run menu command, refresh. Then you can click on this guy here to move it up so it runs before the report. Then you can close this, save changes (yes), and that'll do it. But that's a lot of steps.
So, if I go to orders now, let me move this back where it was, and I type in, let's put a bunch of X's and O's and different letters and stuff in here. If I go invoice now, as you can see, the refresh does run first and it now puts the correct information. That's one way to do it. If you just want to fix your buttons by editing the macro, that's fine. That'll work.
But I really, really, really prefer VBA code. I like to do things with VBA. I hate embedded macros. In fact, one of the reasons I found this button was because I built this originally in that video I told you to go watch earlier and I used the Command Button Wizard to make that button to keep it easier for beginners. But I really like to get rid of all my macros and put VBA code in there.
Now VBA isn't scary. Watch this video. It's 20 minutes long. It teaches you all the basics. Everything you have to know. We're going to basically need two lines of code to do this, and you'll see it's a whole lot easier than working with embedded macros.
How do I replace that? Well, design view. Let's go into this button and go here where it says embedded macro and press delete. That'll get rid of that embedded macro.
So what are the steps? What do we have to do? We have to refresh the record and open the report. The report that we're opening is "order invoice R," which is based on "order invoice Q." I covered that in the invoicing video, and the "invoice Q" puts together the order information, the order detail information, and our line totals in there. It displays the proper order because it has to only display the order ID for the one that we're on. So the query takes care of all the work. All we have to do is open up that "order invoice R."
Right-click, build event. I'm in my code builder. If you get the little prompt that says what builder do you want, pick your code builder.
First, refresh the record: Me.Refresh. "Me" means the current form that you're on. Then we're going to say DoCmd.OpenReport. What's the report name? "order invoice R," comma. We're going to go to acViewPreview, because if you don't pick that, it's going to spit it right to your printer. You want preview. That's it. We don't need a filter. We don't need a where condition. That's all handled by the query.
That's it. See how easy that was? Two lines of code. You need to know Me.Refresh and DoCmd.OpenReport. That's it. Save it, close it, close that, close this, open up the orders again. I'll slide back over here. I move things around a lot in class, but usually when you design it, if you just leave it in one spot, Control-S to save its position. This is an order note: Live long and prosper. Here we go. Notice it's still dirty. Ready? Click. Refreshing it. And it opened up, and there's a "live long and prosper." There you go. See how easy that was? Super easy, and a whole lot better than using embedded macros.
So there you go. There's your fast tip for today. That was pretty fast. Did you learn something? I hope you did. I know I learn something every day. I try to. We'll see you next time.
How do you become a member? Click on the Join button below the video. 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.
Silver members and up will get access to all of my extended cut TechHelp videos, one free beginner class each month, and more.
Gold members get access to download all of the sample databases that I build in my TechHelp videos, plus my Code Vault, where I keep tons of different functions that I use. You'll also get a higher priority if you decide to submit any TechHelp questions to me, and you'll get one free expert class each month after you finish the beginner series.
Platinum members get all the previous perks, plus even higher priority for TechHelp questions, access to all of my full beginner courses for every subject, and one free developer class each month after you finish the expert classes. These are the full-length courses found on my website, not just for Access too. I also teach Word, Excel, Visual Basic, and lots more.
You can now become a Diamond sponsor and have your name or company name listed on a sponsors page that will be shown in 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.
But don't worry, 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 enjoyed this video, please give me a thumbs up and post any comments you may have. 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, and click on the bell icon to select all to receive notifications when new videos are posted. Make sure you click the Show More link down below the video to find additional resources and links. 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, so if you'd like to get an email every time I post a video, click on the link to join my mailing list.
Even if you don't want to become a member, feel free to donate to my tip jar. Your patronage is greatly appreciated and will help keep these free videos coming. I have puppies to feed.
If you have not yet tried my free Access Level 1 course, check it out now. It covers all the basics of Microsoft Access. It's over four hours long, and I just updated it for 2021. You can find it on my website or on my YouTube channel. I'll include a link below that you can click on. Also, if you like Level 1, Level 2 is just one dollar. Yep, that's all, one dollar, and it's free for all members of my YouTube channel at any level, even supporters.
Want to have your question answered in a video just like this one? Visit my TechHelp page on my website, and you can send me your question there. While you're on my site, feel free to stop by the Access Forum. Lots of good conversations happening there. Be sure to follow my blog, find me on Twitter, and of course, YouTube.
Once again, my name is Richard Rost. Thank you for watching this TechHelp video brought to you by AccessLearningZone.com. I hope you enjoyed this video and that you learned something today. I'll see you again soon.Quiz Q1. What is the main problem addressed in the video?
A. Reports in Access not printing in color
B. Reports in Access not updating with the most recent data
C. Reports in Access missing formatting
D. Reports in Access printing too slowly
Q2. What causes the "old order note" to still appear when printing an invoice immediately after editing it?
A. The computer's printer settings
B. The data has not been saved (record is "dirty")
C. The report is out of date
D. Macros are disabled
Q3. What does the little pencil icon next to a record indicate in Access?
A. The record is locked by another user
B. The record has unsaved changes ("dirty")
C. The record is currently being printed
D. The form is in read-only mode
Q4. What does the Command Button Wizard use by default to open a report?
A. VBA code
B. Embedded macro
C. SQL query
D. HTML script
Q5. According to the video, why does Richard prefer VBA over macros?
A. Macros cannot be edited
B. Macros are more difficult to work with and manage
C. Macros are faster than VBA
D. VBA cannot refresh records
Q6. What is the purpose of the "Me.Refresh" command in VBA?
A. To reload all tables in the database
B. To save the current form's record to the table
C. To close the current form
D. To print the current report
Q7. Why should you use "acViewPreview" when opening a report with DoCmd.OpenReport?
A. To print the report automatically
B. To filter the report by the order ID
C. To view the report on screen before printing
D. To export the report to Excel
Q8. Which of the following code snippets will correctly update the form and open the report as demonstrated in the video?
A. DoCmd.OpenReport "order invoice R", acViewPreview
B. Me.Save: DoCmd.OpenTable "order invoice R"
C. Me.Refresh: DoCmd.OpenReport "order invoice R", acViewPreview
D. Me.Update: DoCmd.OpenQuery "order invoice R"
Q9. What is the name of the report being opened for the invoice?
A. "order invoice"
B. "order invoice Q"
C. "order invoice R"
D. "order invoice F"
Q10. What membership level allows you to download all sample databases and access the Code Vault?
A. Silver
B. Gold
C. Platinum
D. Diamond
Q11. What is a benefit exclusive to Platinum members according to the video?
A. Early access to new videos
B. Access to full beginner courses for every subject
C. One free beginner class per month
D. Access to Code Vault
Q12. If you want to ensure your edited data appears on the report immediately, what should you do before running the report?
A. Close and reopen Access
B. Refresh the record (save it)
C. Print the report to PDF first
D. Export data to Excel
Q13. What are the two key VBA lines needed to refresh the form and open the report?
A. DoCmd.Save, Me.Print
B. Me.Refresh, DoCmd.OpenReport
C. DoCmd.Close, Me.Update
D. Me.RunMacro, DoCmd.OpenTable
Q14. What extra step must you perform if you use the macro editor to refresh data before the report opens?
A. Move the Refresh command above the OpenReport command
B. Change the database settings
C. Update all table relationships
D. Set the report to print automatically
Q15. Where can you get access to the free TechHelp template shown in the video?
A. Only by buying a course
B. By sending an email request
C. Downloading from the instructor's website
D. By subscribing to the newsletter
Answers: 1-B; 2-B; 3-B; 4-B; 5-B; 6-B; 7-C; 8-C; 9-C; 10-B; 11-B; 12-B; 13-B; 14-A; 15-C
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 covers a common issue in Microsoft Access: how to make sure your reports display the most up-to-date information when you print or preview them.
In my free TechHelp template database, which you can download from my website, I have a setup with customers, their orders, and order notes. When you print an invoice, the associated order note appears on the report. However, if you update the note on a form and then immediately print the invoice, sometimes the old note still appears on the report. Why does this happen?
The issue lies in how Access handles record updates on forms. If you see a small pencil icon next to the record, it means the record is "dirty" or unsaved. The data you see on the form has not yet been committed to the underlying table. Access locks the record, and if you open the table directly, you will not see the most recent changes until the record is saved.
This problem typically arises because the Command Button Wizard, which many people use to create report-opening buttons, does not refresh or save the current record before running the report. So when you update a note and click to print the invoice, the report opens with the data as it exists in the table, not the unsaved changes from your form.
One manual workaround is to move off the current record in your form, such as clicking in a subform or elsewhere, which commits the changes. However, this is inconvenient and disrupts the workflow. Ideally, you want the report to refresh automatically whenever you update a field and immediately run your report.
The Command Button Wizard creates buttons using embedded macros by default, which do not refresh the current record. While you can edit the macro and insert a refresh command before the report runs, this method involves several cumbersome steps. You have to open the macro editor, add the refresh command, and ensure it runs in the correct order. Although it works, it adds unnecessary complexity.
Personally, I much prefer using VBA code over macros. VBA offers more control and flexibility, and it is easier to maintain and troubleshoot. If macros seem intimidating, I encourage you to watch my beginner VBA video on my website, which covers all the basics you need to get started. In this case, solving the report refresh problem with VBA only takes two lines of code.
To switch from a macro to VBA, open your button in design view, remove the embedded macro, and choose to use the code builder. In your button's event code, you just need to refresh the form so any changes are saved, and then open the report in preview mode. The exact report and query setup, which I explain in detail in my invoicing video, ensures the report displays only the order you are currently viewing.
Once you add this simple VBA code, any changes you make—even if the record was still dirty—will be saved before the report previews. You no longer have to worry about old data showing up, and you avoid the hassle of macros altogether.
That is the fast tip for today. I hope you learned something useful. I am always learning myself and enjoy sharing these practical solutions with you.
If you would like to learn more, you can join my channel with different membership levels for access to extended videos, sample databases, the Code Vault, priority for TechHelp questions, and even full courses for advanced members. If you prefer not to join, you can still support my work with a tip or by subscribing to my mailing list for notifications when new videos are available.
If you have not yet tried my free Access Level 1 course, it is available on my website and covers all the basics in detail. Also, if you want your specific questions answered in a future video, visit my TechHelp page and submit your question.
Thank you again for watching this TechHelp video brought to you by AccessLearningZone.com. 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 Explaining why reports might not update in Access
Identifying dirty records and unsaved data
Using macros to refresh the current record
Editing embedded macros to add a refresh command
Replacing embedded macros with VBA code
Writing VBA to refresh a form before opening a report
Using Me.Refresh to save current form changes
Using DoCmd.OpenReport to preview reports with updated data |
