Finding values in a table field lets you discover important information about your business. In this video, learn how to look up a value in a table field.
- [Instructor] One central database design best practice is to have a field that contains a unique value for each row. For example, every customer would have a unique customer ID number. Having a unique value for each row of a table lets you look up other values based on that number. For example, you could use an order number to look up details about a specific order. In this movie, I will show you how to use the D lookup method to find a value in one field based on the value in another field. I have opened the course database file and I'll switch to the Visual Basic editor by pressing alt F11.
Now that I'm here I need to create a code module, so I'll go to the insert menu and click module. And now I can start adding my code. I'll create a subroutine to which I will not pass any arguments so I'll type sub and then lookup value. And then open and close parentheses, 'cause again, I'm not passing any arguments. Then enter a couple of times and now I can declare my variables. I'll declare two so I'll do dim, L-N-G-I-D.
That will be the ID number that will be looked up inside of the orders table. I'll declare it as a long and then a comma and then S crit as string and this'll be the criteria that we search on that will construct based on input from the input box and other things we know about what we want to search. So I'll press enter a couple of times and now we can get the order number from an input box.
So L-N-G-I-D equals and then input box. Followed by left parenthesis and double quotes. Please enter the order number. Double quotes and right parenthesis to close. That's all we need there. And now we can create the criteria that will be used in these lookup. So that will be assigned to variable S crit equals then the string order num which is the name of the column that will be looking up the order number.
Then equal then a space then a double quote. Ampersand because we are concatenating strings L-N-G-I-D. So what this line of code does is combine the text order num equal with the number that we get from the input box. And finally, I'll press enter twice and we will create a message box line to display the value we look up. So I'll type message box. Then, D lookup. That's the function we're using.
Followed by left parenthesis. And now we need to provide three bits of information, the expression, the domain, an the criteria. Expression is the name of the table column. So in double quotes, because it's a string quantity then double quotes and a comma, now the domain, which is the table, double quotes orders, followed by double quotes and a comma, and then a criteria is the string that we assign to the S crit variable. And because we're using a variable, we don't have to put it in double quotes, just the name, so S crit.
And type a right parenthesis to close out the message box and down arrow, and everything looks good. Now when this code runs, if it runs correctly we should get the quantity of items ordered, and the order of the number we put in. So I'll press F5 to run, order number 41, press enter, and we get a quantity of three. So I'll click okay. This sort of look up is fairly straight forward to implement in Microsoft Access, and it can provide a lot of information with very little code.
- 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
Skill Level Advanced
What you should know1m 38s
1. Introduction to Visual Basic for Applications
2. Define Variables, Constants, and Calculations
3. Add Logic to Your VBA Code
4. Debug Your VBA Code
5. Manipulate Database Objects Using DoCmd
6. Read and Manipulate Table Data
7. Manipulate a Database Using the Application Object
8. Control Forms and Reports Using the Me Object
9. Automate Processes Using Macros
Next steps1m 7s
- Mark as unwatched
- Mark all as unwatched
Are you sure you want to mark all the videos in this course as unwatched?
This will not affect your course history, your reports, or your certificates of completion for this course.Cancel
Take notes with your new membership!
Type in the entry box, then click Enter to save your note.
1:30Press on any video thumbnail to jump immediately to the timecode shown.
Notes are saved with you account but can also be exported as plain text, MS Word, PDF, Google Doc, or Evernote.