Microsoft Access DLOOKUP Without Programming

welcome to another free microsoft access video tutorial brought to you by access learning zone.com my name is Richard Ross in today's free tip video I'm going to teach you how to use the dlookup function to look up a value from a table or query without using any programming I've covered the dlookup function before in some of my other tutorials but those are all more advanced lessons that involve some programming in this lesson I just want to show you one simple way you can use dlookup without any programming to view information from your tables this is the database that I construct them my full access courses and if you open up the customer list pull up a customer I'll pull myself up here and each customer has a sales rep assigned and a service tech and when we're making an order I'll go to show orders here here's an order it's nice sometimes if you have the sales rep up here to be able just to see their phone number if each of your sales rep has a different phone number or extension this sales reps phone number here is the lookup when I change the sales rep as you can see there's a new phone number or there's Joe's phone number how do I do that it's real simple I'm going to switch over to design view and I'm going to delete that phone number field and show you how I built it now here's a simple combo box where I can pick the sales rep if you need help learning how to make relational combo boxes where you can pick from a list of values I've got several other tutorials on how to do that I'll put a link in the description below the video so you can click on it to find that tutorial now I'll grab a blank text box from my toolbox up here drop it right there and I'll slide this over just a little bit this will be the phone number field and I'll format that so it's black so you can actually see it there we go okay now this right here is where the phone number is going to go and I'm going to set that equal to a light shade of gray just so the user knows that they can't edit that or change that it's a locked field now in order to put some data in here we have to know what we're looking up now the sales rep ID in my table is based on the employee table now the employee table is real simple here it is employee ID first name last name title and so on and right over here is their phone number it's the work phone field there's also a home phone if you want to use that but I'm going to look up the work phone field okay so what am I looking up I'm looking up work phone number from the employee table where the employee ID is equal to whatever value is put in this combo box and what's the name of this combo box we'll look at it this is the sales rep combo that's the name of that box it's nice to make sure you know all that stuff before you start following around the code but here's the text box where we're going to put that information now name you can give it a name if you want to like sales rep phone that doesn't matter you can leave it text 23 if you want to the control source is we're going to put our D lookup function it's going to be equals D lookup now the lookup takes three bits of information as text strings expression is what field are you looking up while I'm looking up the work phone field in quotes comma domain means where what table or query is this data in well it's in my employee table comma and the criteria is where the employee ID that's the field in the table equals close your quotes up ampersand sales rep combo and then close your parentheses and that's all it is right do you look up the work phone from the employee table where the employee ID equals and then ampersand means tack on a value concatenate something put two fields together basically employee ID equals and then whatever the value in that combo box is if it's a four this will be employee ID equals four okay save it I'm going to close my form reopen it and there you can see it right there there's the phone number and if I change it there's Ginger's phone number now before you saw it formatted so I just throw some formatting in here real quick this field here will format it as a phone number which looks like this those are at sign that says put one character there there's three followed by a dash three more a dash then four more and now that will give you a nicely formatted phone number there it is using the lookup no programming required all you have to know is that the lookup function and how it works if you want to learn more about the D lookup function and you should it's a very powerful function visit my website look for my access expert level ten course and I cover D lookup in a lot more detail there's tons you can do with the lookup even without programming in the description below the video I will put a link to that combo box tutorial I'll also put a link to another free D lookup tutorial that I have that involves a little bit of programming but it adds some more flexibility so if you want to learn D lookup check out those tutorials and check out my website and of course if you have any questions or comments please feel free to post them I do my best to get back to as many people as I can I sometimes get tons of comments every day and I love reading them I do I read them all I promise but I don't always have time to answer them all so if you post a question I'm sorry if I don't get back to you but but I do read all your comments thank you very much as soon as they add that that 25th hour in the day I'll make sure to get back to everybody but thanks for watching I hope you learn something and we'll see you real soon you

Channel: Computer Learning Zone

Views: 160,219

Rating: 4.8715086 out of 5

Keywords: microsoft access, Microsoft Access (Software), ms access, access tutorial, dlookup, dlookup function, combo box

Id: 2Kq3jZ73PGQ

Length: 6min 16sec (376 seconds)

Published: Wed Jul 24 2013