Welcome

Welcome! Batch Recordset Clone & Search

S M L XL FS | Slo Reg Fast 2x | Bookmark

Welcome to Access Developer 59. In this lesson, we will learn what a Record Set Clone is in Microsoft Access, how it works behind the scenes, and why it is a valuable tool for developers. We will discuss searching and navigating records without interrupting the user interface, looping through filtered records, and building reusable VBA routines using Record Set Clone. We will also cover the prerequisites you should know before starting, such as DAO Record Sets, and give an overview of the lessons ahead, which will include search functions, batch processing, and Bookmark navigation.

Navigation

Prev: D59
Next: Lesson 1

Keywords

Access Developer, Recordset Clone, VBA Recordset, DAO Recordset, filtered records, batch processing, search function, navigation routines, Bookmark, Find Record, Find Next, Find First, batch updates, reusable code, helper functions, generic search

Subscribe to Welcome
Get notifications when this page is updated

Intro

Welcome to <B>Access Developer 59</B>. In this lesson, we will learn what a Record Set Clone is in Microsoft Access, how it works behind the scenes, and why it is a valuable tool for developers. We will discuss searching and navigating records without interrupting the user interface, looping through filtered records, and building reusable VBA routines using Record Set Clone. We will also cover the prerequisites you should know before starting, such as DAO Record Sets, and give an overview of the lessons ahead, which will include search functions, batch processing, and Bookmark navigation.

Transcript

Welcome to Microsoft Access Developer Level 59 brought to you by Access Learning Zone. I am your instructor, Richard Rost.

Today we are going to start our deep dive into Record Set Clone and learn how to use it to build cleaner, faster, and more reusable VBA code. You will see how to search and navigate records behind the scenes without disturbing the user interface, loop through filtered records for batch processing, and create generic helper functions that work across your entire database.

By the end of this level you will understand why Record Set Clone is one of the most useful tools available to an Access Developer and how to apply it in real world applications.

I recommend taking all of my developer classes in order since each level builds on the concepts in the previous ones. However, if you are jumping in specifically to learn about Record Set Clone, you should at least familiarize yourself with DAO Record Sets from Access Developer 16. That is the big one. That is the one that teaches you what Record Sets are, and it is kind of hard to do Record Set Clones if you do not know what a Record Set is in the first place.

Today's course assumes you already understand the basics of opening, navigating, and working with Record Sets in VBA so we can focus on the more advanced Record Set Clone techniques covered here. Like I always say, do not skip levels. They are designed to be one after the other, so if you are missing some stuff you might have to go back.

I will be using Microsoft 365. It is currently June of 2026, so the retail version is about 2024. However, all of this stuff with Record Set Clones and all that goes way back, I think to Access 2003. I think they had this stuff in it, but do not quote me. 2007 for sure. So, I recommend a Microsoft 365 subscription. If not, most of the modern versions should be fine.

If you have questions about today's class, just scroll down to the bottom of the page that you are watching on and post your question there. If you have general questions about anything else that has to do with Access, post it in the Access forum.

Alright, I will stick a closer look at what is covered in today's class.

In lesson one, we are going to learn what a Record Set Clone really is and how it works behind the scenes. We will compare it to techniques like Find Record, learn how Find Next, Find First, No Match, and Bookmark work together, and we will build cleaner search routines that let your code move around records without the form jumping all over the screen. That is the big part.

In lesson two, we will put Record Set Clone to work by looping through filtered records. You will learn how to count only the records currently visible to the user, perform batch updates on those filtered records, and build routines that automatically respect whatever filters the user has already applied without having to recreate those filters in code.

In lesson three, we will start building reusable developer tools by creating a generic Record Set Clone search function that works in any form. We will make it capable of finding records by numeric IDs, text values, and dates and compare Bookmark navigation with where conditions, laying the groundwork for handling more advanced data types in future lessons.

By the end of this level, you will have a solid understanding of Record Set Clone. You will know when to use it instead of Find Record or Open Record Set, and have a reusable search function and navigation routines that you can drop into virtually any Access application that you build.

As always, I recommend you watch each video once all the way through and then go back and watch it a second time while following along with the sample database that I have built in class.

Now, sit back, relax, and let us get ready to start lesson one.

Quiz

Q1. What is the primary benefit of using Record Set Clone in Microsoft Access VBA?
A. It allows you to work with records without affecting the user interface
B. It creates a backup copy of your database
C. It enables you to import data from Excel
D. It locks records to prevent user edits

Q2. To understand Record Set Clone, which prior knowledge is highly recommended?
A. Familiarity with SQL Server
B. Understanding DAO Record Sets and how to use them in VBA
C. Knowing how to design forms only
D. Experience with macros only

Q3. What does Record Set Clone allow you to do in relation to user-applied filters?
A. It only works on unfiltered records
B. It ignores user filters and processes all records
C. It processes only the currently filtered records visible to the user
D. It applies its own random filter

