Finding the data you need can be a challenge. In this video, learn how to find records within a table.
- [Instructor] Whenever you call a company or place an order over a website, you usually log into a system using some sort of user ID. The customer service rep or computer program then uses that ID to look up your customer record. You can provide the same functionality and access using the find family of methods. I'll demonstrate how to use them in this movie. I have opened the course database and opened the customer's table in table view. And you can see the list of customers, as well as the states they are from.
I happen to know there are a lot of customers from California, and what I'll do is find the first one, and then move to the next, perhaps the next a couple of times, and then also find the last customer from California, and move to previous customers. To see how that works, I'll press alt F11 to go to the Visual Basic editor, and insert a code module. So I'll click insert and module, and then in the code module, I'll create a subroutine, and I'll call it sub move around.
I don't need to pass any arguments, so type left and right parentheses, enter a couple of times, and we have room to work. I need to work with the customer's table as a record set, so I will declare a variable for that. So I'll do dim myR as recordset. Now I need to set myR equal to the customer's table. So I'll do set myR equals, then CurrentDb, for the current database, period.
Open record set. Then I need to identify the table I'm going to use, and also that I want to work with it dynamically. So I'll start the argument list by typing a left parenthesis, and then the name of the table is customers. So I'll type that, close the double quotes, then a comma. Now we need to identify the type of record set I need to create. And that will be an open Dyna set. So I'll type dbOpenDynaset.
There is no autocomplete helping you here, so you just need to know that's the keyword to use. Right parenthesis, and enter. Now we can find the first customer with a state value of California. So I'll do myR, and we'll use the find first method. You can see there are others, we'll cover each of them. Find first, and now we need to identify the search string, or criteria. It's a string, so put it in double quotes.
And then the name of the field is state. Followed by a right square bracket. You always enclose the name of fields, and those type of criteria within left and right square brackets. Then equal, and it needs to be equal to another string, in this case California. A string within a string is indicated using single quotes. So I'll do a single quote, California, single quote, and that's the end of the criteria. So I'll type a double quote, and enter.
Now we can show the full name of the customer in a message box. So I'll type message box myR, exclamation point, and then the name of the field in left and right square brackets, and that is custfullname, all one word. Alright, close with the right square bracket, enter. And now we can close the record set using myR.close. That releases the memory.
For a small dataset like this, it doesn't really matter. For larger datasets, which you're more likely to be working with, it does matter because it eats up system resources. And finally we'll set myR to nothing. So set myR=nothing. Again, freeing up that memory. Okay, everything looks good, and now we can find the first customer from the state of California. So I'll press F5 to run, and I see Rona Stanley is the first, and if I look down, I see in row 18, that Rona Stanley is in fact, the first customer from California.
Then I'll click okay. We could also look for the last customer to be entered from California. If we go in chronological order, that would mean that customer is the most recent customer. So I will give myself a little more room by increasing the size of the code window. I'll go ahead and do both because just need to click okay. I'll do myR. And then find last. And I'll copy both the criterion and the code for the message box.
Press Control + V. Everything looks good. Alright, F5 Rona Stanley again, okay. And the last one is Nicole McConnell. Okay again. If you want to find the next record that meets a particular criterion, then you can use the find next method. So I'll go up between the find first and find last little code elements there. And I'll do myR. find next, and then again I will paste.
I believe I still have that. Yep. So I have state of California, and the customer full name in a message box. Alright, I'll press F5 to run, Rona Stanley, Adara Langley, and Nicole McConnell. And finally, you can use find previous, which will find the previous record. So I know that we have multiple records. So I'll go down after find last. I'll do myR.findprevious, then a space, and paste in what I had before, state=Californiacustomerfullname, great.
Now we run it the whole way through. The first few bits will probably look familiar. So we have Rona Stanley, she is first. Next is Adara Langley, last is Nicole McConnell, and the one ahead of Nicole McConnell is Serena Malone. In most cases you won't hard code the values you want to find. Instead, you'll use some form of user input to provide the value to look up.
- Working with macros
- Running a VBA procedure
- Debugging VBA code
- Defining variables, constants, and calculations
- Displaying a calculated result in a message box
- Repeating tasks with loops
- Manipulating database objects
- Adding, editing, and deleting records
- Looking up values in a table
- Automating processes with macros