Fitness 10
By Richard Rost
10 months ago
Using RecordsetClone to Navigate Form Records
In this Microsoft Access tutorial, I will show you how to use a RecordsetClone to automate finding and selecting newly added records in a form, demonstrate how to handle common coding errors such as spelling mistakes in form names, and walk you through setting up and using rich text formatting in table fields and forms. This is part 10.
Members
In the extended cut, we will fix the form load sort problem where the food item sort order flips each time the form is opened, and I will show you how to add an "is active" checkbox to filter active food items so you can hide items without deleting them.
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
Up Next
Keywords
TechHelp Access, fitness database, tracking database, tables, forms, queries, VBA, RecordsetClone, Recordset, FindFirst, Bookmark, SetFocus, error handling, IsLoaded function, rich text field, FoodListF, Data tab, sort order, is active flag, checkbox filter, developer course, Access Developer 16
Subscribe to Fitness 10
Get notifications when this page is updated
Intro In this video, we continue with part 10 of the fitness database series in Microsoft Access, focusing on using VBA techniques to keep your list form synchronized after saving a record. I will show you how to use RecordsetClone and FindFirst to locate and highlight new records, handle errors such as form name typos, use SetFocus to move to specific controls, and update your table and forms to support Rich Text formatting. Although I am building a fitness database, these skills apply to any Microsoft Access project. This is part 10.Transcript Welcome to another TechHelp video brought to you by AccessLearningZone.com. I am your instructor, Richard Rost. Today is part 10 of my fitness database series. I am building a fitness tracking database for my own use, but you do not have to care about fitness. The real value is in all the techniques I am showing you - tables, forms, queries, VBA, all that stuff, which apply to any Access database, whether it is customers, inventory, or orders. The skills are all the same.
If you have not watched parts one through nine yet, go watch those and then come on back.
Like we talked about in the last video, if I add something... I think I deleted - oh, here it is - ham sandwich is still here. Let's delete the ham sandwich. Yes, notice how it sticks around down there with the ages. That is good.
Let's add something else. Let's add a yellow bunny. I do not know what a yellow bunny is. Save it, and now nothing appears to happen. I want the same kind of thing to happen. Is it even showing up here? No. See, we get the requery; then find the yellow bunny and it is down here. But I want that save button to do what the delete button does. I want it to put us on that yellow bunny.
Let's go to design view. Let's go into the code behind that save button - right here. Now, it is possible they might have closed the list form, so we are going to open it if not. If IsLoaded("FoodListF"), then we are going to requery it: Forms("FoodListF").Recordset.Requery. Otherwise, we are going to open it: DoCmd.OpenForm "FoodListF". So now the form is open either way.
Now, we have to open up - you can do find and stuff in the form, but I do not like that. I do not like jumping around between different controls, doing Find First, Find Next. That is fine, but that is basic stuff. That is for entry level developers.
We are going to use a RecordsetClone. The RecordsetClone is basically a memory version of the records that are in that form, but they are in a recordset. So you can search through them and move around through them. When you find the one you are looking for, you can then say, I want the form to match where I am in the RecordsetClone.
I have covered RecordsetClone in my developer lessons and I did cover it in this video's Extended Cut. If you look, I cover it right down here in the Extended Cut. If you want to learn more, sign up.
But here is how it works in a nutshell. We are going to Dim RS as a Recordset. I do have a free video on Recordsets. If you are not familiar with Recordsets and you want to learn a little bit about them, this video will teach you. A Recordset is basically just a way of looping through records in a table or query in memory in the computer, in VBA.
You can see right here, for example, we loop through all the customers and just display them in the status box. That is what a Recordset is good for. That is one of the things a Recordset is good for. Every form has a RecordsetClone underneath it, which is basically the same as exactly what records are in the form itself. It is a little complicated, but that is it in a nutshell.
Now, we are going to Set RS = Forms("FoodListF").RecordsetClone. Our RS is now equal to the exact same set of records in the food list. Same fields, same records, same everything. Now we can use FindFirst to find the record we are looking for in that RecordsetClone.
RS.FindFirst "FoodID=" & whatever FoodID we are on. This is the food form - remember, we are in the food form now. They are saving the food item, and we want to find it in the food list.
You should always find it. It should always be there because we just saved it. This commits it to the table, and this requeries or reloads it. So it should be in there, unless there is an error somehow, like maybe you have an indexing violation or something.
Just in case you do not find it, I like to say:
If Not RS.NoMatch Then
...I know it is a double negative. RS.NoMatch means I did not find it, which should never happen. You could write:
If RS.NoMatch Then
MsgBox "Cannot find food item."
But that should never happen. Otherwise, we have located the food item now.
We are going to set the form's Bookmark equal to the Recordset's Bookmark.
Forms("FoodListF").Bookmark = RS.Bookmark.
The Bookmark is basically what record you are on - where are you in this recordset. We did a FindFirst up here, so that is where the bookmark is. I am saying set the Bookmark of the form equal to the Bookmark of the recordset. Basically, it is going to put us on the same record.
Once we have done that, put the focus on whatever control you want. So, Forms("FoodListF")... Yes, I know I could have used a With, but I do not use a With unless I have a bunch of stuff. FoodGroup, let us say, .SetFocus. Or you can put it on the description, whichever field you want.
The only thing left to do at this point is RS.Close. And remember, if you set it, you have to forget it. Set RS = Nothing.
Yes, I do have that on my mouse pad. Yes, this is my actual mouse pad and my actual mouse. And this is my thing you can get in my store on my website. Yes, I do have a Star Trek desk mat too that lights up. It is really cool. No, I do not sell these. It is just awesome. I found it online and had to have one.
We are done with this. Let's get rid of the extra spaces and save it. Debug Compile once in a while. That is on a mouse pad too.
Now let's get rid of yellow bunny. Delete it. Let's add a new one. Let's add the ham sandwich back in. Ham sandwich. Save. And - oh, what did I do? Debug. Let's see here. Oh, okay. This is interesting, and I am glad I made it now. We are building this together because I made a stupid mistake.
I would probably say that this mistake is the cause of about 80 percent of the forum posts that I answer and the emails that I get. Now it stopped, and IsLoaded - we are going to add some error handling to this. If you hover over a form name, if you hover over - there it goes. You can see the goof. Yeah, it is a spelling error. FootList. Not to be confused with Footloose. That is what threw the error.
We will handle that in just a second. Actually, let's just handle it now. I am just going to throw - let's say IsLoaded = False. Then we are going to say On Error Resume Next. Basically, it is going to return a false unless this returns a true, because if the form name is not found in the AllForms collection, which it is not here, then it is not loaded, is it?
Now let's go find FootList. There it is. No, that is not it. Where is it? IsLoaded, but FoodList. We could probably make a more robust IsLoaded function to check to see if that exists. I will probably do that later, maybe in an Extended Cut. For now, that will fix the problem.
Now I have fixed my silly spelling error again. Let's save it. Debug. Compile. Let's see here. Ham sandwich. Save it. And it put it right down on the ham sandwich. Let's put the focus on the description field, though, not on the group.
Back into code, and we are going to go - I think it is description. Yeah. So let's do a yo-yo and then save it. Ooh, cannot find... see, another spelling error. What is it? FoodDescription. See, it is not just you. That is me too. I am horrible with some spelling.
I think it is because I put this FoodDescription in the table. Let's see. Yeah, see - it is just Description in the table, but in the query, it becomes FoodDescription because there is also a GroupDescription. So that is why that happened.
We should be good to go now. Now let's go in here and get rid of... well, actually one thing I want to do is assign these things in the groups. I am keeping some of these in here. So Cheechy beans - that is a legume. I love Cheechy beans.
Canned chicken is protein. The reason why I have got them both here is nutritionally, they are about the same. But when I do something like chicken and rice, if I open up canned chicken, that is just a serving size. Whereas if I am grilling it, I might actually weigh it, or if it is a chicken breast, it could be 4-6 ounces. That kind of thing.
Coffee is beverage. Egg, dairy. Actually, I like to store eggs under protein. I know they are dairy, but I use them as a source of protein. I like to put them in salads and stuff. Fish, protein. Grapes. That is nice - if you just click over here, this guy opens up and the focus stays on that box. That is really nice.
That is a grain. Let me scroll down here. That is a mix. Vegetables. Vegetables. That is a fat. No, oil. Yep. Rice is a grain. Salmon is a protein. See, this is a pre-packaged salmon dinner that I buy from Publix, which is our local grocery store. It is like a 4 or 5 ounce salmon filet with some noodles in it. It is really good.
Tangerine. Okay, I have got tangerine already, so we will delete that guy. Yes.
Flour tortilla is a grain. Whoops - I did not make sure that my focus was there. That is a grain. Walnuts is a nut. Yogurt. Fat free, one cup. This means I have the big giant tub of it, in which case, I will scoop it out and measure out a cup. That is why I have two separate ones.
Oikos Pro is one of my favorites. So both of these are dairy. Actually, I really consider those protein too, but we will worry about that stuff later. My food list is all good now.
One more thing I want to do: I want this to be rich text because sometimes I want to highlight this and make it big and red and bold and be like, do not buy it again.
So let's come into the table. You do not have to set it in the table in FoodT, but I like to. Long Text right here. Come down to Text Format, set that to Rich Text.
If you leave this as normal text and you set it to Rich Text on the form, what will happen is you will see HTML codes in the table design, which is not a bad thing. Sometimes I actually prefer that. Sometimes I will put two fields on a form so I can see the HTML codes and the regular what it looks like.
Now we will just go back into this guy, design view, and we will open this guy up and go to Data, then change that to Rich Text. Why this is under Data and not Format, I do not know. Save it. Close it. Close it.
Let's go into here and highlight this and hold it - highlight red, maybe do one of these jobs, and then do this and go like that. Now I do not have to buy it.
Nothing against the Aloha bar. The Aloha bar is not bad. It is just got that little bit of a chalky taste to it. There are others that are better. That is why I do not mind the Aloha bars. Save it.
All right. Got some Extended Cut stuff for today too.We're going to fix the form load sort problem. I noticed this earlier when we close it and open it and then close it and open it. Notice the food item sort is flipping every time. Now it's sorted reversed. Now it's sorted forward. See, ascending. Every time it flips, I'll explain why in the extended cut.
Non-members, you don't have that problem because yours doesn't sort. Ours does. We're going to add "is active" so we can have active food items. We're going to put a little check box right here or a little filter box right there so we can mark foods as not active if you don't plan on using them anymore but you don't want to get rid of them, but you don't want to see them in the list. We'll use the "is active" for that.
That's all covered in the extended cut for the members. For the rest of you, if you want to learn more about record sets, I know I spent some time today going over record sets. Watch that free video that I have. Also, I spend a ton of time in my full developer course working with record sets because they are very, very powerful.
You can do all kinds of stuff with record sets behind the scenes without having to manipulate forms and do stuff like that. I start covering them in Access Developer 16. From that point on, almost every lesson has something with record sets in it. It's a very powerful feature of Access that you can really use to do some cool stuff. I'll put a link to this down below as well. Check it out.
That is going to be your TechHelp video for today. I think we're going to start - I'm not going to tell you, but I think we're going to start actually making meals in part 11. My checklist of stuff is done, but I'll always find more stuff that I want to add. So that's going to do it for today.
I hope you learned something. Live long and prosper, my friends. Regardless of what we're covering, I'll see you tomorrow for part 11.
TOPICS:
Updating a related list form after saving a record
Using RecordsetClone to find and navigate to a record
Finding a record in a form using FindFirst in VBA
Synchronizing form record position using Bookmark
Error handling with On Error Resume Next when opening forms
Debugging and fixing form name spelling errors in VBA
Setting focus to a specific control on a form using SetFocus
Assigning food items to nutritional groups in a lookup table
Changing a table field to Rich Text in Access
Configuring a form control to support Rich Text input
COMMERCIAL:
In today's video, we are continuing with part 10 of the fitness database series, but the techniques you will learn apply to any Access project. I will show you how to use VBA to make your save button automatically find and highlight the new record in your list by using RecordsetClone and bookmarks. You'll also see how to fix common mistakes like spelling errors that cause bugs, and how to change a field to rich text so you can highlight or format your data. In today's Extended Cut, we will fix a form sort problem and add an "is active" checkbox to manage which items appear in your list. You'll find the complete video on my YouTube channel and on my website at the link shown. Live long and prosper my friends.Quiz Q1. What is the main reason for using a RecordsetClone in an Access form?
A. To create a backup copy of the entire table
B. To search and navigate records in the form independently of the form itself
C. To reformat text data in a table
D. To enforce referential integrity between tables
Q2. When a new food item is saved in the food form, what is the desired behavior in the FoodListF form?
A. The FoodListF form closes automatically
B. The FoodListF form deletes the first record
C. The FoodListF form navigates and highlights the newly added item
D. The FoodListF form sorts the records in reverse order
Q3. What does the following VBA line accomplish: Forms("FoodListF").Bookmark = RS.Bookmark?
A. It deletes the record at the bookmark from the database
B. It permanently locks the record in the form's recordset
C. It synchronizes the current record in the form with the one located in RecordsetClone
D. It saves the current form position as a system restore point
Q4. What is the purpose of the IsLoaded function used in this lesson?
A. To check if a table is accessible in the current session
B. To see whether a specific form is currently open or loaded
C. To validate user input in a form field
D. To reload all queries in the current database
Q5. Why is error handling added to the code when checking for a loaded form name?
A. To ensure the function always returns true
B. To prevent VBA from displaying an error if the form name is misspelled or not found
C. To force the database to close all open forms
D. To automatically correct spelling errors in form names
Q6. When setting a field to support rich text in Access, what is required?
A. Setting the field to Short Text in the table and Format to Rich Text in the form
B. Changing the field to Long Text and setting Text Format to Rich Text in the table and/or form
C. Using only the Format property in the form
D. Setting field type to Attachment
Q7. According to the video, what is a Recordset in VBA best described as?
A. A direct reference to a SQL query definition
B. An object that stores and processes form formatting options
C. An in-memory set of records from a table or query that VBA can loop through
D. A static print preview of your table data
Q8. Why might you see HTML codes in the table if you set rich text only at the form level and not the table?
A. Because Access always shows HTML for all Long Text fields
B. Because the field type Short Text does not support formatting
C. The table stores raw HTML for Rich Text, appearing as codes unless the table also supports Rich Text
D. Because Access does not support Rich Text under any circumstances
Q9. What is the main benefit of adding an "Is Active" checkbox or filter to your food items list, as planned for the extended cut?
A. To permanently delete foods from the list
B. To keep inactive foods visible at all times
C. To allow temporarily hiding foods without deleting them
D. To force the list to sort foods alphabetically
Q10. What should always be done after creating and using a Recordset in VBA?
A. Leave it open so it can be reused in other functions
B. Set the Recordset object to Nothing and close it
C. Print the Recordset to a report
D. Save the Recordset as a new table
Q11. What common programming mistake caused most errors in the video demonstration?
A. Failing to recompile after code changes
B. Typing errors such as misspelling form or field names
C. Not including enough comments in the code
D. Using outdated versions of Access
Q12. According to the lesson, what happens on the FoodListF form each time it is loaded, as noted in the extended cut preview?
A. The records are always sorted the same way
B. The sort flips between ascending and descending order
C. The list is cleared of all records
D. Only the last record is shown
Answers: 1-B; 2-C; 3-C; 4-B; 5-B; 6-B; 7-C; 8-C; 9-C; 10-B; 11-B; 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 continues my fitness database series, and today we are up to part 10. While I'm building this database for my own use in tracking fitness, the real intention is to teach you techniques with tables, forms, queries, VBA, and more. The methods covered here are universal and apply to any Microsoft Access database application, whether you are working with customers, inventory, orders, or any other system.
If you have not gone through parts one through nine, I encourage you to start with those before you move ahead. Each lesson builds upon the previous topics.
In the prior lesson, we saw how deleting an item – ham sandwich, for example – updates the list automatically and the item disappears as it should. Today's goal is to ensure that when we add a new food item, such as entering "yellow bunny" (even though I have no idea what a yellow bunny is), the form not only saves the new item but also jumps to display it in the list, just like the delete action keeps the user informed about changes.
To make this happen, I first opened the code behind the save button on my food item form. It's important to check if the list form showing all foods (FoodListF) is already open; if it is, we requery the form so it refreshes with the newly added item. If it's not open, we open it. This guarantees that the food list always includes the latest change.
Now, merely refreshing the list is not enough. While you could use basic Find operations in the form, that approach is limited and cumbersome. A better technique, which is more suitable for advanced users, is to use the RecordsetClone property. RecordsetClone is essentially a memory-based snapshot of the data currently loaded in the form. It allows you to move back and forth between records or search for specific items efficiently.
This concept of recordsets and RecordsetClone is explained in more detail in both my developer-level lessons and in the extended cut of today's video. I also have a free video specifically about Recordsets, which I recommend if you'd like to explore the basics further.
By creating a RecordsetClone for our FoodListF form, I can search for the newly saved item using its FoodID. This ensures the form locates exactly the right record we just entered. After finding the matching record, the form's bookmark property is set to the current record in the recordset, ensuring the form displays the new item straight away. This step synchronizes the display in the form to match our recordset search.
I also make sure that after locating the record, the focus moves onto the control of my choice, such as the description field, to make editing easier for the user.
Once the process is finished, it's good practice to close the recordset and set any related object variables to nothing. I also always recommend compiling your code regularly to catch errors as you go. This principle is so important to me that I even have a mouse pad to remind myself.
Throughout the process today, a couple of common issues popped up, such as typographical errors, like using "FootList" instead of "FoodList." These sorts of mistakes are the source of a large portion of the questions I receive. It's worth taking the time to add error handling or improve helper functions like IsLoaded to make your applications more robust and easier to debug. In the future, I might enhance that IsLoaded function in an extended cut.
Other tiny errors came up as well, such as referencing the wrong field names. For example, "FoodDescription" versus simply "Description" or "GroupDescription," which change depending on whether you're in the table or the query. It's a good reminder to stay aware of how field names may differ between your database objects.
With the main goals complete, I also went through and updated the groupings for some of my food items. This helps keep the database organized, assigning items like beans to legumes, chicken to protein, or eggs where they best make sense based on how I use them in my own meal planning.
One additional improvement I wanted was to allow rich text formatting in the long text fields. Sometimes, I want to emphasize certain entries in lists, like highlighting or coloring text to flag items I no longer want to buy. To do this, you need to set the text formatting property to rich text in both the table and then in the form control. If you only set it at the form level, you may see raw HTML codes in your table view, which can be useful depending on your preferences.
As I show, making these edits allows you to use formatting such as colors or bold on your data directly from the form, making the interface more customizable and user-friendly.
For those of you who are members, in the Extended Cut for today's lesson, I cover how to fix the form's sort order issue on load, which sometimes causes the list to appear in reverse depending on how the form opens and closes. I explain why this happens and how to resolve it.
We also discuss enabling an "is active" flag for food items so that you can hide foods you are no longer using without deleting them, simply by toggling their active status. This allows you to maintain a cleaner, more relevant list while still preserving historical data.
For everyone interested in deepening their understanding of Recordsets, especially the wide range of things you can accomplish with them behind the scenes in Access, I highly encourage you to watch my free introductory video or join my full developer course. Recordsets become instrumental from Access Developer 16 onwards in my series, and mastering them opens up many possibilities in Access programming.
That does it for today's TechHelp tutorial. In part 11, I plan to start working with meal building, unless I discover a few additional features to add beforehand. I hope you learned something useful today.
For step-by-step instructions and the complete video walkthrough covering this material, visit my website at the link below. Live long and prosper, my friends.Topic List Updating a related list form after saving a record
Using RecordsetClone to find and navigate to a record
Finding a record in a form using FindFirst in VBA
Synchronizing form record position using Bookmark
Error handling with On Error Resume Next when opening forms
Debugging and fixing form name spelling errors in VBA
Setting focus to a specific control on a form using SetFocus
Assigning food items to nutritional groups in a lookup table
Changing a table field to Rich Text in Access
Configuring a form control to support Rich Text input |
