From the course: Access: VBA

Look up a value in a table field

From the course: Access: VBA

Start my 1-month free trial

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.

Contents