Q4. How does using Record Set Clone improve batch processing tasks?
A. It automatically exports records to external systems
B. It loops through filtered records efficiently without disturbing the form's position
C. It syncs all open forms with the main form
D. It encrypts data for security

Q5. Which of the following is a key difference between moving records with Record Set Clone versus using Find Record on a form?
A. Record Set Clone directly moves the user's form to the found record
B. Find Record works in the background without UI changes
C. Record Set Clone searches and processes records without affecting what the user sees on the form
D. Find Record works only with numeric fields

Q6. When building a reusable Record Set Clone function, which criteria should it be able to handle according to the lesson?
A. Only numeric IDs
B. Only text values
C. Numeric IDs, text values, and dates
D. Only alphabetical values

Q7. Why is it NOT recommended to skip earlier Developer Levels before taking this course?
A. Each level is unrelated to the others
B. Each level builds on the concepts in the previous ones
C. The topics change completely every level
D. Earlier levels only cover Macro programming

Q8. What traditional method does Record Set Clone replace or improve upon for form searches and navigation?
A. Append queries
B. Form-based Find Record routines
C. Importing external data
D. Table design

Q9. According to the video, for which versions of Microsoft Access are Record Set Clones available?
A. Only in the newest subscription versions
B. Only in very old versions
C. In most modern and some older versions
D. Not available in any version

Q10. What is a recommended way to learn most effectively from these video lessons?
A. Watch the video once then practice while watching a second time
B. Watch only the final lesson
C. Only read the transcripts without watching
D. Immediately try all features in a live production database without a sample

Answers: 1-A; 2-B; 3-C; 4-B; 5-C; 6-C; 7-B; 8-B; 9-C; 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 video from Access Learning Zone brings you Microsoft Access Developer Level 59, where I will teach you all about working with Record Set Clone in Access VBA. I am Richard Rost, your instructor.

In this lesson, I will walk you through what makes Record Set Clone such an essential tool for Access developers. You will learn how it lets you write cleaner, faster, and much more reusable VBA code. With this feature, you can search through records and move between them behind the scenes, without interfering with what the user sees on the form. Record Set Clone is perfect when you need to loop through only filtered records for things like batch processing or whenever you need to create generic helper routines that work anywhere in your database.

By the time you finish this class, you will understand why developers rely on Record Set Clone for so many real-world applications, and you will be able to implement it confidently in your own projects.

Now, if you are just starting out, I always suggest that you work through my developer classes in sequence. Each builds right on the last and the concepts make much more sense if you learn them in order. In particular, if you are coming here just to learn about Record Set Clone, you definitely should be familiar with DAO Record Sets first, which I cover in detail in Access Developer Level 16. That course is vital because you really cannot work with clones until you are comfortable with what Record Sets are in the first place.

This class assumes you already know how to open Record Sets, move through them, and work with data in VBA. Here, we are focusing on more advanced techniques made possible by using Record Set Clone. Like I always say, do not skip levels unless you want to get lost along the way.

I am teaching this with Microsoft 365. As of June 2026, the current retail version is about 2024, but everything with Record Set Clone has been in Access since at least 2003 or so. I recommend Microsoft 365, but most recent versions will be fine for everything we cover here.

If you have any questions about the topics in this lesson, just scroll down on the page and post your question. For general Access questions, use the Access forum.

Now, let me give you an overview of what you will learn today.

Lesson one covers the fundamentals of Record Set Clone. I will explain what it really is and how it operates under the hood. We will look at how it compares to Find Record, see how methods like Find Next, Find First, and No Match interact, and we will analyze the role of Bookmarks. You will see how to build more efficient search routines that can navigate through your data in code without the form itself jumping between records and disrupting the user's experience. That is a major benefit of using clones.

Lesson two focuses on applying Record Set Clone to work with filtered records. You will learn to count only the records that are currently visible to the user, perform batch updates on just those filtered items, and create routines that automatically honor any filters the user has already set up, so you do not need to duplicate those filters in your code.

In lesson three, I will show you how to build reusable developer tools using Record Set Clone. Together, we will create a generic search function that works on any form, whether you are searching by numeric value, text, or date. I will also compare navigation using Bookmarks to using Where conditions, setting you up to handle more complex data types in the future.

By the end of this level, you will have mastered the main uses of Record Set Clone. You will know when and why to use it instead of Find Record or opening a separate Record Set. Plus, you will have a reusable search function and navigation routines that you can plug into almost any Access application you are developing.

As always, I recommend watching each video lesson all the way through once, then go back and follow along with the sample database I have prepared so you can put everything into practice.

Alright, let's get started with lesson one.

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

What is a Record Set Clone and how it works
Comparing Record Set Clone to Find Record
Using Find Next, Find First, No Match, and Bookmark
Building cleaner search routines with Record Set Clone
Looping through filtered records using Record Set Clone
Counting only visible (filtered) records
Batch updating filtered records
Respecting form filters in VBA code
Creating reusable Record Set Clone search functions
Finding records by ID, text, or date
Differences between Bookmark navigation and where conditions
Building navigation routines with Record Set Clone