Recordsets
By Richard Rost
3 years ago
Understanding Recordsets in Microsoft Access VBA
In this Microsoft Access tutorial, we will explore the fundamental concepts of recordsets and their practical applications. You'll learn what recordsets are, how they function within the VBA environment, and why they are a crucial tool for database manipulation and data management. This guide is tailored for beginner Access VBA developers, providing step-by-step insights into using recordsets to enhance your data handling capabilities in Access, setting a strong foundation for more advanced database programming techniques.We'll also see how to send emails in a loop to multiple customers using a Recordset.
Members
There is no extended cut, but here is the database 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
Keywords
TechHelp Access 2016, Access 2019, Access 2021, Access 365, Microsoft Access, MS Access, MS Access Tutorial, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #learn, #lesson, #training, #database, Understanding Recordsets, OpenRecordset, CurrentDB, Recordset Fundamentals, Recordset Loop, loop thru records, Introduction to Recordsets, Email Automation with Access VBA, Managing Data with Recordsets, Recordset Object, VBA Recordset Methods, Recordset Navigation, Recordset and SQL, avoiding endless loops, MoveNext, Nothing
Subscribe to Recordsets
Get notifications when this page is updated
Intro In this video, we will learn about recordsets in Microsoft Access using VBA. I'll explain what recordsets are, show you how to create them, display individual records, and loop through multiple records to process your data. We'll walk through examples including filtering records with SQL, handling different states, and using loops to perform actions like sending emails to specific groups in your database. This tutorial is geared toward Access developers who want to take advantage of VBA recordsets for advanced data tasks.Transcript Today we're going to learn about record sets. We're going to learn about what they are and how to use them. I'll show you a few simple examples. We'll learn how to create a record set, display a record, loop through a bunch of records, and display the information. Then I'll teach you how to do something cool, like how to send emails individually to a bunch of people in your database based on whatever criteria you want. We can do all of that by looping through records with a record set in our VBA code.
Now, of course, this is a more advanced developer lesson for developers. So if you're new to VBA and you haven't done any VBA programming before, go watch this video. Here's the link down here where you can scan this little QR code. Go watch this. It's about 20 minutes long. It'll teach you everything you need to know to get started. Go watch this, then come on back.
To use record sets, you're going to have to know SQL. There's no way around it. The SQL language, at least how to form a good select statement. So go watch this video too. You can just use a table or a query for a record set, but the real power is when you learn how to write SQL statements. And make sure you know how to use a while loop. While loop is my personal preference. There are other kinds of loops too. You can use any one you want, but I like the while loops. Go watch this video too. These are all free videos. They're on my website. They're on my YouTube channel. Go watch them and come on back.
All right. So what is a record set? Well, a record set is an object that is used to represent a set of records. The record set - set of records - retrieved from your database. You can think of it like a virtual table that's in memory. It holds whatever data you put in it. You can say, give me all the customers from Florida, and it'll save it in memory as a record set.
Record sets then allow you to interact with the data programmatically. You can read, add, update, delete records. You can move through them one at a time. You can move forward. You can move backward. You can do all kinds of stuff. It's a very useful tool when dealing with complex data processing that cannot be handled through standard SQL queries. If a basic query can't do it, then you can use a record set.
And usually record sets are powerful because you can do stuff in the loop. A basic query can't send an email or save a PDF file, but you can do all that stuff in a record set loop.
Here's an example. We're going to build this a little bit later. We're going to set up a record set. Here's the SQL. All right. Give me all the customers from Florida that have an email address. Here's the record set declaration. We're going to loop through all the records. Then we're going to send an email inside that loop to each of those people. You can't do that with a basic query. So that's what we're going to do today.
But let's start simple.
Here I am in my TechHelp free template. This is a free database. You can download a copy off my website if you want to. Let's go in and hijack this button. Let's go to design view. I'm going to make this guy a little bit bigger because we're going to use the status box here for my results.
Status box is just a text box. And I wrote a function called status. If you haven't watched the blank template database where I build this, I'll put a link down below. I show you how to use the status box.
Anyways, in here, build event. Instead of hello world, let's delete that. We're going to set up a record set. Dim RS as a record set. It's a built-in Access object. There it is.
Again, think of it like a virtual table or a virtual query. It exists in the computer's memory. Now we have to set up our record set. We do it like this.
Set RS equals current DB. That says the current database. You have to open. Dot. OpenRecordset. There it is.
Then in parentheses, you can put either the name of a table or a query or an SQL statement. So I could put in here customerT, for example, and I'll get a record set that is pointing to the records in the customer table.
Now, since record set is an object, anything you ever have to use the word set for is an object. Whenever you see that, you want to make sure you delete it when you're done. You want to destroy it, basically. Clear up that memory. Set RS equals nothing before you exit that subroutine.
You absolutely have to do that. Access should clean up after itself, but I know from experience that it doesn't. If you get a lot of stuff going on in here with lots of record sets, doing this, doing that, more complexities, obviously the worse it gets. Access isn't perfect at cleaning up after itself, so just whenever you set up one, make sure you set it to nothing at the end.
Now what goes in the middle here? Well, now we have a record set open, and it's pointing to customerT. So let's take a look at what we got. Let's take a look at one of the records that we have. So let's say status - that's my status function to just put stuff in the status box - and I'll say the customer's first name is, colon, and here's how you reference the fields in the record set. It's RS, the name of your record set, bang (the exclamation point), and then whatever your field is. So first name, just like that.
Then we're going to say RS close, close the record set. You've got it. You open it, you close it, then you destroy it. That, in a nutshell, is a very simple record set.
Let's see what we get. Let's save it. Always throw in a debug compile. Let's go back over. Close this. Let's open it back up again. I got code that moves it. Ready, click. Boom. There you go. There's the first person in that table. If you open it up, first name, Richard, that's the first record.
I didn't specify any order. I didn't specify any conditions. Just open up the table. Now you might want to open up a specific record from that table, and that's where your SQL comes in handy.
You don't have to know SQL to work with records, but realistically you do. So in here, for example, I like to put something like select star or whatever field you want from customerT, and then you can specify which one you want. So, where customerID equals and give it a number, like one. Now save it, and now I'm getting Richard again, but I'm getting a specific record. See, come in here.
Let's say I want the one with Diana. Let's give it a three. Make this three. Save it. Come back over here. There's Diana.
Now what if you want to loop through all of the records in the customer table? Well, that's where your while loop comes into play. Come over here. Let's put this. So we open it up, and we're going to put a while loop right in here.
We're going to put that inside of a while loop. So right here, we're going to say while not RS.EOF. EOF stands for end of file. It's kind of a throwback to text files. Whenever you're reading the lines out of a text file, you start with the beginning and you read until the end of file. That's where EOF comes in. Even though it's not really a file, it's a record set, you get the point. Us programmers love to reuse code. So it's EOF.
Whenever you open up a loop, you have to close the loop, put your wend down there in the bottom. Save it. Are we done yet with our loop? Take a look at it. Go rewatch the while loop video if you have to. What's missing? If I run this right now, what's going to happen? Anybody? Yeah, you guessed it. I got an endless loop going on here, don't I? Because it's going to start here, it's going to open up the record set, it's going to keep statusing that first name over and over again on that first record. It's never going to go anywhere.
Remember, in a while loop, we have to increment our counter. Since we're not using a counter like an x variable, we have to say in here, move to the next record. We do that with an RS.MoveNext. All right, don't forget that. Very important. In fact, oftentimes when I'm writing a record set, I put the shell in there first. I put the while loop, the move next, the wend, the close, and I write all that, then I write the stuff inside the shell. I write the nut later.
All right, let's give it a debug compile. Everything's good. Let's run it. Oh, hang on. What did I do? I just got Diana again. Well, I forgot to change this. Let's get rid of the where customerID equals three. Let's just get rid of that, so we're getting all the customers. Again, you could put just customerT back in there or a query name. That's up to you. Run it. There's everybody.
If you want to throw the customerID on there, you can do that too. You can put in here, RS!CustomerID, and a space. Now you'll see the ID in there as well. There they go. See? That's pretty cool.
What if you want to see just the people from Florida? Well, come over here. Where state equals - now you can't just put Florida like that because that's a text value, and we don't want to use single quotes in here. Get out of the habit of using single quotes. I know SQL Server requires single quotes, and then a lot of people find them easier because with double double quotes, you're supposed to do double double quotes in here, is what you want. I got a whole separate video on why you should use double double quotes instead of single quotes. Because if you've got a name like O'Brien, that single quote will break it. That's why a lot of airline reservation systems won't let you put in single quotes. We run into this all the time when we try to book flights.
What you want is that. This turns into a quote, a double quote inside your string. Trust me. Save it. Run it. There's just people from Florida, right there. I think it's Mr. Spock and Mr. Somebody else from Florida. Let's see where my Floridians are in here. Where are you? Right click, filter. Let's see. Oh, yeah. Mr. Worf, Mr. Data. That's why I got two matches.
Are you with me so far? You see how this is cool? You set up whatever you want here, and then you run through the records inside here. All of this you can do with just a standard query. If you want to just get a list of customers from Florida, etc., I get it. Why bother learning record sets? Well, because inside this loop now, you can do other stuff. For example, you can send each one of these people an email. You can export each one of their invoices. Let's say your SQL statement is set up to show all of the invoices that are 30 days late and you want to send them out to people. You can export a PDF, which I have another video that shows you how to do that, and you can send an email to these people.
Let's send all of my Floridians an email, which you can't do any other way but by using a record set. A standard query won't do this. This is why this stuff is so powerful. So right in here, we can say send email. What do you want the message body to be? Just say hi, and the recipient email is going to be the email address field from the customer table, so it's RS!Email, the subject, email to Floridians. Send immediately is do you want Access to send it immediately without any prompt, or if you say false, it will pop up the Outlook window so you can maybe add something to it or change it or whatever you want to do. I'm going to say true so that just sends it right out. Don't worry about an attachment; this is where you could attach the PDF.
So there's the send email. What's going to happen is it's going to open up the record set, it's going to loop through all the records from Floridians, display their value, send them an email. You're probably wondering to yourself, what is the send email command, where does that come from? Well, I covered that in my other video where I show you how to send email using Outlook. Here it is. Here's the other video. It's free. It's on my website and on my YouTube channel. Go watch this. It talks about all kinds of stuff, including bypassing that warning message that sometimes pops up. Go watch this if you want to learn how to send email from Access using Outlook.
But I already copied the send email code. I put it in my global module. It's right here. There it is. Let me see if I can get it on the screen. Pause the screen now if you want. If you're a Gold member, it's also in the code vault. I'll put a link to that down below. There it is. There's the code vaults. Come down here and hit copy.
Now, the point is that this record set loop can send an email to each person in this record set. When you click the button, boom, boom, boom, boom. It takes a second for each, but there they go. It just sent out four emails. This is one of the reasons why I make sure that all the email addresses in the sample databases that I build in my classes now are from my domain names, because I used to just do random email addresses at Gmail. I used to get some angry responses, like stop doing that.
If I open up my Outlook, there they are. They just went out. Here's me running through a test earlier this afternoon. Same ones. Now this is just a real simple record set. This is just the tip of the iceberg. You can do tons and tons of stuff with record sets. You can do loops. You can do record sets inside of record sets. You can loop through all these customers and then loop through a record set of all their orders for each customer and then go through all the line item details for each one of those. You can add up stuff. You can move forward and backward through the record sets. The sky is the limit.
As I said, if you really want to learn record sets, they're extremely powerful. I start covering them in Access Developer 16. One of the things that record sets are really good for is for using multi-select list boxes, because the only way to populate these and to save the data from them if you want to select multiple items in a list box is to use a record set. It's the best way to do it. I start covering record sets in Developer 16. It goes through several classes. There's lots and lots you can do with this. It's a very powerful professional-level Access development tool, and it'll take your databases to a whole other level. I'll put a link to this class down below, check it out.
And that, my friends, is going to be your TechHelp video for today. I hope you learned something. Live long and prosper. I'll see you next time.
If you enjoyed this video, please give me a thumbs up and post any comments you may have below. I do try to read and answer all of them as soon as I can. Make sure you subscribe to my channel, which is completely free. Click the bell icon and select all to receive notifications when new videos are posted.
Want to learn more? Click the show more link below the video to find additional resources and links. YouTube does a pretty good job of hiding it. It's right down there. See this part of the description here. The name of the video is up here. There's a little show more down there right at the bottom. It's kind of hard to find. Once you click on that, you'll see a list of other videos, additional information related to the current topic, free lessons, and lots more.
YouTube no longer sends out email notifications when new videos are posted like they used to. But if you'd like to get an email every time I post a new video, click on the link to join my mailing list. You can pick how frequently to get emails from me, either as they happen daily, weekly, or monthly.
If you'd like to become a paid member of my channel and receive all kinds of awesome perks, click on the join button. You'll see a list of all the different membership levels that are available, each with its own special perks, including my extended cut videos, access to my code vault, lots of VBA source code in there, template downloads, and lots more. I'll talk more about these perks at the end of the video.
Even if you don't want to commit to becoming a paid member and you'd like to help support my work, please feel free to click on the tip jar link. Your patronage is greatly appreciated and will help keep these free videos coming. I got some puppies to feed. But don't worry, no matter what, these free TechHelp videos are going to keep coming. As long as you keep watching them, I'll keep making more and they'll always be free.
If you really want to learn Access and you haven't tried my free Access Level 1 course, check it out now. It covers all the basics of Microsoft Access, including building forms, queries, reports, and more. It's over four hours long. You can find it on my website or on my YouTube channel. I'll put a link down below to it. Did I mention it's completely free? The whole thing - free, four hours. Go watch it.
A lot of you have told me that you don't have time to sit through a four-hour course. So I do now have a quicker Microsoft Access for Beginners video that covers all the basics faster in about 30 minutes. And no, I didn't just put the video on fast forward. I'll put a link to this down below as well.
If you like Level 1, Level 2 is just a dollar. That's it. One dollar. That's another whole 90-minute course. Level 2 is also free for paid members of any level, including supporters. So if you're a member, go watch Level 2. It's free.
Want to get your question answered in a video just like this one? Visit my TechHelp page and send me your question there. Members get priority, of course.
While I do try to read and respond to all of the comments posted below in the comments section, I only have time to go through them briefly a couple of times a month. Sometimes I get thousands of them. So send me your question here on the TechHelp page and you'll have a better chance of getting it answered.
While you're on my website, be sure to stop by my Access forum. We've got lots of lively conversations about Microsoft Access and other topics. I have a fantastic group of moderators who help me answer questions. Shout out to Alex, Kevin, Scott, Adam, John, Dan, Juan, and everybody else who helps on the site. I appreciate everything you do. I couldn't do it without you.
Be sure to follow my blog, find me on Twitter, and of course on YouTube. I'm on Facebook too, but I don't like Facebook. Don't get me started.
Now let's talk more about those member perks. If you decide to join as a paid member, there are different levels: Silver, Gold, Platinum, and Diamond. Silver members and up get access to all of my extended cut TechHelp videos, one free beginner class every month, and some other perks.
Gold members get all the previous perks plus access to download the sample databases that I build in my TechHelp videos, plus access to my code vault where I keep tons of different functions that I use, the code that I build in most of the videos. You'll also get higher priority if you do submit any TechHelp questions. Now, answers are never guaranteed, but you do go higher in the list to meet the algorithm, and if I like your question, you have a good chance of it being answered. You'll also get one free expert level class each month after you've finished the beginner series.
Platinum members get all the previous perks plus even higher priority for TechHelp questions. You get access to all of my full beginner level courses for every subject, and I cover lots of different subjects like Word, Excel, VBA, ASP, lots of different stuff, not just Access. These are the full length courses found on my website. You get all the beginner ones. In addition, once you finish the expert classes, you get one free developer class per month, so lots of training.
Finally, you can also become a Diamond sponsor. You'll have your name or your company name listed on a sponsors page that will be shown on each video as long as you're a sponsor. You'll get a shout out in the video and a link to your website or product in the text below the video and on my website.
So that's it. Once again, my name is Richard Rost. Thank you for watching this video brought to you by AccessLearningZone.com. I hope you enjoyed it. I hope you learned something today. Live long and prosper my friends. I'll see you again soon.Quiz Q1. What is a record set in the context of Microsoft Access VBA?
A. An object that represents a set of records retrieved from your database
B. A type of query that updates multiple tables at once
C. A function that prints out all database field names
D. An Access form used to display one record at a time
Q2. Why is it important to set a record set object to Nothing after using it in VBA?
A. To prevent duplicate records
B. To release memory and avoid issues with unclosed objects
C. To make sure all records are deleted
D. To convert the record set into a query
Q3. Which of the following is a correct way to open a record set using a table in Access VBA?
A. Set RS = OpenRecordset("customerT")
B. Set RS = CurrentDB.OpenRecordset("customerT")
C. RS.Open("customerT")
D. Open RS as Recordset from customerT
Q4. What is the purpose of the EOF property when looping through a record set?
A. It tells you the current field name
B. It marks the end of the loop through records
C. It prevents moving to a deleted record
D. It allows you to sort the records
Q5. In a record set loop, what is the function of RS.MoveNext?
A. Moves you to the previous record in the set
B. Moves you to the next record in the set
C. Deletes the current record
D. Sets the record set to Nothing
Q6. Which statement best explains why record sets are needed instead of just using SQL queries for some tasks?
A. SQL queries run too slowly compared to record sets
B. Record sets allow you to interact with the data programmatically, enabling tasks like sending emails or exporting PDFs
C. Record sets are required to display tables in Access
D. Only record sets can retrieve data from more than one table
Q7. Which syntax is used in VBA to reference a field from the current record of a record set?
A. RS-fieldName
B. RS(fieldName)
C. RS!fieldName
D. RS.fieldName
Q8. When writing SQL statements in VBA code strings, why is it recommended to use double quotes instead of single quotes around text values?
A. Double quotes are easier to read
B. Double quotes prevent errors with special characters such as an apostrophe in names
C. Single quotes do not work in SQL at all
D. Double quotes make the code run faster
Q9. What is the main advantage of using a loop with a record set in VBA?
A. It is the only way to run queries in Access
B. It allows you to automate actions on each record, such as sending emails or exporting data
C. It automatically updates and deletes records
D. It enables multiple users to view the same data at once
Q10. What would likely happen if you forgot to include RS.MoveNext inside your While Not RS.EOF loop?
A. The code would stop after the first record
B. The loop would process only even-numbered records
C. You would enter an endless loop, repeatedly processing the same record
D. The record set would be emptied
Q11. Which of the following uses for record sets was NOT mentioned as an example in the video?
A. Sending emails to selected people in the database
B. Saving PDF files for each record
C. Dynamically changing form properties based on record data
D. Looping through each customer and their related orders
Q12. Why does the presenter suggest learning SQL and while loops before working with record sets?
A. Record sets cannot be used without SQL queries
B. Knowing SQL and loops makes it easier to create, filter, and navigate record sets programmatically
C. Record sets are slower than queries without SQL knowledge
D. SQL replaces the need for record sets entirely
Q13. How does the presenter suggest handling text criteria in SQL statements within VBA code?
A. Use no quotes
B. Use single quotes
C. Use double quotes inside the string
D. Replace all spaces with underscores
Q14. What is a typical use case for a record set inside another record set (nested record sets)?
A. To open two databases at once
B. To loop through customers and then loop through each customer's orders
C. To combine two record sets into one
D. To export all data as a single CSV file
Q15. What is the correct order of steps when using a record set in VBA as described in the video?
A. Open the record set, process records, close the record set, set it to Nothing
B. Set to Nothing, open the record set, process records, close the record set
C. Process records, open record set, set to Nothing, close record set
D. Open the record set, set it to Nothing, process records, close record set
Answers: 1-A; 2-B; 3-B; 4-B; 5-B; 6-B; 7-C; 8-B; 9-B; 10-C; 11-C; 12-B; 13-C; 14-B; 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 is all about recordsets in Microsoft Access. I will explain what a recordset is, give you a clear understanding of how it works, and then guide you through several examples. First, we will cover how to create a recordset, display information from a single record, and then loop through multiple records to display their data. After the basics, I will show you a practical application: sending individualized emails to a list of people in your database based on criteria you specify, all accomplished by looping through records with a recordset in VBA.
This lesson is intended for more advanced Access users and developers. If you are new to VBA and have not done any programming in VBA before, I recommend starting with one of my introductory videos on the topic. It is about twenty minutes long and covers the basics needed to get you started. Once you are comfortable with the foundations, you'll be ready to continue with this lesson.
To effectively use recordsets, a working knowledge of SQL is also important. While you can use tables or queries directly in a recordset, the true flexibility comes from being able to write SQL SELECT statements. I have a resource available for learning SQL as well. Finally, you'll want a good grasp of looping structures in VBA, particularly the while loop, which is my preferred method, though other loop types are valid.
Let's begin with the basics. What is a recordset? In Microsoft Access, a recordset is an object designed to represent a group of records retrieved from your database. Imagine it as a virtual table that exists in your computer's memory, holding whatever data you put into it. For example, if you request all customers from Florida, the recordset collects that data so you can work with it programmatically.
With a recordset, you have the ability to read, update, add, or delete records. You can step through the data one record at a time, in either direction, and perform actions on each record individually. This makes recordsets a very useful tool for complex data processing tasks that cannot be solved with straightforward SQL queries. For example, if your operation involves sending emails or saving PDF files for each record, a recordset makes this sort of procedural work possible.
Let me walk you through a basic example. Suppose you want to send emails to customers from Florida who have email addresses. You can set up a recordset using an appropriate SQL statement to pull the data you need. Then you loop through each record in that set, sending an email inside the loop to each matched customer. That sort of targeted, automated operation is only possible with recordsets and VBA.
Now, let's look at a simple example within the TechHelp free template database, which you can download from my website. In this example, I use a status box to display results. The status box is just a plain text box, and I have a utility function to display messages in it. If you want to know more about how the status box works, I have another tutorial that covers building this feature.
In the sample code, the process starts by declaring a recordset object, which is built into Access. Think of it as a virtual table or query that only exists in memory. You set up your recordset by assigning it to an open recordset in your current database. When you do this, you can specify the name of a table, the name of a query, or an SQL statement inside the parentheses. For example, if you specify your customer table, the recordset will point to those records.
Since a recordset is an object, it's important to destroy it and free up memory when you're done with it by setting it to nothing at the end of your process. This is crucial because Access does not always clean up memory automatically, especially if your application gets more complex.
Once your recordset is open and pointing to the right table, you can look at its contents. To display a value from the first record, you reference a field by saying RS, the name of your recordset, followed by an exclamation point and the name of your field, such as first name. Then you close the recordset and destroy the object. This simple procedure demonstrates the basic mechanics of working with recordsets.
If you want to work with a specific record, you can adjust your SQL statement to select only that record. For example, by specifying "where customerID equals 3," you are telling Access to pull only the record with that specific ID. You can change the ID value in the SQL to display different records.
To loop through all the records in a table, introduce a while loop. The loop continues as long as RS.EOF is false. EOF stands for "end of file," a holdover from the world of text files, but it's still used here to indicate when the recordset has reached the end of its data. Within each loop iteration, you display the current record's data and move to the next record using the MoveNext method. Failing to include MoveNext results in an endless loop because the pointer never advances past the first record. I recommend setting up the loop structure first and adding your detailed code inside afterward.
If you want to display only the customers from a particular state, such as Florida, you modify the SQL statement to add a WHERE clause. Note that for text values in SQL, you use double quotes, not single quotes, to avoid errors with names containing apostrophes. This prevents problems like those caused by names such as O'Brien.
Once your SQL and loop are correctly set up, running the code will display only the matching records. This technique can be used for more than just display. Inside the loop, you can perform any routine you need on each record, such as sending emails, exporting data, or generating reports. For example, you might want to export all overdue invoices and email each customer a PDF of their invoice. This kind of task is exactly why recordsets are so useful and powerful.
For sending emails, you simply call a procedure to send an individual message to each recipient in your result set. The recipient's email address is pulled directly from the current record in the loop. You can even automate whether the email is sent immediately or whether Outlook opens a message window for you to review.
If you are curious about how the send email function works, I have a separate video that explains using Outlook with Access to send emails programmatically. You may want to review the code or copy it from my global module, which is available in my code vault for Gold members.
Recordsets are incredibly versatile. In addition to looping through records, you can also nest recordsets within each other. For instance, you could loop through each customer, then loop through all of their orders, and even the line items for each order. You can calculate totals, move both forward and backward through the data, and much more.
If you want to advance your skills and truly master recordsets, I take a deep dive into this topic beginning with Access Developer 16. Recordsets are essential for working with multi-select list boxes, saving selections, and a variety of other tasks. Learning to use them will elevate your database applications and give you the professional tools needed for advanced development. Look for a link to that class on my website.
That wraps up this TechHelp lesson. I hope you found it informative and helpful. If you would like to see a complete, step-by-step video tutorial on everything we've discussed here today, visit my website at the link below. Live long and prosper, my friends.Topic List Introduction to record sets in Access VBA
Creating a record set using OpenRecordset
Displaying fields from a record set
Referencing fields using RS!FieldName
Using SQL statements to filter record sets
Retrieving a specific record with a WHERE clause
Looping through all records with a while loop
Understanding RS.EOF and looping logic
Moving to the next record with RS.MoveNext
Displaying multiple fields in a loop
Filtering records by criteria such as state
Using double quotes in SQL strings for text values
Sending individual emails to records in a loop
Using a custom SendEmail procedure with record sets
Cleaning up by closing the record set and setting to Nothing |
