Welcome

Welcome! RecordsetClone Continued (Part 2)

Welcome to Access Developer 60. We will cover improvements to a reusable Recordset Clone search function, including reliable handling of text and numeric values with optional data types and enumerations. You will learn how to process only filtered, visible records, work with clones in subforms, compare form recordsets with Recordset Clone, and package reusable search and navigation code into a class module for use across multiple forms.

Navigation

Prev: D60
Next: Lesson 1

Keywords

Access Developer, Recordset Clone, RecordsetClone, FindFirst, FindNext, NoMatch, bookmarks, filtered record processing, reusable search function, VBA enum, optional data type parameter, subform record navigation, DAO recordset, class module, Clone Navigat

Subscribe to Welcome
Get notifications when this page is updated

More Information

Transcript

Welcome to Microsoft Access Developer Level 60, brought to you by Access Learning Zone. I'm your instructor, Richard Rost.

Today we are continuing our deep dive into Record Set Clone Parts 4 through 7. We're going to improve the reusable search function that we built in Developer 59, use Record Set Clone as a filtered selection list for processing visible records, work with clones inside subforms, and finally package our navigation code into a reusable class module.

Lots of cool stuff in this one. By the end of this level, you'll have a much better understanding of how Record Set Clone fits into a real-world Access application. You'll know how to handle different data types reliably, process only the records the user can currently see, navigate records in subforms without disturbing the interface, and reuse the same search functionality across multiple forms with only a couple of lines of code.

This class basically is part 2 of Record Set Clone, so it directly follows Access Developer 59. So make sure you watch that class first. That's where we covered the basics of Record Set Clone, including FindFirst, FindNext, NoMatch, bookmarks, filtered record processing, and our reusable GoToRecordByValue function.

And, of course, I recommend you take all my previous classes, not just because I'm trying to sell you more classes, but because every lesson is built on stuff we learn in the previous lessons. Especially Access Developer 16, where we learn recordsets. And in lesson 4 today, we're going to be building a class module, so you might want to review Access Developer 50 if you need to refresh here.

So that's why I always say I recommend taking all my classes in order. Don't skip levels. Where is it? There it is. Don't skip levels. I got a whole video on this page now about not skipping levels because it's just one after the other.

The lessons today are going to build on techniques we've developed throughout the entire course.

I will be using Microsoft 365. It's currently 2026, so the closest retail version would be Access 2024. Most of the Record Set Clone techniques in this class will work with much older versions of Access as well. I've been using this for decades, so it goes back.

If you got questions about a specific lesson, scroll down to the bottom of that lesson's page, the page you're watching the video on, and post your question there. Each of my videos now for classes has its own page, so you just scroll to the bottom and post it there. It makes it much easier for me to answer your questions and everybody else, too, because it's now on the page with the video. I don't have to ask you, "Which video are you talking about?" Just post it on that page.

And if you get general Microsoft Access questions that aren't pertaining to class, post them in the Access forum. That way they're public, everyone can see them, and lots more people have an opportunity to answer them and chime in, whereas the class page is only for people who have paid for that class or taken the class, so those are not public.

All right, let's take a closer look at what's covered in today's class, and let me know if you guys actually watch this part, because I know from the logs that a lot of people skip it. So post a comment down below right now. Let me know, do you actually watch the entire introductory video, or should I just shorten these up?

All right, in lesson one, Record Set Clone Part Four, we will improve the reusable search function that we built in Developer 59. Our automatic data type detection works well until we encounter text fields that contain only numbers, such as phone numbers or Social Security numbers. Access sees those values as numeric and builds the wrong criteria string.

So we'll fix that by adding an optional data type parameter and creating our own enumeration. You'll learn how enums work, how Access already uses them throughout VBA, and how they can make your own code easier to read and use with IntelliSense.

In lesson two, we'll use the clone as a filtered selection list. Instead of making the clone perform all of the work itself, we will use it to identify exactly which records the user can currently see. Then we'll let SQL or another recordset create related records, perform updates, or delete records.

We will then create follow-up records for visible customers, discuss safer ways to delete filtered records, that's always fun, and learn why separating record selection from the actual processing often produces cleaner and more reliable code.

In lesson three, we'll move beyond the main form and work with Record Set Clone inside subforms. You will learn the proper syntax for referencing the form contained inside a subform control, search for records within that subform, and use bookmarks to move it to the matching record.

We will also compare Me.Recordset with Me.RecordsetClone so you can see why looping through actual form recordsets moves the visible cursor, while the clone can perform the same work quickly behind the scenes. We'll also cover some common gotchas involving filters, sort order, unsaved new records, and the difference between DAO and ADO clones.

In lesson four, we're going to combine everything we've learned with the class modules that we covered back in Developer 50. I told you guys we'd get to some more stuff with class modules. They do have some cool uses.

