Requery in Place
By Richard Rost
3 years ago
How to Requery a Continuous Form in Place
In this Microsoft Access tutorial, I'm going to teach you how to requery a continuous form in place so that the data updates but the focus doesn't move. We will use an On Got Focus event with a Me.Recordset.Requery, and I'll also show you how to make a custom edit form with a Close event to do the same thing, but is better for large datasets.
Damaris from Milwaukee, Wisconsin (a Platinum Member) asks: I have a continuous form much like your customer list form, however because I have some aggregate functions in there, it's not updateable. I can made it so I can double click to open up a single form where I can edit records individually but then when I return back to the continuous form the data doesn't update. And if I issue a requery command, it puts me back up at the top of the list. Is there any way to refresh that form and update the data without the focus moving?
Members
Members will learn how to edit data in a non-updateable continuous form in place using one of my secret special tricks. Curious as to what the trick is? Join today!
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!
Pre-Requisites
Links
The Code
Recommended Courses
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, Retain position on continuous form through requery, Refresh or requery, Form.Refresh, Form.Requery, refreshing or requerying a continuous form, Restoring a continuous form after Requery, Requery a Form in Place, Form.Recordset.Requery, requery a single record in a continuous form microsoft access, edit non-updateable form in place
Intro In this video, I will show you how to requery a continuous form in Microsoft Access without losing your place or having the focus move to the top of the list. We'll talk about why aggregate queries can make your form non-updatable, how to display calculated values like order totals, and the issues that arise when trying to refresh data. You'll learn the difference between using Me.Requery and Me.Recordset.Requery, see how to trigger a requery with a button or automatically, and get tips for handling both small and large sets of records.Transcript Welcome to another TechHelp video brought to you by AccessLearningZone.com. I am your instructor, Richard Rost. In today's video, I'm going to show you how to requery a continuous form in place without the focus moving.
Today's question comes from Demaris in Milwaukee, Wisconsin, one of my platinum members. Demaris says I have a continuous form much like your customer list form. However, because I have some aggregate functions in there, it's not updatable. I can make it so I can double-click to open up a single form where I can edit records individually, but when I return back to the continuous form, the data does not update. And if I issue a requery command, it puts me back at the top of the list. Is there any way to refresh that form and update the data without the focus moving?
There is a lot to unpack in this, so I'm going to take it from the top and explain everything that Demaris is talking about. When we're done, you're going to see there's a really cool solution to it.
To start off with, yes, this is a developer-level video. Even though the solution really only involves one line of code, you have to know everything else that Demaris is talking about to get to that point. First, if you've never done any VBA programming before, go watch this. It's free. It's on my YouTube channel. It's about 20 minutes long. It teaches you all the basics.
Next, you will need to know what an aggregate query is. That's where we can sum things up by groups. Go watch this video. An aggregate query will do this to your forms. If you base a form on a query that has aggregate functions in it like SUM, it will make your recordset not updatable. You cannot change any of the values or add new values because of the calculations. This video will explain that in more detail and why Demaris cannot modify the records right in this form.
Here I am in my TechHelp free template. This is a free database. You can download a copy from my website if you want to. Here I have a customer list that is just based on the customer table. If you go to the properties here and go to data, you will see it's just customer T. So it's nice and simple.
I can edit records right in place here if I want to. Or if I open up this record by double-clicking on the ID and change something here, when I close this and come back, it's updated for me because it has a simple table as the underlying record source. However, if this form is based on a complicated query that might have some functions in it like aggregate functions, you lose the ability to update this data.
Let's say I want to add the customer's total value on here, the total of all of their orders in the past. We can do that with a simple aggregate query. Go to query design. Bring in the customer table. I'll bring in the ID, first name, last name. Then we want to bring in their order total. For that, we're going to need their order T, which will link to their order table. Bring that in. That creates the link. Then bring in their order detail query because the query has the extended price in it. The sum of the extended price gives us their order totals.
If you have not watched my invoicing video where I build the order entry system, go watch that too. I'll put a link to that down below as well. That explains the order table and the order detail query in more detail.
Now I can bring in the extended price, which is the quantity times the unit price for each item. Add it up. Simple math. Make this an aggregate query. Change the group by here to SUM. Now when I run this, I get my customers and the sum of extended price over here is the sum for all of their orders.
Let's change sum of extended price. Let's call it order total, with the alias order total. You can see there's that screen bug popping up. There's a bug in Access right now where if you go to run a query and then go back to design view, the font is all messed up down here. That's not you. It's not me. It's an Access issue. They know about it. The Access Forever guy has posted about it.
There's your total. Notice you're missing some customers too, so you might want to make these outer joins. Double-click on the joins. Include all records from customer T. Then do the same for order T. Now when you run it, you'll see everybody in there. You will get some blanks in there too. For that, we can change this to NZ null zero. If it's null, it'll show up as zero.
I do not usually do my formatting in my queries. I'll save that for the form. Anyway, we have all the customers now, with all their order totals. That's good. But notice this recordset is now no longer updatable. I can't add a new record. I can't click in here and change anything. This is what Demaris is dealing with in his form.
Let's save this query. I'm going to save this as my customer order value Q. We're going to base our list form on this query. Close this up. Go back to our customer list. We're going to change where this form gets its data from. Open that up and change it to the query that we just made, the customer order value Q.
Now, right away, you can see there are some fields missing because I didn't bring state or customer since. Let's say we don't need those. This credit limit is now going to have that value in it, so we're going to say order total. Double-click on this. Go into the control source. Change that to order total. Don't forget to change the name as well. Format it as currency so it looks nice and pretty.
Save it. Close it. Close it. Open it back up again. There's our form. We have all of our customers and their order totals. But again, notice you can't update this stuff. You can't click in here and change anything. It's now a non-updatable recordset.
What I usually do at this point is make things the user can't update gray. I'm going to give these controls a light shade of gray. It visually tells the user they cannot update this stuff.
Now, let's say I want to change something about Richard Rost. I want to change his name to Rick. I cannot click in here and make a change, but like Demaris says, you can double-click here to open up the customer form and make the change. Change it and close it, but it does not update this form unless you close it and reopen it, which requeried the data in the form.
Refresh won't work. Refresh just redos the calculations. You have to requery the actual data set under the form itself.
Now, where could you do that requery command? You could put a button on here to requery it manually. That's always an option. I'll show you how to do it real quick. It's simple.
Grab a button and drop it down here. Cancel the wizard. Yes, you can do it with the wizard, but I like my own requery. Open it up. Let's call this requery button BTN. Right-click, build event. What's going in here? It's just me.requery. That's it.
Close it. Close it. Open it. Now if I make a change and close it, I can hit requery and it requeried for me. There we go.
The problem is if you're down here and you edit, let's say, Geddy Lee and you hit requery, it pops you back up to the top of the list. But there's a trick, and here's the one line of code that I told you that you needed before.
Instead of doing a me.requery, do this: me.recordset.requery. What that will do is it will requery the data in the form without changing the form itself, including where the focus is and what record you're sitting on.
Come up here, save it, close it, open it. Come in here. This just happens to be right over the spot where that command button is. I'm going to move this over here, the control box. Let's do that again. Now that issue is gone.
If I come back in here and put this back to Richard and close it, then hit requery, it requeried nicely. Now if I come down here and edit Geddy Lee, change this to Gary, which is his real name, then requery, look at that. It requeried right in place without changing where the record is sitting.
If I come in here for Christopher Pike, double-click, change this to Chris, close it, then requery, look at that. It didn't move. We need that recordset requery.
Now you might not want the user to have to click our requery button. Can this happen automatically? Yes. There are two places you could trigger that. You could trigger it in the close event of this form. Assuming that the only way they get to this form is through this method, you can just in the close event, make this one modal so users cannot click behind it, and then put it in the on close event. Or another way is in the on got focus event for this field. Because they have to double-click on this to get there, when this field gets the focus back after this form closes, it'll requery itself.
I wouldn't do that if you have tens of thousands of records in this form because it will slow things down, but if you have a couple hundred or a couple thousand records and a fast connection, you can do it in the on got focus event.
Got focus was yesterday's video. Go watch this.
In design view, come to this control. Event on got focus is right here. Put that command in there: me.recordset.requery. Save it. Close it. Close it. Close it. Open it.
Now notice as you click around, it's running that requery in the background. But I only have 29 records; it's pretty fast. If you have 20,000 records, it might slow down a little bit, so you might want to consider another option.
Alternatively, if you only want to edit a couple of things on this form like first name, last name, and a few other fields, make a custom form. Here, let me show you that this works first before I branch off.
Let's go to William Riker, double-click, change William to Bill. As soon as I close this form, it updates because the control got the focus back.
If you have lots and lots of records and don't want to use on got focus, you could make a special editing form just for this process if you don't want to open up the full customer form. Take the customer form, copy and paste it. Let's call this customer edit form or whatever you want to call it.
Right-click, design view. Let's say you only want to be able to edit certain things. Delete any not needed fields. You are only interested in first name and last name, let's say. Give a little color.
We're going to make this form modal. I hate popup because popup has problems on multi-monitor systems. I have two monitors now. I just downgraded from four monitors to two, but I got a big one, so it's nice. But still, popup will put that window randomly. I don't like popup.
Modal means you can't click behind until you close the form. Here's my video on modal versus popup if you want to watch that.
Now change this form, so when you double-click on this control, instead of opening the customer form, you can open customer edit F. Change this in the code. The code checks if the ID is blank. We can get rid of that got focus event to speed things up with many records.
Save it. Close it. Close it, open it now. When you open this, it brings up the new modal form. Model forms will hide the navigation pane like that; it just snaps it shut. That's just how they behave. End users should not see that because you will hide the navigation pane for the end user. If you don't know how to hide it, go watch my simple security video. I'll show you how to do that.
Now, if you open up Jean-Luc Picard, you get this. You can't click behind it until you make changes. You can hide this and other things. If I come in and call him Johnny, there you go, like Q calls him. It didn't update yet. We need to put that code in the close event for this form.
Right-click, design view. Go to events, go to either on close or on unload. In the code, when this form closes, you will say: Forms!CustomerListF.Recordset.Requery. This is the same as saying ME on the other form. Forms!CustomerListF is saying go find that form, then do the recordset requery for that form. It has to be open because it's the only way you can get to this modal edit form.
Now, open up the customer and change Johnny again, close it, and look at that. It updates. If you're down here, go to Mr. Data, double-click, call him Mr., close it, and it updates right there.
Whenever I do a video like this, I always give it a quick Google before I start to do a little research and see what everyone else has to say about this topic. Mike over at No Longer Set covered this a couple of years ago, requery your form in place, and he goes into a lot more detail on other methods like Form.Refresh, Form.Bookmark. Daniel Pano has a big, long piece of code, so check that out if you want to. At the top, he says TLDR use Form.Recordset.Requery. That's it.
Like Mike, I want to give you the background as to why this happens. When you get into situations where you can't update the data in a form, it happens a lot. I get asked that all the time. That's why I made the other video on non-updatable recordsets.
If you put any kind of calculations in a query, you aggregate it. For some calculations, you can still get away with it. For example, if you put a DLookUp or DSum in your query, chances are you will probably still be able to update the other fields, but not the DLookUp field. Sometimes if you have really complex queries, like sixteen tables joined together, you won't be able to update it. That's why: it's too complicated. Break it down, make it simpler. Take your form, and if you want to update any of that data, make a double-click event that opens another form. I'll put a link to my open form video down below. I'll also put a link to Mike's article.
If you want to learn more about this topic, in the extended cut for the members, I'm going to teach you a trick where you can actually still edit those records in place, even though it's a non-updatable recordset. How do you do it? That will be covered in the extended cut for the members. Silver members and up get access to all my extended cut videos. Gold members can download all of these databases and get access to the code vault.
If you are really interested in learning more about developing applications with Microsoft Access, be sure to check out my developer series of courses. They teach you everything you need to know from beginner up through advanced level developer programming, starting with an introduction to VBA and going all the way up to recordsets and advanced stuff.
Check it out on my website; you will find links down below. Check out the extended cut if you're interested.
That, my friends, is your TechHelp video for today. I hope you learned something.
Live long and prosper. I'll see you next time.Quiz Q1. What is the main problem Demaris faces with her continuous form in Access?
A. She cannot create aggregate queries
B. The form does not update after record edits and requerying moves the focus to the top
C. The form is too slow to open
D. Users can update fields they should not be able to change
Q2. Why is the recordset in an aggregate query often not updatable in Microsoft Access?
A. Aggregate queries lock the entire database
B. Aggregate functions like SUM change the data type
C. Calculated results prevent changing the original source data in the form
D. Forms based on tables with images are never updatable
Q3. What happens if you use a standard Me.Requery in your form's VBA code?
A. It saves the current record but does not reload data
B. It refreshes the calculations but keeps the current focus
C. It reloads the data and returns the focus to the top of the form
D. It updates only the controls on the form, not the recordset
Q4. What single line of VBA code does the instructor recommend to requery the form without changing the current record focus?
A. Me.Refresh
B. Me.Requery
C. Me.Controls.Requery
D. Me.Recordset.Requery
Q5. When would it be inadvisable to use the On Got Focus event to trigger a requery?
A. When there are more than four fields in your form
B. When the form contains only a few records
C. When you have a large number of records that could slow performance
D. When users should not navigate between records
Q6. Why does the instructor recommend using a modal form over a popup when editing records?
A. Modal forms always open full screen
B. Modal forms are faster to load
C. Popup forms may appear unpredictably on multi-monitor setups
D. Modal forms support animations, while popups do not
Q7. What change is required to update the main list form after closing a modal edit form?
A. Add Me.Requery to the list form's On Load event
B. Use Forms!CustomerListF.Recordset.Requery in the modal form's On Close event
C. Refresh the subform from the main form
D. Rely on Access to refresh automatically
Q8. Why is it a good idea to make non-updatable controls on a form a light shade of gray?
A. To help the system run faster
B. To visually indicate to users that those fields cannot be edited
C. To match the Access default theme
D. Because only gray fields support calculated controls
Q9. If you base a form on a query with DLookUp or DSum functions, what will often be true?
A. None of the fields will be updatable
B. All fields remain updatable
C. You can usually update non-calculated fields, but not the DLookUp or DSum result fields
D. DLookUp disables all editing on the form
Q10. What should you do if you need to edit only a few fields, like first name and last name, from a complex main form?
A. Edit directly in the main form
B. Create a custom edit form with only those fields
C. Add calculated controls to the main form and lock them
D. Switch the main form to datasheet view
Answers: 1-B; 2-C; 3-C; 4-D; 5-C; 6-C; 7-B; 8-B; 9-C; 10-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 how to requery a continuous form in Microsoft Access without losing your place in the list. This lesson is for those of you who work with forms based on aggregate queries, where the normal editing and updating features are no longer available.
The problem we're addressing comes from a scenario with a continuous form similar to a customer list. If your form is based on an aggregate query that includes calculations such as SUM, the resulting recordset is not updatable. That means you cannot directly edit records in the list. A common workaround is to allow the user to double-click a record to open a single form where edits can be made. However, when you return to the continuous list, the new data does not appear unless you issue a requery. The challenge is that a standard requery command moves the focus back to the top of the list, which can be quite frustrating, especially when working with long lists.
Let's break down everything you need to know about this situation and how to resolve it.
First, if you have never worked with VBA in Access before, it is essential to get comfortable with the basics. I have a free introductory video available on my YouTube channel to help you with this step.
Next, understand what aggregate queries are. If you base a form on a query that uses aggregate functions like SUM, your form becomes non-updatable. This is because Access cannot reconcile updates through the summary calculations. That is why you lose the ability to directly edit or add records in your continuous form.
To illustrate, I have a free sample database featuring a simple customer list form using a direct table as the source. In such a form, you can edit records in place, and changes are instantly visible. However, when you switch the source to a query containing aggregate functions — for instance, if you want to add each customer's total order value — the form becomes read-only. That happens because the sum of extended prices comes from grouping and summarizing records in related tables.
To construct this type of query, you would bring in the customer table, link it to the order table and the order details, and then sum up the extended prices to get the total for each customer. After running the query, you will see customers and their total orders, but now the query is not updatable. You cannot add or change records directly within this set.
Once this aggregate query is built, you can adjust the form to display the new calculated total order values. I recommend formatting computed controls (like order totals) in a distinct color such as gray to indicate to users that editing is not allowed.
Now, when you try to edit a customer, you must do this by opening a separate form in single-form view. When you make a change and close that editing form, the continuous form does not automatically update to reflect the changes. If you use the standard requery command, the form will redraw its data but will lose your position and jump back to the top of the list. The refresh command is not effective here; it only recalculates what is already loaded and does not fetch fresh data from the source.
To handle this, you can put a manual requery button on your form. This lets users choose when to reload data. However, using the typical method will cause the focus issue mentioned earlier. The trick is to requery just the recordset, not the whole form. There is a specific way to do this using a single command in VBA that tells Access to update the data while preserving your current place and selection within the list. This method allows updated information, such as an edited name, to appear right away without resetting your scroll position.
If you want the update to happen automatically, you can use the form or control events in Access. For example, after the single-form edit is closed, you can use either the On Close event of the edit form or the On Got Focus event of the control on your list form. Of course, you should be cautious with very large datasets since automating a requery every time focus is regained could slow performance. For small and medium-sized lists, this approach works well.
Alternatively, if you only need to allow users to update a handful of fields, consider making a dedicated, simplified editing form. You can design a modal form so users cannot interact with the main list until they finish editing in the pop-up. This improves usability and prevents confusion, especially if your users will access the database in a secured or locked-down mode.
The editing form can trigger the requery of your main form when it closes, ensuring that the displayed data is always up-to-date for the user. If you rely on modal forms for editing, be aware the navigation pane might momentarily disappear — this is normal behavior for modals, and you can configure the user interface accordingly.
This situation often comes up when working with more advanced data sources and queries in Access. If calculations or complex joins make the recordset non-updatable, consider routing edits through a secondary form and then updating the main form's data using the optimized requery approach. For complicated scenarios, remember to keep queries simple and avoid unnecessary complexity in the recordsource.
If you want to explore this topic further, in today's Extended Cut for members, I cover a trick for editing non-updatable records directly in place. This technique lets you work around some of the restrictions imposed by aggregate queries. Extended Cut videos are available for Silver members and higher, who also get access to additional downloads and resources, including sample databases.
If you are looking to deepen your knowledge of Microsoft Access development in general, I have a full series of courses available. These cover everything from beginner concepts up to advanced developer programming, working with VBA and recordsets.
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 an aggregate query for totals
Setting aliases for query fields
Making aggregate queries updatable with outer joins
Handling nulls in query results with NZ
Formatting currency fields in forms
Binding a form to an aggregate query
Identifying non-updatable recordsets
Visual cues for non-editable form fields
Opening a single form on double-click to edit records
Adding a requery button to a form
Using Me.Requery versus Me.Recordset.Requery
Preventing focus movement when requerying
Triggering requery in the On Got Focus event
Considerations for large recordsets and performance
Creating a modal popup editing form
Using Forms!FormName.Recordset.Requery for parent forms
Opening a modal form from a continuous form
Putting a requery command in a modal form's close event
Removing unnecessary fields from an edit form
Difference between modal and popup forms in Access |
