Fitness 18
By Richard Rost
3 months ago
Using RecordsetClone to Navigate, Sync Form Records
In this Microsoft Access tutorial, I will show you how to improve your fitness database forms by replacing filtering with a recordset clone to let users navigate records more smoothly, handle bookmark setting when selecting items, fix errors caused by null values, use requery methods when adding new records, and control tab order and navigation behavior for a better user experience. This is part 18.
Members
There is no extended cut, but here is the file 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
Up Next
Keywords
TechHelp Access, recordset clone, on current event, meal list form, parent.bookmark, findfirst, nomatch, Nz function, requery, tempID variable, tab order, cycle property, bookmark, multi-user database, aggregate query, description selstart, me.dirty, recordset requery, custom delete button
Subscribe to Fitness 18
Get notifications when this page is updated
Transcript
Welcome to another TechHelp video brought to you by AccessLearningZone.com. I'm your instructor, Richard Rost.
Today is part 18 of my fitness database series. If you haven't watched parts 1 through 17, go watch those first. As a reminder, if you don't care about fitness, that's fine because these tricks work in every database you could possibly build.
So let's get back to it. In the last class, we merged our meal list so it's in one form, and I want to make this guy behave more like this guy, the food list, which lets us browse the items down here if we want to. Over here we can't, because we're filtering. So instead of filtering, let's use a recordset like we did back in fitness level 10.
When this guy is clicked on, we'll seek for that item inside the recordset and then set the bookmark. Some more advanced stuff here.
We need to go back to the On Current event for this guy. I do this a lot. Sometimes it just happens. Sometimes the first time through, I just like to make it work. Now it's working. But how can we make it work better?
Filtering is okay, but I want it to work like the other one so the user can still use the navigation buttons to go back and forth. How do we do that? Back into the On Current event. Let's get rid of this guy. This is going to be a little more complicated now.
We need a recordset: Dim RS as a recordset. I don't have a video specifically on recordset clones yet, but I have covered them in a lot of other videos. I have to add it to my TechHelp list - a recordset clone only video.
Now we've got the recordset defined. We're going to set the recordset equal to the parent's recordset clone: parent.recordsetclone. What is that? Well, the recordset is the list of records that are in that form, and its recordsetclone is something we can use to manipulate, change things, and move through things without actually changing the records themselves.
So we can do RS.findfirst, same criteria: where the meal ID equals meal ID. It's going to take the recordset clone, which is a behind-the-scenes hidden recordset pointing to the same set of records in that form, and we're just going to find the first record that matches our meal ID. It's not going to actually change anything yet.
Now it shouldn't happen, because we are literally clicking on a meal ID, but just in case: if RS.nomatch, if something happens where it can't match it, then we're just going to yield the user status: record not found. We'll make it red. That should never happen, otherwise we're going to say parent.bookmark equals RS.bookmark.
I mostly do this because I hate writing the reverse negative here: if not RS.nomatch - the double negative doesn't make sense when you read it. So this makes more sense. I like writing clean code, not fancy code. This is easier to write, it's easier to read, even though this should never happen. Unless you've got another form open and the user deletes that record before this form is refreshed, is it possible? It's possible, especially if you're in a multi-user database.
So we're basically saying set the parent's bookmark, which is the parent form, that meal F. Set it equal to RS.bookmark; in other words, the record that I found up here.
Now we're basically done: RS.close, set RS = nothing. If you set it, you have to forget it and save it. Close it, open it, and look at it. There we go. Beautiful.
Now, I have the focus jumping down here. If you want to keep the focus up here, that's fine. Since this isn't editable, I kind of like that it jumps down here, because if they click here and want to change it, they can't, but down here they can make and type in changes.
That brings us to our other problem we had last time. We got an error if we try to go to a new one: invalid use of null - debug. Why is that? It's trying to set the description selstart equal to the length of description, but length is already zero. You could fix this by checking to see if it's null, or you can just say Nz(description), and that will convert it to zero.
So when I go to a new record now, I don't get the problem. But again, if you like writing clean code, just put 'if not isnull(description) then do that,' but this will work fine too. I guess sometimes I sneak in tricks and that's okay.
Now, this Description_AfterUpdate we can get rid of, but we're probably going to want to requery this guy when a record is added down here.
What's the name of this guy up here? This is meal list F. So let's come in here and say 'me.dirty = false' so that saves the record in the meal form, and then we're going to say 'meal list F.requery.' Save it like that. We might need to go to the record. Let's see.
If I come down here and just type in an exclamation point, hit Tab, all right, updated that up there. Now that I'm thinking about it, the tab order - I don't want it to jump in for the next record. We'll get to that in a minute. Yes, the tab order - we'll work on that.
If I add something new and I type in here 'xyz,' let's say, okay, it's up here. But we're going to want to seek and find that too, so at least our focus is sitting on that guy.
Let's first set that cycle, because I don't like when you tab tab tab and it cycles to a different record. I'm going to come in here real quick, go to this guy, and set Cycle to stay on the current record. If you're going to leave the record, I don't want to tab to the next record. So for down here, it'll tab through these items and it'll stick right there. This one won't tab forward or backward to a different record. I don't like that.
And while we're at it, let's make sure this guy does the same thing. Put it down here. Okay, it stays there. Good. Good. Good. And this should just stay up in here. I want the forms to at least try to behave somewhat similar to each other.
All right, so that requery when we added this guy... Let me delete this guy for this again. All right, let me add a new one. Instead of a full requery, let's try a recordset requery. We can't go recordset.requery here. If you debug and compile, it should get there. Yep, see, so the trick is here, always remember it's got to go .form.recordset.requery. That should work.
Again, I have whole different videos on recordset requery. I think I pointed you to them in a previous video. Now, let's see. If I type in 'xyz,' okay, it shows up there without a full requery.
That's important. You'll see why in a second. What we need to do next, though - this list is going to get big and long.
What I want to do is, when this is edited or updated, I want to find and sit on that record up there using the same recordset clone technique we just did a minute ago. It's very similar.
Seek to the record, or whatever you want to call it. Same thing; it's just a little bit different. Dim RS as a recordset. Set RS = meal list F.form.recordsetclone. There it is.
Now I've got a recordset clone pointing to that list up top, which at this point should be in there because we requeriеd it. Remember, this query is an aggregate query. It had to refresh itself, basically. Not 'refresh,' because Refresh is actually an Access term.
Now we do the same thing: RS.findfirst where meal ID equals the meal ID we're on, this guy. Find the record up here that we're on down here. If RS.nomatch, then status: record not found.
Else, now we have to move the bookmark to the record we just found: meal list F.form.bookmark = RS.bookmark. Basically, move the cursor - the selected record - to the bookmark we just found in the recordset clone.
RS.close, set RS = nothing. I know that's one, two, three... ten lines of code, but it will make your database much better.
So now, if I'm up here, if I come down here, if I do something like this, Tab, look, it stays on that record up there. If I add a new item, if I add, let's say, 'coffee,' 'Joe's,' Tab, look at that. Oh, it's slightly off. It's up here instead of down here. We have to fix that.
I'm glad that came up, because that reminds me, sometimes you can't get away with the recordset requery like that, because when you add records, it changes the structure of what's in that recordset. So, we actually have to take a slightly different approach. I'm glad this came up. I haven't had to deal with this problem in a long time.
We have to tackle this a slightly different way. We actually are going to me.dirty = false, then remember what meal ID we're on, then properly requery the list, then seek to it. It's a slight modification, because this doesn't know that we've added or changed a record. Changing records is fine, but it doesn't know that we've added a record to that recordset.
So what we're going to do is up here, we're just going to dim tempID as Long. Me.dirty = false saves the record in the table. Now, we're going to say tempID = mealID, whatever meal ID we're currently on. Now give it a proper requery (funny, that's what we started with). It's going to requery the list.
Now that new item will show up in there properly, and all we have to do is down here, say tempID - in other words, go to that one that we saved up top here.
Save it, debug, compile. Let's see if memory serves. I haven't run through this yet. So I'm going based on old memories. I probably should have run through this first. Let's delete this one, and let's delete Joe's coffee. We're going to make our own delete buttons to handle this stuff, too, because I don't like that we have to hit F5 to refresh the whole thing.
Now, let's add - oops, wrong add - let's add 'ZZZZZZZZZZZE.' Now that's fine, says at the end. Let's add another one: 'Joe's coffee,' and boom, we're right on it. See? Because we had to requery the list, so it's up to date, then we can go to it.
Let's try adding something else. Let's add... I'm just going to try to sneak something in between cereal and coffee. Let's add 'CFFFFF.' Boom, it puts it right in that spot. Beautiful. That was a tricky one.
But it just goes to show, sometimes you have to save your variable, then you can requery the list, then you can go in here and blah, blah, blah. You might be able to do it without that temp variable. I'm kind of tempted now, because we're requering that subform up here. It might work.
Let's try it without doing this. Let's just put mealID back down here. I just want to see if it works. It might work without the variable.
Oops, someone's beaming in.
All right, let's try it again. Let's try putting in 'CZZZZZ.'
Nope, see, because when we do the requery, this guy's got an event in it. We have an event in here. So this thing requeries, it puts the record back at the top, and then that changes what's here. That's why we need that tempID.So it all came back to me now. So we do need this. You see why? Save the record. Remember the ID because as soon as we do this, this thing requeries. But when this requeries, this goes back to the first record and we have an event in here in the on current event that brings this to whatever that was.
Once that happens, then we need to immediately go back to the original. That's why we have to use a variable.
We're going to have to worry about deleting stuff, which we'll do in the next class. We got the Joe's Coffee can get deleted and Z, Z, Z deleted. When we use our own delete button, we will then requery that and everything is coming along nicely.
Lots of little stuff.
Did you learn something? Is this fun? Are you enjoying this? Give me a comment down below.
That's going to do it for part 18. Hope you learned something. Live long and prosper, my friends. See you tomorrow for part 19.
TOPICS:
Replacing filtering with recordset-based navigation
Using recordset clones in forms
Seeking and setting the current record with bookmarks
Handling RS.nomatch to display status messages
Cleaning up recordsets by closing and releasing
Fixing errors with null values using Nz function
Synchronizing additions in subforms with meal list form
Triggering a requery to update related forms
Setting the Cycle property to stay on current record
Using recordset requery to refresh subform data
Selecting newly added records using recordset clone
Storing and using temporary variables for record navigation
Resolving issues when adding new records and keeping focus
Explaining why tempID is necessary for proper navigation
COMMERCIAL:
In today's video, we're continuing with part 18 of our fitness database series. I'll show you how to upgrade your meal list form to use recordset clones instead of filters, allowing users to browse and navigate records smoothly just like in the food list. You'll learn how to use the FindFirst method to locate records, handle potential no-match errors safely, and keep your forms synchronized by updating bookmarks and refreshing recordsets when new records are added or edited. I will also explain how to manage form focus and cycle property for a more user-friendly experience, tackle issues with null values using the Nz function, and discuss why using a temporary variable is necessary after requerying the list. If you want your Access database to be easier and more efficient for users, you will not want to miss this one. 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 advantage of using a recordset clone instead of filtering in the context described in the video?
A. It allows the user to browse and use navigation buttons without restrictions.
B. It automatically sorts all records alphabetically.
C. It improves the database security by hiding sensitive data.
D. It prevents any accidental changes to the data.
Q2. How does the parent.bookmark = RS.bookmark statement help in the form?
A. It sets the current record in the parent form to the found record in the recordset.
B. It copies all fields from the recordset to the parent form.
C. It deletes the current record from the parent form.
D. It sorts the parent form's records alphabetically.
Q3. What should you do after opening a recordset and finishing with it?
A. Close the recordset and set it to nothing.
B. Save the recordset to a backup file.
C. Refresh the entire application.
D. Export the recordset to Excel.
Q4. What Access function was suggested to prevent errors when setting selstart for a potentially null Description field?
A. Nz(description)
B. Len(description)
C. Trim(description)
D. Fix(description)
Q5. When adding a new record and needing to highlight it in the form after a requery, why is it important to use a temporary variable like tempID?
A. Requerying can reset the current record, so tempID lets you restore focus to the new or edited record.
B. TempID automatically sorts the list alphabetically.
C. It allows you to delete the record right after adding it.
D. TempID prevents duplicates from appearing in the form.
Q6. What happens if you try to programmatically cycle through records (using the Tab key) but do not set the Cycle property to "stay on current record"?
A. Tabbing will move to the next or previous record unintentionally.
B. The form will close unexpectedly.
C. The current record will be deleted automatically.
D. No records can be edited.
Q7. Why must RS.findfirst be used after requering the list when adding a new record?
A. Requery can reset the recordset, so you need to find and select the new record.
B. It is required to duplicate the data for reporting.
C. It automatically deletes any unused records.
D. It locks the form for editing by others.
Q8. Why is it not sufficient to just use mealID after a requery, without storing it in a tempID variable?
A. Because the requery can change the current context, and without tempID, you may not be able to restore focus to the right record.
B. Because mealID is always null after a requery.
C. Because tempID encrypts the ID for security.
D. Because Access requires all IDs to be global variables.
Q9. Why did the instructor prefer writing "if RS.nomatch" rather than "if not RS.nomatch" in his code?
A. To keep the code easy to read and understand.
B. To increase the performance of the database.
C. Because Access does not allow double negatives.
D. To avoid having to declare extra variables.
Q10. What is the benefit of using your own delete button and handling the requery after deletion?
A. It allows you to control when the form updates and ensures the form reflects current data.
B. It immediately exports the deleted data to a backup file.
C. It prevents any accidental data entry by the user.
D. It sends an automatic email alert on deletion.
Answers: 1-A; 2-A; 3-A; 4-A; 5-A; 6-A; 7-A; 8-A; 9-A; 10-A
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 our ongoing exploration of building a fitness database in Microsoft Access. This lesson is part 18 in the series, so if you have not yet seen parts 1 through 17, I strongly recommend going through them first. Even if your application is not specifically fitness-related, these Access techniques are broadly useful and applicable in any database you might construct.
Picking up where we left off previously, we now have our meal list combined into one form. I want this meal list form to offer a similar user experience to our food list form, where it is possible to browse all available items, regardless of filtering. Currently, the meal list is stuck in a filtered view, so users cannot navigate other items conveniently. Instead of relying solely on filtering, we can use a recordset, just as we did back in lesson 10.
By setting up event handling so that when a specific item is selected, the code searches for that record within a recordset clone and sets the form's bookmark accordingly, we allow for more advanced navigation. This means users will be able to use the form's navigation buttons to move back and forth through records, which is preferable to the locked-down, filtered approach.
To accomplish this, we need to make changes in the form's On Current event. I often initially implement a straightforward solution, just to make things work, but later revisit my code to optimize and refine it for better performance and usability.
To get started, I stop filtering and instead create a recordset variable. I have not published a video focused exclusively on recordset clones yet, but I do cover the concept in many other lessons. A recordset clone in Access is essentially a copy of the form's underlying recordset that can be used to search, manipulate, and navigate records without disturbing what the user sees.
In this case, we use a FindFirst method on the recordset clone to jump to the record where the MealID matches the selected item. If, for some reason, no matching record is found, we display a status message in red to indicate that the record is not present. This scenario should almost never occur, but in a multi-user environment, records could potentially be deleted in another form during use. Writing the logic this way keeps the code clean and understandable.
If a match is found, we set the parent form's bookmark to the bookmark found in the cloned recordset, effectively moving the current record pointer to the right place. After that, it's good practice to close the recordset and clear the object variable to maintain proper memory management.
Next, I discuss user interface behavior regarding focus. When the user selects a record up top, focus may jump to the subform below. Since the upper part is not meant for editing, I prefer this behavior, as any edits should be done in the detail area below.
Another issue that popped up previously was an error when navigating to a new record, receiving an 'invalid use of null' message. This happens when code tries to set the starting position of a text selection but the relevant field is null. To resolve this, we check for nulls using Nz or an IsNull logic, which safely handles new, empty records.
Requerying and synchronization are important here as well. When a new record is added in the subform, I want to ensure the main list refreshes accordingly. Thus, after saving the record, I trigger a requery on the meal list form so that all new changes are displayed. However, requerying can sometimes move focus or change the navigation context, so handling these transitions carefully is important for a good user experience.
Along the way, I address tab order and cycling. I don't want tabbing through a subform to move the cursor out to different records. Setting the Cycle property to stay on the current record ensures that tabbing behaves consistently and doesn't inadvertently jump to a new record.
Another refinement is seeking and maintaining focus on a newly added or updated record. This uses a recordset clone to find the relevant item in the list and set the bookmark as before. When simply updating a record, this method works fine. However, when adding a new record, particularly into a sorted or filtered list, the recordset's contents and order can change. This makes it necessary to store the MealID of the newly added record before the list is requeried, and then seek out that MealID again afterward to ensure the focus is on the correct new item.
We do all of this by saving the important ID into a temporary variable, saving the record, performing the requery so the new item is guaranteed to appear, and then using the variable to find and restore focus. Leaving out the temporary variable can result in the record pointer getting reset to the top or incorrect items being selected, since the requery events trigger code that changes the current selection.
If you're wondering about delete operations, that topic will be addressed in the next lesson. We'll create our own custom delete buttons for more precise control, rather than relying on the default Access behavior, which sometimes necessitates a full manual refresh.
These refinements might seem minor, but they add up to a much smoother and more professional application. Writing clean, efficient, and predictable code is essential for usability, especially as your database grows more complex.
That brings this lesson to a close. I hope you picked up some useful tips and are enjoying the series so far. 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
Replacing filtering with recordset-based navigation
Using recordset clones in forms
Seeking and setting the current record with bookmarks
Handling RS.nomatch to display status messages
Cleaning up recordsets by closing and releasing
Fixing errors with null values using Nz function
Synchronizing additions in subforms with meal list form
Triggering a requery to update related forms
Setting the Cycle property to stay on current record
Using recordset requery to refresh subform data
Selecting newly added records using recordset clone
Storing and using temporary variables for record navigation
Resolving issues when adding new records and keeping focus
Explaining why tempID is necessary for proper navigation
|