We will take all of the Record Set Clone search and navigation code that we've been repeating and package it into a reusable Clone Navigator class. Once that class is built, you'll be able to add powerful search and navigation features to any bound form with only a few lines of code.

We will attach the class to a form, search numeric and text fields, and then reuse the same class on another form without copying all of the original Record Set Clone logic.

By the end of Developer 60, you'll have a flexible and reusable Record Set Clone system that can handle multiple data types, process filtered records, work with subforms, and be dropped into forms throughout your database using a class module.

As always, I recommend watching each lesson once all the way through, and then watch it a second time following along with the examples. This one especially, because we've got a lot of new concepts I'll be introducing. So watch the video once, get it right, make sure you understand it in your head conceptually.

Then follow along and build it with my sample database with me, and then if you need to go through it a third time to add this stuff to your database, do it then. Make sure you understand it with the sample database first.

All right, sit back, relax, grab your coffee, and let's get started with lesson one. Here we go.

Thanks for watching.

Intro

Welcome to <B>Access Developer 60</B>. We will cover improvements to a reusable Recordset Clone search function, including reliable handling of text and numeric values with optional data types and enumerations. You will learn how to process only filtered, visible records, work with clones in subforms, compare form recordsets with Recordset Clone, and package reusable search and navigation code into a class module for use across multiple forms.

Quiz

Q1. What is the main purpose of using a RecordsetClone on a bound form?
A. To work with the form's records without moving the visible current record
B. To permanently duplicate all records in the form's table
C. To convert a bound form into an unbound form
D. To automatically create relationships between tables

Q2. Why can automatic data type detection cause problems when searching text fields such as phone numbers?
A. Phone numbers cannot be searched with FindFirst
B. Access may interpret text containing only digits as a numeric value
C. Text fields cannot contain digits
D. RecordsetClone only works with numeric fields

Q3. What improvement can be made to a reusable search function to handle ambiguous values correctly?
A. Add an optional data type parameter
B. Remove all text field searches
C. Convert every value to a date
D. Use only primary key fields

Q4. What is an enumeration in VBA useful for?
A. Defining readable named constants that can appear in IntelliSense
B. Automatically sorting records alphabetically
C. Creating a new table from a query
D. Encrypting values before searching

Q5. When processing only records that the user can currently see, what role can a RecordsetClone serve?
A. It can identify the visible or filtered records to process
B. It can permanently hide all records from the form
C. It can replace the form's record source
D. It can automatically create a backup table

Q6. Why is it often better to separate record selection from record processing?
A. It allows the clone to determine which records qualify while SQL or another recordset performs the action
B. It prevents filtered records from being displayed
C. It eliminates the need for tables
D. It forces all processing to occur in a form module

Q7. Which is an example of an action that might be performed after identifying visible customer records?
A. Create related follow-up records
B. Convert all customer names to numbers
C. Delete the customer table definition
D. Remove all forms from the database

Q8. What is an important consideration when deleting filtered records?
A. Deletion should be handled carefully because it is permanent and can affect multiple records
B. Filtered records cannot be deleted under any circumstances
C. A clone automatically prevents all deletions
D. Deleting records always removes the table itself

Q9. How do you properly reference the form inside a subform control?
A. Refer to the subform control's Form property
B. Refer directly to the table name only
C. Refer to the main form's RecordsetClone only
D. Refer to the database's Relations collection

Q10. What is a key difference between Me.Recordset and Me.RecordsetClone?
A. Moving through Me.Recordset moves the visible form cursor, while moving through Me.RecordsetClone does not
B. Me.RecordsetClone can edit records, but Me.Recordset cannot
C. Me.Recordset works only with unbound forms
D. Me.RecordsetClone always includes records hidden by a filter

Q11. After finding a matching record in a clone, what can be used to move the form to that record?
A. The record's Bookmark
B. The table's Description property
C. The database password
D. The form's Caption property

Q12. Which issue can affect RecordsetClone behavior when working with forms?
A. Unsaved new records may not yet be included in the clone
B. Clones cannot be used with filtered forms
C. Sort order is ignored by all recordsets
D. A subform cannot have a recordset

Q13. Why might a developer use a class module for RecordsetClone navigation code?
A. To package reusable search and navigation logic for use on multiple forms
B. To eliminate all form event procedures
C. To store table data permanently
D. To replace every query in the database

Q14. What is the purpose of a reusable Clone Navigator class?
A. To add consistent search and navigation features to bound forms with minimal repeated code
B. To automatically create forms for every table
C. To convert DAO recordsets into tables
D. To disable bookmarks throughout the database

Q15. Why should a developer be aware of the difference between DAO and ADO clones?
A. They can have different behavior and syntax in Access applications
B. DAO clones cannot search for records
C. ADO clones always move the visible form cursor
D. They are identical and can always be used interchangeably

Answers: 1-A; 2-B; 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 video from Access Learning Zone continues our work with RecordsetClone in Microsoft Access VBA. This is Access Developer Level 60, and it follows directly after Access Developer 59.

