RecordsetClone
By Richard Rost
9 months ago
RecordsetClone v FindRecord: Best Way to Find Records
In this Microsoft Access tutorial, I will show you how to use the RecordsetClone property in your forms to search for, find, and navigate to specific records using bookmarks in VBA. We will compare this method to the older GoToControl and FindRecord approach, highlight the advantages of using RecordsetClone for precise, background data operations, and cover tips for working with filtered forms without altering the user interface. This tutorial is intended for developers with some VBA experience.
Members
In the extended cut, we will learn how to loop through records that are visible in the form after filters are applied, update multiple records at once (multi-record updates), and use a button to mark filtered records as active or inactive. I will show you how to process filtered records using the Recordset Clone and perform batch operations much faster and easier than the traditional methods.
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
Recommended Courses
Keywords
TechHelp Access, Recordset Clone, VBA, form record navigation, bookmark, DAO, record filtering, FindFirst, looping records, batch updates, aggregates, background validation, multi-record updates, custom navigation buttons, filtered records, export preparation, temp tables
Subscribe to RecordsetClone
Get notifications when this page is updated
Intro In this video, we talk about the Recordset Clone feature in Microsoft Access and how developers can use it to work with the data behind a form without affecting what the user sees. We'll look at what a Recordset Clone is, the benefits of using it over DoCmd.FindRecord or other UI-driven methods, how to sync the form to a specific record using bookmarks, and step through the VBA code needed to implement it. We'll also cover error handling if a record isn't found, navigating filtered data, and some real-world examples where Recordset Clones offer speed and flexibility for background operations.Transcript Welcome to another TechHelp video brought to you by AccessLearningZone.com. I'm your instructor Richard Rost.
Today's a more advanced video for the developers. We're going to talk about Recordset Clone. No, not clown - clone - Recordset Clone. This is a cool feature in Access where you can play around with the data under a form without actually having to integrate and use the form's controls and stuff. I'll explain it better in just a minute.
All right, so what is a Recordset Clone? Well, a Recordset Clone is basically a copy of the same data that your form is already sitting on. Now, it's not the same as the table or query that the form is based on, but it's literally what data is in the form, because you can apply filters and stuff like that which will change the Recordset Clone.
So it's essentially all of the data that's in your form. Think of it like another layer below it.
Now, when you open a form in Access the form itself is bound to a recordset behind the scenes. The clone gives you another pointer to the same data without disturbing what the user sees on the screen.
That's not a new query or a new dataset. It's just a parallel pointer or a handle into the same data.
Now, the big advantage is you can move around, search, or loop through records in the clone without kicking the form's cursor around, so you're not moving around on the screen. I've showed a lot of videos where you can do things like 'go to control,' 'go to record,' and then all moves around on the screen and it's slow and error-prone.
With a Recordset Clone, you can do that moving around in the data and do stuff and even change stuff. And if you're, let's say, looking for a record, once you find it then you can sync the form to the record that you're on using the bookmark.
It's like you bookmark this and say 'bookmark Eddie Van Halen,' and now you can move this form's bookmark to the same thing that you found in here.
This makes Recordset Clones especially useful for things like finding records, but you can also count or sum records, do background checks and updates, and all kinds of stuff.
I'm going to give you a list of things you can do a little later on.
So in short, a Recordset Clone is like a backstage pass to the form's data. You get to look around, manipulate it quietly, and then decide if and when you want to pull the user on the form along with you into the Recordset Clone.
All right, let's take a look at an example, but first a couple of prerequisites.
Of course, this is a developer level video, so you'll need to know some VBA. If you've never watched any VBA lessons before, watch this one - it'll get you started.
If you don't know what a recordset is, go watch this first. A recordset and a Recordset Clone are very, very similar. But a Recordset Clone essentially is a recordset that's attached to a form, basically in a nutshell. But it helps to learn recordsets first because you can set up your own recordsets which just exist in memory, and you can loop through them and change records and do stuff.
So go watch the recordsets video first if you haven't seen this yet, and then come back and learn about Recordset Clones.
Recordset Clone is saying basically 'clone the recordset that's behind this form.' That's why they call it a clone.
It's like that Star Trek episode with The Next Generation where the Enterprise goes to the planet and they're all clones and it's like clones, clones, clones.
And if you're not familiar with the other method that I show - how to open a form and find a record - go watch this video because instead of using a filter or a where condition, you might want to open the form and still allow the user to use the navigation buttons and move between the records.
You want to open up to a specific record and display that one, but still have the rest of the records there.
To do this we open the form, we go to control, and then we find the record, which moves around in the user interface and it's a little clunky and it's actually prone to some errors. We'll talk more about this in a bit.
But if you want to learn this, because sometimes it's better to learn the not-so-great way first so that you appreciate why this better method is better. That's why I always teach this other method first and now I can show you the better method. It's a little more complicated. It's a couple more lines of code, but it's a better solution.
All right, so go watch all those prerequisite videos first. They're free. They're on my YouTube channel, they're on my website. Go watch those and come on back.
All right, here I am in my TechHelp free template. This is a free database you can grab off my website if you want a copy.
And in here I've got a customer list, and in the customer list you can double click on one of these guys and it opens up the customer form. There's the individual customer form. Now notice it's filtered.
Yeah, you could turn off the filter, but then it puts you back on record one, so that's not always desirable and sometimes you want to go right to that record.
So what do we do here? Well, in this guy, in the On Double Click event right there, we've got - let me resize this real quick, sometimes when I'm playing around off camera I move things around - we've got an OpenCustomer sub that we built. It just checks to see if the CustomerID is null and then it does an OpenForm CustomerF, comma, comma, comma, chameleon, where the CustomerID is equal to the CustomerID on that list form. And what that does is it opens it up, but the where condition puts a filter on it.
Now, if you don't want to do that, if you want to get rid of that filter and still allow the user to jump around, you can do the other method that I show in the OpenForm Unfiltered video, which basically is then once you've got the form open, do DoCmd.GoToControl the CustomerID, and this assumes CustomerID is visible on the form because I don't always put the IDs visible in a finished database.
But once you're on that, then you can use a search. You can do DoCmd.FindRecord and then just CustomerID for that and then all the other default options are fine for that search.
Save it, and now when I do this, it does open it, but it involves GoToControl and it's prone to problems. We'll talk more specifics about the problems later.
But here's the better way to do it. The better way to do it is to open the form and then create a Recordset Clone of the form, which is basically down here in memory. Find the record and then match the bookmarks.
Here's how that works. Are you ready? A little more advanced.
Now, these two lines up top are still the same. We're still going to check for a null CustomerID. We're still going to open up the form. Now next we're going to dim rs as a recordset. We're going to set rs = Forms!CustomerF.RecordsetClone.
That makes a Recordset Clone which is pointing to the records in that form.
Now in the recordset we're going to say rs.FindFirst and then this will look like an actual where condition, so it will be CustomerID equals CustomerID on this customer list form that we're on. Remember, right now we're on this customer list form.
So now we've found - well, hopefully we've found that record. It should exist because if it's in the list it should exist in the other one, but you never know, especially in a multi-user database. Things can happen and it might disappear at the last minute.
So next you want to check to see if you've actually found it. If rs.NoMatch, not NoMatch, then we haven't found it. MessageBox "Customer not found". And here you can close the customer form if you want to or you could go to a new record if it doesn't exist, whatever you want to do. I'll just close the form: DoCmd.Close acForm, "CustomerF".
Else this means we've found it, so now we're going to match up the bookmarks. We're going to say, okay, you found a customer in the Recordset Clone, so your bookmark, your cursor in the Recordset Clone is on, let's say, record seven.
Now I want to take the form that I've got and match that bookmark. It'll basically move the cursor - it'll move the record you're on - to the whatever's bookmark.
So Forms!CustomerF.Bookmark - bookmark basically means the record that you're on - equals rs.Bookmark.
At this moment here when the find is successful, you've found it, so now it's moved the focus to that record.
That's what your bookmark is. Bookmark is whatever page you're on in a book, right? Same thing with recordsets. It's whatever record you happen to be sitting on when you're doing searches, moving around, that kind of stuff.
So now all we're going to say is, okay, I want the form's bookmark to be equal to the recordset's bookmark - just move us to that page.
And then of course some cleanup: rs.Close, Set rs = Nothing. If you set it, you got to forget it.
Save it, debug, compile once in a while, close it, open it, and let's open up Jean-Luc. Ready? Boom, there it is. And we're on four of 33.
We didn't do any DoCmd.GoToControl nonsense because you're moving around in the user interface when you do that. GoToControl, GoToRecord, those kinds of commands, those are okay for beginner programmers, and believe me, I used them a lot when I was getting started. I still have some of that in my database. I'm slowly working on getting rid of all of it.
But I'm always very much so 'if it ain't broke, don't fix it.' So if it's working I kind of tend to leave it although sometimes, once in a while, it does break because if you've got code heavily reliant on looping and stuff through the user interface, moving through fields in a form and stuff, if the user happens to click while that's happening, it can mess everything up.
So this is why I try to prefer stuff like this that doesn't involve the user interface. The magic happens in the background and then, bam, at the last minute you just move to where it is.
Now, some other benefits of using a Recordset Clone.
So, major advantages of the Recordset Clone: It's direct, it's not user interface driven, you're not working with the form, you're working with the form's data engine underneath it.
Instead of simulating keystrokes or moving to different controls, moving to different records, all that stuff, you get precise targeting. You use an exact where clause instead of relying on search settings.
It's silent. There's no flicker and no pop-ups, no cursor jumps, no moving around on the screen. It's just instant - it goes right to the record you want.
It works in more contexts. It's usable in forms. You can do some stuff with Recordset Clones in reports, and even background Recordset operations. There's much you can do with it.
It's faster, especially with very, very large data sets. Since it skips the UI navigation, it searches the data directly, whereas FindRecord sometimes has to walk through the user interface.
Partial matches, format, and can break searches. Let's say you've got to find record in a form, and then later on you change the format of the form, or you put an input mask on it. Well, that's going to break your search right there, whereas Recordset Clone doesn't care how the field is formatted on the form.It's what's in the table. The right method gives you professional polish, gives your apps stability, and avoids macro-like quirks that can happen. For example, FindRecord can flicker, shift focus, and highlight text unexpectedly. User clicks can mess it up, and it's not as robust.
DAO (Data Access Objects), like Recordset Clone, is another word for it. It works even when the form is hidden or running in the background.
Some other things you can do with it: counting records without changing the user's position. You can check how many records the form currently has, even with filters applied, without moving the form's pointer. And yes, there are other ways to do that, like a Count function or DCount, but again, if the Recordset Clone already exists, you can access the Recordset Clone Count property, and it's much, much faster.
You can loop through filtered records and process only what the form is showing, leaving the form itself untouched. I'm going to go over this in the extended cut with the members. You can actually loop through the records that are visible, even after the user has applied multiple filters. You don't have to try to figure out what those filters are; you just access the Recordset Clone.
So, if they've taken a list of customers where the first letter has to be a G, it's from New York State, and this filter and that filter, and now you want to mark all those people active - boom, it's done. You don't have to go and try to figure out what the filter is, what the order, or the SQL statement was that the user created the form with. It doesn't matter. That's handy. I use that one all the time myself.
Aggregations: once you've got the Recordset Clone built, you can Sum, Average, Max, Min, all that stuff. As we know, domain functions are slow. For example, DLookup, DCount, DMax, DMin, all those are really slow. So, once you have the Recordset Clone already built, you can aggregate those fields.
Finding duplicates or related records is easy. You can build custom navigation buttons. You can loop through the records and make your own Next and Previous buttons. Instead of using the form to move back and forth, you move back and forth through the Recordset Clone and then set the bookmark. There are actually some benefits to doing it that way.
You can do background validation to check if values exist before you allow a save. And yes, you could do some of that stuff with just the regular form too, but again, you have to use domain functions at the time.
Multi-record updates. This is what we're going to cover in the extended cut too. You can do some export or reporting preparation. You can walk the clone - walking the clone basically means start at the beginning and then walk through all the records, moving next to build a collection, an array, a custom data set, or a temporary table. There are all kinds of things you can do.
Another trick I like is you can set up multiple bookmarks, like in a memory array, and then you can return to those bookmarks later. You can do batch deletes. That's always fun. We try not to delete records, but sometimes, especially if you're dealing with temporary data, it's okay to delete that.
As I mentioned, in the extended cut we're going to do just that. We're going to loop through whatever's in the form, the Recordset Clone. We're going to make a button that says, make everybody visible active.
So you can filter by name, by ID, set the status, filter New York customers, set the credit limit under whatever, and then hit Go, and it just marks all of the guys with that filter. Whatever filter you set up, mark all those people active or inactive or whatever you want to do to it.
It's so much better than the old-school way, which is to go to the first record, set it active, go to the next record, set it active, and repeat. We do it on the Recordset Clone, and it's so much faster and easier.
So that's what a Recordset Clone is. This is a more advanced topic. I am going to be covering this in a lot more detail in my Access Developer course. We're in Developer 52 or 53 right now, and we're working on class modules. But when I'm done with that, we're going to spend some more time on Recordset Clones because there are all kinds of cool things you can do with them.
That's going to do it for your TechHelp video for today. I hope you learned something. Live long and prosper, my friends.
I will see you next time, and members, I will see you in the extended cut.
TOPICS:
Explanation of Recordset Clone concept
Differences between Recordset Clone and table/query
Use of Recordset Clone to find records without UI movement
Syncing form to found record using Bookmark
Step-by-step VBA code to use Recordset Clone
Error handling when a record is not found
How to match form Bookmark to Recordset Clone Bookmark
Advantages of Recordset Clone over DoCmd.FindRecord
Benefits of Recordset Clone for professional app polish
Using Recordset Clone for counting records with filters
Using Recordset Clone to avoid UI-based navigation
Performance benefits with large datasets
Handling filtered records with Recordset Clone
Using Recordset Clone for aggregations like Sum and Count
Creating custom navigation with Recordset Clone
Using Recordset Clone for background validation
Setting multiple Bookmarks and returning to them later
COMMERCIAL:
In today's video, we're learning about how to use the Recordset Clone feature in Microsoft Access. You'll see how Recordset Clones let you work with the same data your form is using, but behind the scenes and without messing with what the user sees on screen. We'll compare the traditional method of moving around records with things like DoCmd.GoToControl and DoCmd.FindRecord, and then show you how Recordset Clones make finding, counting, updating, and aggregating your filtered data faster and more reliable. If you want to learn how to search through records, set bookmarks, or do background operations using VBA, this video covers all that and more. 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 a Recordset Clone in Microsoft Access?
A. A copy of the current data in a form, allowing manipulation without changing the user interface
B. A duplicate table stored in the backend database
C. A new query created every time you open a form
D. A backup of the database for data recovery purposes
Q2. Which of the following is TRUE about how a Recordset Clone interacts with the form's data?
A. It creates a parallel pointer to the same filtered data set as the form
B. It always creates a new unfiltered table
C. It automatically synchronizes every movement with the form's visible record
D. It includes all data in the underlying table, ignoring any filters on the form
Q3. What major benefit does using a Recordset Clone provide compared to looping through records with form controls and navigation?
A. It allows data manipulation in the background without UI flicker or cursor jumps
B. It automatically updates the design of the form
C. It requires less code than using DAO
D. It restricts the user from applying filters
Q4. What is the correct way to synchronize the active record on a form with the record you have found in a Recordset Clone?
A. Use Forms!FormName.Bookmark = rs.Bookmark after a successful find operation
B. Use DoCmd.GoToRecord on the form to match the recordset position
C. Use FindRecord on the form followed by GoToControl
D. Set the RecordSource property of the form to the found record
Q5. Which of the following describes a Bookmark in the context of Recordset Clones?
A. A reference to a specific record's position in the recordset
B. The primary key value of a record
C. An indicator of the last record viewed by the user
D. The filter expression applied to the form
Q6. Why is using the Recordset Clone approach preferable to methods like DoCmd.GoToControl or FindRecord for finding and positioning on a record?
A. It works silently without disturbing the user interface and is less error-prone
B. It provides a graphical way to move between records
C. It temporarily hides fields not needed for the search
D. It is required in all forms for Access to work
Q7. Which of the following are possible uses of Recordset Clones as described in the video? (Choose the BEST answer)
A. Counting filtered records, looping through visible records, and performing batch updates
B. Compiling database objects, exporting user forms, and resizing tables
C. Editing form controls, modifying table relationships, and exporting macros
D. Creating new queries, updating the database schema, and managing users
Q8. What is a typical prerequisite for understanding and implementing Recordset Clone functionality in Access?
A. Basic knowledge of VBA and understanding of recordsets
B. Knowledge of SQL Server Management Studio
C. Experience with Access macros only, without VBA
D. Using only the Access query designer, not code
Q9. How does Recordset Clone handle form filters?
A. It reflects all filters currently active on the form
B. It ignores form filters and always returns all records
C. It duplicates both the form and its controls for filtered data
D. It only works if there are no filters on the form
Q10. What advantage does using a Recordset Clone have when dealing with large datasets?
A. Faster searching and looping since it bypasses UI navigation and deals directly with the data
B. It compresses the table automatically
C. It improves form design performance
D. It prevents users from entering new records
Q11. If a record cannot be found in the Recordset Clone during a search operation, what is a recommended response?
A. Display a message to the user and optionally close or reset the form
B. Automatically add the missing record to the table
C. Ignore the issue and continue processing
D. Delete all records from the form
Q12. What is meant by 'walking the clone' as used in the video?
A. Iterating through each record in the Recordset Clone to build collections or perform operations
B. Moving through each form control one by one
C. Cloning macros for each user input
D. Converting recordsets into form controls
Q13. What is a limitation of using DoCmd.GoToControl and FindRecord compared to Recordset Clone?
A. They are prone to interface flicker and may be disrupted by user actions during execution
B. They can only be used once per form
C. They support only unfiltered forms
D. They are required for all forms
Q14. Why might you choose Recordset Clone for batch updates or aggregations over domain functions like DCount or DSum?
A. Recordset Clone is generally much faster than domain functions, especially when the clone is already built
B. Domain functions are required before Recordset Clones can be used
C. Recordset Clone cannot access filtered data
D. Domain functions work only on macros
Q15. Which statement about using a Recordset Clone to build custom navigation buttons is correct?
A. You can move through the clone and set the form's bookmark to create your own navigation logic
B. It forces the form into read-only mode
C. It removes the user's ability to apply filters
D. It always resizes the form automatically
Answers: 1-A; 2-A; 3-A; 4-A; 5-A; 6-A; 7-A; 8-A; 9-A; 10-A; 11-A; 12-A; 13-A; 14-A; 15-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 focuses on a topic for more advanced Access developers: the Recordset Clone. This is a powerful feature in Microsoft Access that allows you to manipulate and explore the underlying data source of a form without directly interacting with the form's controls or user interface.
Let me explain what the Recordset Clone is. When you open a form in Access, that form is attached to a recordset behind the scenes. The Recordset Clone provides another way to access that same set of data, but independently of the copy that the form itself is working with. This is not the same as making a new copy of the table or query, but instead gives you a new pointer to whatever records the form currently has loaded. If the form has filters applied, the Recordset Clone sees only that filtered data. In essence, it is a parallel window into the data visible to the form, allowing you to move through records, search, or process data in the background, all without altering what the user sees.
One of the biggest advantages of the Recordset Clone is that you can perform operations quietly in the background. You might want to search for a record, count records, summarize data, or update specific records, and you can do these tasks on the clone so that the user's experience on the form is not disrupted. It keeps the user interface stable and smooth, and, for example, avoids the cursor jumping around or fields being highlighted, which can happen when you use methods like GoToControl or FindRecord.
There are a few things you need to know before working with the Recordset Clone. This is definitely a developer-level topic, so some familiarity with VBA is required. If you have not worked with VBA before or do not know what a recordset is, I recommend watching my introductory videos on recordsets first. You will want to understand how recordsets work in general before dealing with those attached to forms.
Now, let me describe a real-world use case to highlight the difference between the older method of finding records and the cleaner method using a Recordset Clone. In the traditional approach, you might open a form with a filter to immediately display a particular record. However, this could limit the user's navigation within the other records on that form. Alternatively, you might try methods like GoToControl and FindRecord to locate a particular record in an unfiltered form, but this approach manipulates the user interface, is slower, and can introduce errors, especially if the form's structure changes or records are not always visible.
Using a Recordset Clone provides a much better method. You open the form, create a Recordset Clone of that form's data, find the record you are interested in, and then synchronize the form's current position to match the one found in the clone. Here, bookmarks play a key role: once you locate the desired record in the clone, you set the form's bookmark equal to the clone's bookmark, and the form instantly jumps to that location. This process does not involve navigation commands or moving through the user interface; instead, it efficiently and accurately repositions the form in the background.
The benefits of using the Recordset Clone go beyond simply finding and displaying specific records. Here are several additional advantages:
- Precision: You can target records directly using exactly the match you need, rather than relying on user interface searches or navigation.
- Speed: Especially with larger datasets, operating on the data directly is much faster than moving through the UI.
- Stability: The form does not flicker, shift focus, or surprise the user with unexpected behavior.
- Independence from formatting: Unlike search commands, the Recordset Clone accesses values as they exist in the recordset, ignoring any formatting or input masks imposed by the form.
- Flexibility: This technique works in more contexts, including when the form is hidden, filtered, or even in background operations.
- Batch and background processing: You can count, sum, or process filtered records without losing the current record or having the form jump around. If you need to mark a group of filtered records as active, for example, you can loop through the Recordset Clone and make bulk updates.
Another powerful use is aggregation. Once you have created a Recordset Clone, you can calculate sums, averages, minimums, maximums, and more. While domain functions like DCount or DLookup are available, these functions are slower and less efficient than working directly with the recordset.
You can also use this approach to build custom navigation or validation logic, find duplicate records, perform multi-record updates, delete temporary data, and even track multiple bookmarks for advanced workflow scenarios. For example, you could filter a list of customers to certain criteria, run a batch update that marks all those customers as active, and do so efficiently in memory using the Recordset Clone.
In the extended cut of this lesson, I will cover looping through the filtered records in a Recordset Clone, performing group updates, and demonstrating even more advanced batch processing examples.
The Recordset Clone is a high-level feature that I cover in greater depth in my full Access Developer course, including how to integrate it into larger applications.
That will conclude this TechHelp tutorial. For a complete video with all of the step-by-step details and demonstrations, visit my website at the link below. Live long and prosper, my friends.Topic List Explanation of Recordset Clone concept
Differences between Recordset Clone and table/query
Use of Recordset Clone to find records without UI movement
Syncing form to found record using Bookmark
Step-by-step VBA code to use Recordset Clone
Error handling when a record is not found
How to match form Bookmark to Recordset Clone Bookmark
Advantages of Recordset Clone over DoCmd.FindRecord
Benefits of Recordset Clone for professional app polish
Using Recordset Clone for counting records with filters
Using Recordset Clone to avoid UI-based navigation
Performance benefits with large datasets
Handling filtered records with Recordset Clone
Using Recordset Clone for aggregations like Sum and Count
Creating custom navigation with Recordset Clone
Using Recordset Clone for background validation
Setting multiple Bookmarks and returning to them laterArticle If you build Microsoft Access applications, RecordsetClone is a powerful tool that lets you work with the data behind a form without driving the user interface. Think of it as a second handle on the same records your form is already using. It reflects the form's current filter and sort, it stays in sync with the form's data source, and you can search, loop, and even edit records through it without flicker, focus changes, or moving the cursor on screen. When you are ready, you can synchronize the form to a record you found in the clone by copying a bookmark. This approach is cleaner, faster, and more reliable than using commands like GoToControl or FindRecord.
A RecordsetClone is not a separate query or a new dataset. It is a DAO recordset that points at the form's current recordset. If the user filters the form, the clone sees the filtered set. If the user sorts, the clone sees the same order. You get direct access to the data layer while leaving the form's visual state alone until you decide to change it.
Here is a practical example. Suppose you have a customer list form that opens a single-customer form when you double-click a row. You want the single-customer form to open unfiltered but positioned on the selected customer. The RecordsetClone pattern does exactly that.
VBA example: open a form and move to a specific record using a clone and bookmarks
Option Compare Database
Option Explicit
Public Sub OpenCustomerAtID(ByVal CustID As Long)
On Error GoTo ErrHandler
DoCmd.OpenForm "CustomerF", acNormal
With Forms!CustomerF
Dim rs As DAO.Recordset
Set rs = .RecordsetClone
rs.FindFirst "CustomerID=" & CustID
If rs.NoMatch Then
rs.Close
Set rs = Nothing
DoCmd.Close acForm, "CustomerF"
MsgBox "Customer not found.", vbExclamation
Exit Sub
End If
.Bookmark = rs.Bookmark
rs.Close
Set rs = Nothing
End With
Exit Sub
ErrHandler:
MsgBox "Error: " & Err.Number & " - " & Err.Description, vbExclamation
End Sub
That code opens the form normally so the user can still navigate other records. It then creates a clone of the form's recordset, searches for a CustomerID, and if found, copies the bookmark from the clone to the form. Only at that final line does the form jump to the target record. There is no UI navigation, no GoToControl, and no dependence on control formats or masks.
FindFirst accepts a criteria string similar to a WHERE clause without the WHERE keyword. Use quotes for text and # for dates. Here are three quick patterns you will use a lot:
rs.FindFirst "CustomerID=" & someLongValue
rs.FindFirst "LastName='" & Replace(someText, "'", "''") & "'"
rs.FindFirst "OrderDate=#" & Format(someDate, "yyyy-mm-dd") & "#"
The ISO yyyy-mm-dd format works reliably for dates in Access criteria strings. Always escape embedded apostrophes in text by doubling them as shown with Replace.
Once you get comfortable with bookmarks, you can use the clone to build custom navigation too. A handy technique is to align the clone with the form's current record, move in the clone, then copy the bookmark back.
Private Sub cmdNextHighBalance_Click()
Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone
rs.Bookmark = Me.Bookmark
rs.FindNext "Balance > 1000"
If rs.NoMatch Then
MsgBox "No more matches."
Else
Me.Bookmark = rs.Bookmark
End If
rs.Close
Set rs = Nothing
End Sub
Because you are not driving the UI, this approach is less brittle than FindRecord. It does not care if controls are hidden, masked, or formatted, and it does not flicker or steal focus.
Counting and aggregating the records visible in a form is simple and fast with a clone. The clone already embodies the current filter, so you do not need to rebuild SQL or guess what the user did.
Function VisibleCount() As Long
Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone
If Not (rs.BOF And rs.EOF) Then rs.MoveLast
VisibleCount = rs.RecordCount
rs.Close
Set rs = Nothing
End Function
DAO returns the number of records that have been loaded, so moving to the last record ensures RecordCount is accurate for dynasets and snapshots. If you want a quick sum over the filtered set, iterate the clone.
Function SumVisibleCreditLimit() As Currency
Dim rs As DAO.Recordset
Dim total As Currency
Set rs = Me.RecordsetClone
If rs.BOF And rs.EOF Then
SumVisibleCreditLimit = 0
Else
rs.MoveFirst
Do While Not rs.EOF
total = total + Nz(rs!CreditLimit, 0)
rs.MoveNext
Loop
SumVisibleCreditLimit = total
End If
rs.Close
Set rs = Nothing
End Function
Because this works against the form's current recordset, it respects whatever filters the user has applied. It is typically faster and more predictable than repeated domain function calls against the underlying table.
One of the biggest wins is batch updates that target only the records the form is showing. You loop the clone, edit each record, and update. The form does not need to move, and the UI remains calm.
Public Sub MarkFilteredActive(ByVal MakeActive As Boolean)
On Error GoTo ErrHandler
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = Me.RecordsetClone
If rs.BOF And rs.EOF Then GoTo CleanExit
db.BeginTrans
rs.MoveFirst
Do While Not rs.EOF
If rs.Updatable Then
rs.Edit
rs!IsActive = MakeActive
rs.Update
End If
rs.MoveNext
Loop
db.CommitTrans
CleanExit:
rs.Close
Set rs = Nothing
Set db = Nothing
Me.Requery
Exit Sub
ErrHandler:
If Not db Is Nothing Then On Error Resume Next: db.Rollback
MsgBox "Batch update failed: " & Err.Number & " - " & Err.Description, vbExclamation
Resume CleanExit
End Sub
That routine touches only the records currently visible in the form. If the user filtered by state and minimum credit limit, those are the rows affected. You do not need to reconstruct the filter in SQL. Wrapping changes in a transaction makes the operation faster and keeps your data consistent if something goes wrong. If the form or its data source is read-only, Updatable will be False and edits will be skipped.
You can also use a clone when the form is hidden. For example, you can keep a form loaded in the background for caching or filtering, then run counts, sums, or checks against its clone. Because the clone is DAO, it works even when the form itself is not visible. This keeps background processes decoupled from the UI.
A few practical tips will help you avoid common pitfalls. Always declare your clones as DAO.Recordset to prevent confusion if both ADO and DAO libraries are referenced. Always close the recordset and set it to Nothing when you are done. If you need an accurate count, move to the last record before reading RecordCount. When building FindFirst criteria, use quotes for text and # for dates, and escape apostrophes in strings. Remember that the clone reflects filters and sorts that might change at runtime, so account for the possibility of zero rows. And when you sync via bookmarks, treat that as the single moment where the UI updates, which keeps your forms responsive and stable.
Once you start using RecordsetClone, you will find many ways it improves your code. You can find records without UI gymnastics, build smarter navigation, perform fast aggregations over exactly what the user is looking at, validate data before saves, walk the filtered set to prepare exports, or queue up bookmarks to jump among interesting rows. The key idea is simple. Work quietly in the clone, and only when you have what you need do you copy the bookmark to bring the form along. This keeps your apps fast, polished, and more robust. |