In Developer 59, I introduced the fundamentals of RecordsetClone, including FindFirst, FindNext, NoMatch, bookmarks, processing filtered records, and a reusable GoToRecordByValue function. In this course, we will build on those skills and apply them to more practical situations that you are likely to encounter in a real Access database.

I recommend taking my developer classes in sequence whenever possible. Each lesson builds on material from earlier courses. In particular, this class relies heavily on the recordset concepts from Access Developer 16. We will also create a class module later in this course, so reviewing Access Developer 50 may be helpful if you need a refresher on classes and object-oriented programming in VBA.

I am using Microsoft 365 Access, and the current retail equivalent is Access 2024. However, most of the RecordsetClone techniques covered here have been available in Access for many years and should work in much older versions as well.

If you have questions about a specific lesson, post your question on the lesson page associated with that video. Keeping lesson-related questions together makes it easier for me and other students to follow the discussion. For general Access questions that are not related to a particular lesson, use the public Access forum so that more people can contribute answers and suggestions.

In the first lesson, RecordsetClone Part 4, we will improve the reusable search function created in Developer 59. The original function automatically detects the type of data being searched so it can build the correct criteria string. That works well in most cases, but it can fail when a text field contains only numbers.

For example, phone numbers, Social Security numbers, account codes, ZIP codes, and similar values may consist entirely of digits but still be stored as text. Access can interpret those values as numeric when building criteria, which can lead to incorrect search results or invalid criteria strings.

To solve that problem, I will add an optional data type parameter to the search function. We will also create a custom enumeration, or Enum, to make the function easier to use and easier to read. You will learn how enumerations work, how VBA and Access use them internally, and how creating your own Enum values can improve readability and provide IntelliSense support while writing code.

In the second lesson, we will use RecordsetClone as a filtered selection list. Rather than forcing the clone to perform every operation itself, we will use it to identify the exact records currently visible to the user. Once we know which records are selected by the form's filter, we can use SQL statements or another recordset to perform the actual processing.

For example, we will create follow-up records for only the customers currently visible on a filtered form. We will also discuss safer methods for deleting filtered records. Deleting records always requires extra care, especially when filters and related records are involved.

The main concept is that selection and processing are often best handled separately. RecordsetClone can identify which records the user is viewing, while SQL, action queries, or separate recordsets can handle creating, updating, or deleting related data. This approach often produces cleaner, more reliable, and easier-to-maintain VBA code.

In the third lesson, we will work with RecordsetClone inside subforms. Subforms require slightly different syntax because you must reference the form object contained inside the subform control. Once that reference is set correctly, you can search records inside the subform and use bookmarks to move the subform to a matching record.

We will compare Me.Recordset with Me.RecordsetClone so you can understand an important difference between them. When you move through Me.Recordset, you move the form's actual visible record pointer. The user may see the form navigate from record to record while your code runs. RecordsetClone provides a separate recordset that you can search and loop through behind the scenes without changing the record currently displayed to the user.

We will also cover several common RecordsetClone issues involving filters, sort order, unsaved new records, and the distinction between DAO and ADO recordsets. Understanding those differences is important when you are working with bound forms and subforms.

In the fourth lesson, we will bring these concepts together by building a reusable class module. We will take the RecordsetClone search and navigation logic that has been repeated in several examples and package it into a Clone Navigator class.

Once that class is complete, you will be able to add search and navigation tools to a bound form with only a few lines of code. We will attach the class to one form, use it to search numeric and text fields, and then reuse the same class on another form without copying and pasting all of the original RecordsetClone code.

By the end of Access Developer 60, you will have a flexible RecordsetClone navigation system that can handle multiple data types, process only filtered records, work with subforms, and be reused throughout your database with a class module.

I recommend watching each lesson once from beginning to end before trying to build it yourself. Focus first on understanding the concepts and how the pieces fit together. Then watch the lessons again while following along with the sample database. Once you understand the examples in the sample database, you can adapt the techniques to your own projects.

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

Improving reusable RecordsetClone searches
Handling numeric-looking text search values
Optional data type parameters in VBA
Creating and using VBA enumerations
Processing only visible filtered records
Creating follow-up records for selected records
Deleting filtered records safely
Using RecordsetClone in subforms
Navigating subform records with bookmarks
Comparing Recordset and RecordsetClone behavior
RecordsetClone filters, sorting, and new records
Building a reusable Clone Navigator class
Adding reusable form search and navigation

Primary Topics

RecordsetClone reusable search improvements, data type handling in search criteria, optional data type parameters, custom VBA enumerations, processing filtered visible records, RecordsetClone in subforms, bookmarks and navigation, Recordset versus Records

Secondary Topics

FindFirst, FindNext, NoMatch, DAO versus ADO clones, filters, sort order, unsaved new records, SQL processing, safe deletion considerations, IntelliSense